Stored Procedures - OUTPUT?
-
Can anyone point me in the right direction, or at least towards some decent documentation for the following...?
I want a stored procedure that calls multiple select queries that all have a single result. I want all the results to be available once my procedure has finished.
So this is how I’m going about it so far…
(… selects are done this way because in my ‘real code’ they are created using params that are passed in …)
CREATE PROCEDURE myprocedure
@out_var1 varchar(5) OUTPUT,
@out_var2 varchar(5) OUTPUT,
@out_var3 varchar(5) OUTPUT,
AS
BEGIN
declare @foo1 varchar(1000)
select @ foo1 = 'select count(id) as count2 from foobar where theId = ''2'''
print @ foo1
exec (@foo1)
declare @foo2 varchar(1000)
select @ foo2 = 'select count(id) as count2 from foobar where theId = ''2'''
print @ foo2
exec (@foo2)
declare @foo3 varchar(1000)
select @ foo3 = 'select count(id) as count2 from foobar where theId = ''3''’
print @ foo3
exec (@foo3)
return
end
… obviously I would like all the results to be available to out_var’n’, and this is the bit that I can’t figure out...
Many thanks in advance.
Ollie
-
Unless I have missed something in your requirements, will the following give you ther required results:
CREATE PROCEDURE myprocedure
@out_var1 varchar(5) OUTPUT,
@out_var2 varchar(5) OUTPUT,
@out_var3 varchar(5) OUTPUT,
AS
BEGIN
set @out_var1 = (select count(id) as count2 from foobar where theId = '2')
set @out_var2 = (select count(id) as count2 from foobar where theId = '2')
set @out_var3 = (select count(id) as count2 from foobar where theId = '3')
return
end
-
You might try something like this. This is how to return variables to calling program from dynamic SQL.
set nocount on
create table foobar (id int, theid int)
insert into foobar values(1,1)
insert into foobar values(1,2)
insert into foobar values(1,2)
insert into foobar values(1,3)
insert into foobar values(1,3)
insert into foobar values(1,3)
go
create PROCEDURE myprocedure
@out_var1 varchar(5) OUTPUT,
@out_var2 varchar(5) OUTPUT,
@out_var3 varchar(5) OUTPUT
AS
BEGIN
declare @foo1 nvarchar(1000)
select @foo1 = 'select @out_var1=count(id) from foobar where theId = ''1'''
print @foo1
execute sp_executesql @foo1,N'@out_var1 varchar(5) output',@out_var1 output
declare @foo2 nvarchar(1000)
select @foo2 = 'select @out_var2=count(id) from foobar where theId = ''2'''
print @foo2
execute sp_executesql @foo2,N'@out_var2 varchar(5) output',@out_var2 output
declare @foo3 nvarchar(1000)
select @foo3 = 'select @out_var3=count(id) from foobar where theId = ''3'''
print @foo3
execute sp_executesql @foo3,N'@out_var3 varchar(5) output',@out_var3 output
return
end
go
declare @foo1 varchar(1000)
declare @foo2 varchar(1000)
declare @foo3 varchar(1000)
set @foo1='Nothing returned'
set @foo2='Nothing returned'
set @foo3='Nothing returned'
exec myprocedure @foo1 output,@foo2 output,@foo3 output
print @foo1
print @foo2
print @foo3
drop proc myprocedure
drop table foobar
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
I may be missing something here but what about:
CREATE PROCEDURE myprocedure
@strOut1 varchar(5) = NULL OUT
, @strOut2 varchar(5) = NULL OUT
, @strOut3 varchar(5) = NULL OUT
AS
BEGIN
DECLARE @cmd nvarchar(1000)
SET @cmd = 'SELECT @sOut = Col1 FROM database.dbo.TABLE1'
EXEC master.dbo.sp_executesql
@stmt = @cmd
, @params = N'@sOut varchar(5) OUT'
, @iTmp = @strOut1 OUT
SET @cmd = 'SELECT @sOut = Col2 FROM database.dbo.TABLE2'
EXEC master.dbo.sp_executesql
@stmt = @cmd
, @params = N'@sOut varchar(5) OUT'
, @iTmp = @strOut2 OUT
SET @cmd = 'SELECT @sOut = Col3 FROM database.dbo.TABLE3'
EXEC master.dbo.sp_executesql
@stmt = @cmd
, @params = N'@sOut varchar(5) OUT'
, @iTmp = @strOut3 OUT
END
GO
If I understand the question correctly then this is the most elegant solution, and requires theleast amount of typing! Hope this helps.
. . Greg
Edited by - gmlucas on 06/05/2003 12:28:31 AM
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa -
Did you try any of these solutions? If so which one solved your problem, it seems to me some of the solutions were a bit too complex for what was required.
-
Yeh - these have been a great deal of help. Thanks people.
The complexity is due to the fact that the sql statements that are executed in the stored procedure are created dynamically by paramaters passed into it. I'm not sure that I made that very clear in my original question.
Thanks again
Edited by - ojl96 on 06/05/2003 02:03:38 AM
-
Here is an example:
CREATE PROC Author_Info
@Au_Id VARCHAR(14) OUT,
@Au_FName VARCHAR(14) OUT,
@Au_LName VARCHAR(14) OUT,
@Phone VARCHAR(14) OUT
AS
Select TOP 1 @Au_Id = au_id,
@Au_FName = au_fname,
@Au_LName = au_lname,
@Phone = Phone
From Pubs..Authors
Call the proc like this
Declare @Au_id VARCHAR(14),
@Au_FName VARCHAR(14),
@Au_LName VARCHAR(14),
@Phone VARCHAR(14)
Exec Pubs..Author_Info @Au_id OUT, @Au_FName OUT,
@Au_LName OUT, @Phone OUT
Select @Au_id, @Au_FName, @Au_LName, @Phone
MW
Edited by - mworku on 06/05/2003 10:02:04 AM
MW -
Creating a temp table (or table variable) is probably the best way, especially if the procedure is going to get large, or you want to manipulate the output with a sort...
-
What is the different between SELECT and SET
DECLARE @var varchar(100)
SELECT @var = 'select * from tablename'
SET @var = = 'select * from tablename'
-
Hi mlwang,
quote:
What is the different between SELECT and SETDECLARE @var varchar(100)
SELECT @var = 'select * from tablename'
SET @var = = 'select * from tablename'
results are in both cases the same. However, MS recommends using SET for variable assigment
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply