how to capture stored proc return value

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)

how to capture stored proc return value

Author

Topic

btayfun
Starting Member

2 Posts

Posted - 2005-06-23 : 12:22:54

hello everybody,

i wrote the following stored proc:
CREATE PROCEDURE [SerialNoExists]
(@SerialNo nvarchar(64))
AS

If Exists(Select Serial_No From ITEM Where Serial_No=@SerialNo)

Return -1

Else
Return 0
GO

what i am asking is how will i capture the return value in vb.net?

thanks for any reply :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-23 : 12:29:42

use an output parameter:

CREATE PROCEDURE [SerialNoExists]
(@SerialNo nvarchar(64),
@retValue int output
)
AS
If Exists(Select Serial_No From ITEM Where Serial_No=@SerialNo)
set @retValue = 1
Else
set @retValue = 0

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-23 : 13:16:48

for c# you can do this (I assume a similar thing is available for vb.net):


SqlCommand cmd = new SqlCommand("myProc", myConnection);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parm = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnValue

after executing the command, parm will contain your SPs return value.

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-23 : 13:29:54

Spirit1 is right,
you could also ask your calling page to return a recordset and test to see if the bof and eof properties are true or false

Afrika

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-23 : 13:47:30

I think the root of btayfun's question was how to get the return value of ANY stored procedure. Every SP ever written has a return value whether the author explicitly returns one or not. ADO.NET has provided a way to access that by way of a commandParameter object. Developers should know how to access it, don't you think?

ie:


--Some existing proc with no explicit return value
create proc junk as 
declare @int int
set @int = 1/0

select @int [@int]
go

--exec the proc and get the return code
declare @returnCode int
exec @returnCode = junk
select @returnCode [@returnCode]
go

drop proc junk

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-23 : 13:53:46

well it looks like our cross-posting friend can pick the answer he like best :)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51511

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources