December 14, 2008 at 9:06 am
Hi I have the following problem:
The data set obtained from a stored proc proc1 with input parameter @partnum is:
projectno partno
08-03520-00 E360MLID
08-03267-01 E360MLID
06-10595-03 E360MLID
Now I need to use the above set of data to a stored procedure proc2 with input parameter @projectno and @partno
and then get the result set.
How does one do this in stored procedure where instead of passing @projectno and @partno three times and get three output corresponding to executing the secong procedure three times
I want to execute the second procedure executed one time with the three sets of values and have one set of output.
I appreciate any help to resolve this. Regards.:unsure:
December 14, 2008 at 9:25 am
IIRC, You can only pass a table in a parameter as of 2K8. That said, there are a number of other ways to accomplish what you're trying to do, such as global temp tables. Which method you use will depend on concurrency. Is this something that many people may run at exactly the same time? Is there any reason it needs to be 2 separate stored procedures?
December 14, 2008 at 9:33 am
Hi Seth,
There will be ten people who will be using the system. I am told that there will be six people in the system at the same time. I am not sure whether I should be considering concurrency as an issue. Also the two stored procedure does not need to be separate. However what is important here is to run the second procedure once to have a single input of multiple rows rather than inputing the parameter from the dataset three times. I have run out of ideas how to solve this problem due to limitation of knowledge of sql server. I would appreciate any help. Thanks again.
December 14, 2008 at 9:43 am
Is the dataset generated by the first stored procedure being run once, or does it get generated by the first stored procedure being run multiple times? How long are these procedures? Is it possible to just post them here?
December 14, 2008 at 9:54 am
Seth,
The first procedure is run once to get the output. The first procedure will always run once to get single and mostly multiple row output. My stored procedure is somewhat cumbersome. Hence I have simplefied my problem in a few lines. What I am looking for is how does one use the data set generated by running the first stored procedure to generate an output keeping in mind that the second stored procedure can only be executed once utilizing the dataset generated by the first stored procedure. Any further thoughts
December 14, 2008 at 10:48 am
You can also use cursors or Loop within the first SP and calling the 2nd SP for each rowset from within the cursor/loop.
December 14, 2008 at 10:52 am
Hi SSC Journeyman
Could you please show an example here. I have not delat with cursor much. Your help is highly appreciated. Due to adequate knowledge of sql server I am at my wits end. Regards.
December 14, 2008 at 11:08 am
Just modified the cursor example from BOL
=========================================
Declare @LN varchar(255) -- variable to store Last Name
Declare @FN varchar(255) -- variable to store First Name
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees /* This is the query fetching records from YourSP1*/
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor into @LN, @FN -- Fetch the first record
WHILE @@FETCH_STATUS = 0
BEGIN
exec YourSP2 @LN, @FN --Process the fetched record by calling second SP
FETCH NEXT FROM Employee_Cursor --Fetch next rowset from the Resultset
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
=========================================
-- code placed beween BEGIN and END will continue to loop until @@FETCH_STATUS assumes a negative value which happens when there are no more rows to process.
Modify this as per your query structure...
December 14, 2008 at 11:13 am
BEGIN
exec YourSP2 @LN, @FN --Process the fetched record by calling second SP
FETCH NEXT FROM Employee_Cursor --Fetch next rowset from the Resultset
END
missed one thing..
modify FETCH NEXT FROM Employee_Cursor
to FETCH NEXT FROM Employee_Cursor into @LN, @FN
December 14, 2008 at 11:44 am
ps (12/14/2008)
You can also use cursors or Loop within the first SP and calling the 2nd SP for each rowset from within the cursor/loop.
You can, but that's an exceedingly slow way of doing things. Cursors do not perform well.
Gamaz, put the result set into a temp table that you create in the first procedure. You'll be able to reference the temp table in the second. Temp tables are local to the connection they're created on, so no concurrency problems.
A brief eg to show the concept.
CREATE PROCEDURE Proc1 AS
CREATE TABLE #InterimResultSet (id int)
INSERT INTO #InterimResultSet values (1)
INSERT INTO #InterimResultSet values (3)
INSERT INTO #InterimResultSet values (2)
EXEC Proc2
GO
CREATE PROCEDURE Proc2 AS
SELECT * FROM #InterimResultSet
GO
EXEC Proc1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2008 at 12:59 pm
Thanks Journeyman for the nice example. One thing is still different from my scenario. That is in the cursor example you have shown the select statement of cursor does not have a parameter to pass. Can I have a stored proc with parameter in a select statement for the cursor. If not, can I have a sql statement with a parameter. I need to pass a parameter to my first proc. Here you are using a sql which is great but does not have any parameter. Is it possible to define a cursor where the select statement will have a parameter e.g.
SELECT LastName, FirstName FROM Northwind.dbo.Employees
where lastname = @lastname
I appreciate your feedback.
December 14, 2008 at 6:38 pm
SELECT LastName, FirstName FROM Northwind.dbo.Employees
where lastname = @lastname
is fine... you can use that...
You can, but that's an exceedingly slow way of doing things. Cursors do not perform well.
I'd also agree with Gail's comment that cursors are slow.
If your concern was to pass the entire resultset into the 2nd SP, Gail's exmple just did that. You can reference the temporary table created in the 1st SP in the 2nd SP without worring abt concurrency.
Gail, I think he needs to run 2nd SP for each Row. If the interim table contains 10 records, he needs to fire the 2nd SP 10 times. Any better suggestions to cursor on this?
December 15, 2008 at 12:02 am
gamaz (12/14/2008)
Thanks Journeyman for the nice example. One thing is still different from my scenario. That is in the cursor example you have shown the select statement of cursor does not have a parameter to pass. Can I have a stored proc with parameter in a select statement for the cursor. If not, can I have a sql statement with a parameter. I need to pass a parameter to my first proc. Here you are using a sql which is great but does not have any parameter. Is it possible to define a cursor where the select statement will have a parameter e.g.SELECT LastName, FirstName FROM Northwind.dbo.Employees
where lastname = @lastname
I appreciate your feedback.
Sure it's possible to have parameters. The source for a cursor is any valid select statement.
That said, cursors are probably not the best way of doing things. Can you please explain in a bit more detail (ignoring cursors for now) what it is you need to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2008 at 12:10 am
ps (12/14/2008)
Gail, I think he needs to run 2nd SP for each Row.
I don't think so, seeing as he was asking how to pass a resultset to a stored procedure
If the interim table contains 10 records, he needs to fire the 2nd SP 10 times. Any better suggestions to cursor on this?
Change the second proc so that it can handle a resultset in a temp table and call it once.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply