Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure

Author

Topic

sreenathk
Starting Member

1 Post

Posted - 2009-01-15 : 15:40:17

Hi Guys,

Can I use a stored Procedure which has 1 input value and 3 output values.

1st Output Value - will be a string

2nd Output value will be a resultset.

3rd Output value will be a string.

Please let me know how to proceed further in this scenario?

Thanks,

Sree

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-15 : 16:45:05

Use two parameters and return the result set in the body of the stored procedure.

CREATE PROC Proc1
(@var1 int, @var2 varchar(5) OUTPUT)
AS
SELECT ...
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-15 : 16:55:24

Yes you can have procedure with multiple outputs. As for resultset, it has to be a select statement in the procedure. Stored procedure can not return results as table; Function can do it.


create proc proc_xxxxxx (@i int, @o1 varchar(10) output, @o2 varchar(10) output)
as
select @o1 = v1, @o2 = v2 from t1 where id = @i -- set  your values for output
select * from t1 where id = @i -- Your result set query

-----------------------------

Execute your proc:

declare @out1 varchar(10), @out2 varchar(10)
exec proc_xxxxxx 1, @o1=@out1 output, @o2 = out2 output

select @out1, @out2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources