How to Join Store procedure with a table

Question

0.00/5 (No votes)

See more:

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

SQL

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

SQL

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

MAbubakar.Riaz

Add a Solution

6 solutions

Add a Solution

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

Maciej Los · Answer 1 · 2013-11-13T10:57:00

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[^]

Posted 13-Nov-13 10:57am

Maciej Los

coded007 · Answer 2 · 2013-11-13T19:34:00

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

Posted 13-Nov-13 19:34pm

coded007

mgoad99 · Answer 3 · 2013-11-13T10:10:00

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.

Posted 13-Nov-13 10:10am

mgoad99

Comments

MAbubakar.Riaz 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

mgoad99 · Answer 4 · 2013-11-14T10:21:00

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.

**Dnyaneshwar**@Pune · Answer 5 · 2013-11-14T20:11:00

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.

Posted 14-Nov-13 20:11pm

Dnyaneshwar@Pune

Siva Hyderabad · Answer 6 · 2013-11-14T20:16:00

C#

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

How to Join Store procedure with a table

6 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0

How to Join Store procedure with a table

6 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us