Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi my store procedure returns a table from a input value
but i am unable to use/join this procedure with values available in another table.
look at this catastrophe code and help me out. thank you
 
lame attempt #1
select T1.* , SP.* from dbo.mytable as [T1]
inner join
exec sp_iteration_control [T1.sub] as [SP]
on sp.sub=t1.sub

 
Lame attempt #2
declare @counter int
        ,@sub varchar(12)
While(@Counter<(select COUNT(*) from dbo.mytable))
BEGIN
    select @sub=Sub from dbo.mytable
    exec dbo.sp_iteration_control @sub
    SET @Counter +=1
    END
Posted 13-Nov-13 10:00am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can call SP and get result into temporary table, then join these data with your recordset.
 
Please, see: Insert Stored Procedure Results Into Table[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Quote:
select T1.* , SP.* from dbo.mytable as [T1]
inner join
exec sp_iteration_control [T1.sub] as [SP]
on sp.sub=t1.sub

 
This is not the way to join the stored procedure to a table. In order to do join the we need to insert stored procedure data into a table and we need to to the join
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You cannot call a stored procedure and use the results inside another query. A view would work, but you cannot have parameters with views. If you need to do something like this and need parameters, you can checkout creating a function.
  Permalink  
Comments
Member 10392458 at 14-Nov-13 16:00pm
   
i already hav created functions inside my store procedure, can one function have another functions in it? my problem is my store procedure needs a input variable and then returns result set in single row in tabular form of that input variable... now one variable is ok by i have to use tons of variables that are places in another table, can i not have exact same functionalty like a 'table valued user defined function'? in SP
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I think a function within a function should be ok.
 
I think what you need is not a stored procedure, but a table-valued function, which would have paramaters just like the SP.
 
Here is a simplifed example from one of my stored procedures:
DECLARE @StartDate as smalldatetime
DECLARE @EndDate as smalldatetime
DECLARE @AuditID as int
DECLARE @Region as varchar(10)
 
SET @StartDate = '11/1/2013'
SET @EndDate = '11/30/2013'
SET @AuditID = 12
SET @Region = '%'
 

SELECT Review.*, UDF.*
FROM  MyDatabase.MySchema.Review 
      inner join MyDatabase.MySchema.udfSelectedReviews (@StartDate, @EndDate, @AuditID, @Region) as UDF
	on Review.ReviewID = UDF.ReviewID
 
The select statement is joining the result set from a user defined function that has 4 parameters.
 
I hope that helps to point you in the right direction.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

First save the result of Stored Procedure into a temporary table and then apply the Inner Join on this table with result set then it will work.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

    CREATE PROCEDURE [dbo].[k_H_GetList]
    @status int
	
    AS
     BEGIN
	
	--HRM 
	
	SET NOCOUNT ON;
		
	if (@status=1)
	begin
	select VA.sno as Keys, MV.Sno,ED.branch,ED.designation,ED.Firstname+' '+ED.Lastname+' - '+ED.kjlid as Empname,MV.Vehicleno,MV.updatedby from
	 K_HRM_Vehicle_Assign VA
    inner join K_HRM_Vehicles MV on MV.sno=VA.vehicleno
    inner join K_MasterEmpDetails ED on ED.sno=VA.empname      
    
    
	end
		
	
     end
    END
    
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,656
1 Sergey Alexandrovich Kryukov 6,400
2 CPallini 5,240
3 George Jonsson 3,584
4 Gihan Liyanage 2,625


Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 15 Nov 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100