Multiple return values from stored procdure.

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

General SQL Server Forums

New to SQL Server Programming

Multiple return values from stored procdure.

Author

Topic

myksdsu
Starting Member

13 Posts

Posted - 2007-02-01 : 17:53:59

I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-01 : 18:12:28

Yes that is possible. But please provide an example so that we can ensure you heading down the correct path. You probably just would want to return a result set rather than using OUTPUT parameters.

Tara Kizer

myksdsu
Starting Member

13 Posts

Posted - 2007-02-01 : 18:42:27

I'm calling this from C#, and it was requested that it be done from a stored procedure, initially i was executing a query and getting the result set back. I was trying something like:

Can a sproc return a result set?

CREATE PROCEDURE dbo.GetUserInfo
(
@UserID VARCHAR(50),
@B1200_ID VARCHAR OUTPUT,
@B1200_PWD VARCHAR OUTPUT,
@BILLT_ID VARCHAR OUTPUT,
@BILLT_PWD VARCHAR OUTPUT,
@EPDT_ID VARCHAR OUTPUT,
@EPDT_PWD VARCHAR OUTPUT,
)
AS

SET NOCOUNT ON
SELECT@B1200_ID ,@B1200_PWD,@BILLT_ID ,@BILLT_PWD ,@EPDT_ID,@EPDT_PWD,
FROM CGA_USER
WHERE (CGA_USER_ID = @UserID);

PRINT @B1200_ID

RETURN
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-01 : 18:45:23

You should be returning columns not parameters in your select statement:

CREATE PROC dbo.GetUserInfo
(@UserID varchar(50))
AS

SET NOCOUNT ON

SELECT B1200_ID, B1200_PWD, BILLT_ID, BILLT_PWD, EPDT_ID, EPDT_PWD
FROM CGA_USER
WHERE CGA_USER_ID = @UserID

GO

Tara Kizer

myksdsu
Starting Member

13 Posts

Posted - 2007-02-01 : 19:06:35

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources