November 26, 2007 at 10:28 am
Hi everyone,
I have a SP1 which returns a table result set. I want to create SP2 which returns an integer value for count(SP1)
I can do this by creating a temporary table.
Is there any way to achieve this without creating a temp table?
Is there any table object in the tempdb that i can recall for SP1 reult set?
Here is my code sample:
--Lets create SP1
CREATE PROC SP1
AS
SELECT au_lname
FROM authors
---Lets create SP2 which counts SP1
CREATE PROC SP2
AS
SET NOCOUNT ON
CREATE TABLE #tmp
(au_lname VARCHAR(20))
INSERT #tmp (au_lname)
EXEC SP1
SELECT COUNT(*)
FROM #tmp
--Now we can execute SP2 to see how SP2 works
EXEC SP2
November 26, 2007 at 10:46 am
--Lets create SP1
CREATE PROC SP1
@NoRows int OUTPUT
AS
begin
set nocount on -- avoid unneeded network info
SELECT au_lname
FROM authors
SET @NoRows = @@rowcount
end
go
declare @Rowcount int
exec sp1 @Rowcount output
print @Rowcount
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2007 at 11:54 am
Hi,
Thank you for the reply.
However, the application which calls those procedures need both of them explicitly.
Which means,
Application needs
SP1 to get the result set
SP2 to get how many of them
Any more ideas?
Thank you
November 26, 2007 at 12:25 pm
it is actualy getting the resultset as well as the rowcount because that is returned as an outputvariable.
.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2007 at 12:32 pm
>>However, the application which calls those procedures need both of them explicitly.
By that, are you inferring that you have no control over the application code, and therefore can't make any changes to which procedures are called and what their parameters are ?
November 26, 2007 at 1:43 pm
Day,
ALZDBA was stating that the application only has to make one stored procedure call to retrieve both sets of information. The count is stored in output variable that the application can use.
If you must have two sperate stored procedures, I think you are going to have to use a table variable or temp table for this.
November 26, 2007 at 2:35 pm
Hi all,
Application calls SP1 for result set
and
calls SP2 for how many results from SP1
This application is kind of reporting tool. Even though two SP sounds they are related, they are consumed separately in the application. When application calls SP1, it only needs the table.
I cannot change the application at this point. So, I guess I have to use that temp table.
Thank you for all
November 26, 2007 at 2:39 pm
Definitely you should change the App. It is a "bad" design to do that in two separated calls.
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply