How do I test OUTPUT variable ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How do I test OUTPUT variable ?

Author

Topic

tuka
Starting Member

26 Posts

Posted - 2006-01-12 : 03:52:23

Hi All,

I have the following stored procedure which I created using a fairly standard pattern for stored procedure (using error variable and transaction). i based it on a few examples:

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'procUpdateTradeLead')
DROP PROCEDURE procUpdateTradeLead
GO

/* Coding of the Procedure */
CREATE PROCEDURE procUpdateTradeLead
--Input Parameters (for tb_tradelead)
@id_tradelead int,
@subject nvarchar(100),
@keywords nvarchar(100),
@description nvarchar(2000),

-- Output Parameters
@errorParameter int OUTPUT -- Output Parameters
AS

BEGIN TRAN
-- Entry into 1st table
UPDATE tb_tradelead
SET id_tradelead_type = @id_tradelead_type,
subject = @subject,
keywords = @keywords,
description = @description
WHERE id_tradelead = @id_tradelead
SET @errorParameter = @@ERROR
-- Test to rollback or commit
IF @errorParameter <> 0
BEGIN
ROLLBACK TRAN
RETURN @errorParameter
END
ELSE
BEGIN
COMMIT TRAN
RETURN @errorParameter
END

I test the SP using
exec procUpdateTradeLead 40,2,'Modified TL','keywords 123','description',0

where the 0 is I presume the error parameter. What I find odd is that I need to define the error parameter to execute the query even though it is an OUTPUT parameter otherwise if I omit 0 I get the following error:

Server message
Procedure 'procUpdateTradeLead' expects parameter '@errorParameter', which was not supplied.

Since I use this query within a .NET application, I dont get an error but the query has no effect. Does anyone know how to test on the SQL level the above sp ?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 04:04:38

U need to declare the output parameter and pass it while executing ...
declare @errorParameter int
exec procUpdateTradeLead 40,2,'Modified TL','keywords 123','description',@errorParameter OUTPUT
print @errorParameter

tuka
Starting Member

26 Posts

Posted - 2006-01-14 : 11:01:32

Thanks for your input,

I simplified my SP just to return value and eliminated the error parameter because of the awkward testing. Additional parameters I guess would be useful if I had more than one return value which is rarely the case. Anyway, testing was simplified without the additional parameter.

As for the return value in .NET, I use the SqlParameter collection and declare a variable there for the return value. the link below show how to arrive at the return value using the SqlParameterCollection that works great !!!

http://www.mcse.ms/message1454145.html

Thanks again Gupta !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources