output parameter - 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 2000 Forums

Transact-SQL (2000)

output parameter

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-10-04 : 01:09:21

I am trying to upgrade my site to .net and a few of the SP's to use output paramters. What I would like to do is return an output paramter if the Insert here fails. I am not sure I am going about it the right way. I think I have to have an @@OUTPUT in the paramters?? So I can grab it from my asp.net code?? Any suggestions appreciated.

Thanks alot

Mike123

CREATE PROCEDURE insert_tblInstantMessage
(
@MessageToID [int],
@MessageFromID [int],
@Message [varchar](500),
@Date [smalldatetime]
)
AS SET NOCOUNT ON

IF EXISTS(SELECT userID from tblBlockList WHERE userID = @messageToID and BlockUserID = @MessageFromID)
RETURN '1'
else

INSERT INTO [tblInstantMessage]
(
[MessageToID],
[MessageFromID],
[Message],
[Date],
[Checked]
)
VALUES
(
@MessageToID,
@MessageFromID,
@Message,
@Date,
'0'
)

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-04 : 04:23:57

You are setting a return code not an output parameter.
Note that with ado.net you will have to name the parameters the same way as in the SP.

CREATE PROCEDURE insert_tblInstantMessage
(
@MessageToID [int],
@MessageFromID [int],
@Message [varchar](500),
@Date [smalldatetime] ,
@ReturnCode int output
)
AS SET NOCOUNT ON

set @ReturnCode = 0

IF EXISTS(SELECT userID from tblBlockList WHERE userID = @messageToID and BlockUserID = @MessageFromID)
set @ReturnCode = 1
else

INSERT INTO [tblInstantMessage]
(
[MessageToID],
[MessageFromID],
[Message],
[Date],
[Checked]
)
VALUES
(
@MessageToID,
@MessageFromID,
@Message,
@Date,
'0'
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-10-10 : 06:33:31

thanks for the help

does this mean I have to pass a value to the output parameter ??? if so what should I pass??

Thanks

mike123

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-10-10 : 06:59:03

Mike,

Every stored procedure has a Return Value. It must be of type int so your statement: RETURN '1' ..should be RETURN(1) ..brackets are optional but make it a little neater.

IMHO it is good practice to always bind this parameter to the Command object of a stored procedure. Its initial value does not have to be set. If the stored proc runs successfully the Return Value is set to 0.

So basically for a single output parameter of type int, the return value is the way to go.

HTH

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-10-10 : 17:37:57

thanks david for all my single output SP's I will now do it that way.

What about when I return multiple outputs and need to return a GUID as well as a couple strings...?? do I have to pass a value to the output parameter ??? if so what should I pass??

thanks again

mike123

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-10-10 : 17:58:27

Mike,

When you need more than one output parameter or the value is not of type int then you will have to declare them and assign to them. You can assign a default to the output parameter and that default can be NULL. Give it a test...

Another option is to return another recordset but then the overhead is higher.

HTH

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources