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
Your Filters
Interested
Ignored
     
0 Shai Vashdi 600
1 Tadit Dash 305
2 Sergey Alexandrovich Kryukov 250
3 Peter Leow 190
4 OriginalGriff 148
0 Sergey Alexandrovich Kryukov 9,395
1 OriginalGriff 5,473
2 Peter Leow 4,150
3 Maciej Los 3,540
4 Abhinav S 3,333


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