how to return outValue - 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

General SQL Server Forums

New to SQL Server Programming

how to return outValue

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-20 : 12:42:43

I have a SP.

ALTER Procedure [dbo].[CustomerFmLN]

@LN int = 3659,
@OutValue varchar(5) OUTPUT
AS
BEGIN
SELECT Customer FROM dbo.CustomerLN WHERE (dbo.CustomerLN.LN= @LN)
RETURN @OutValue
END

The query finds the value in the SQL management studio as customer, but @OutValue = null. How do I insert ( set ) the @OutValue ?

Thanks

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 13:28:51

[code]
ALTER Procedure [dbo].[CustomerFmLN]

@LN int = 3659,
@OutValue varchar(5) OUTPUT
AS
BEGIN
SELECT @OutValue=Customer FROM dbo.CustomerLN WHERE (dbo.CustomerLN.LN= @LN)
END

and call it like

DECLARE @RetParam varchar(5)

EXEC [dbo].[CustomerFmLN] @RetParam OUT

SELECT @RetParam
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-20 : 14:21:22

Visakh16 - thank you very much - you anticipated my subsequent question - how to use it !
I do have a quick question - in my SP I have some print statements - do they affect run time performance ?
They are nice for observing different parts when testing. Its easy to comment them out, but why if the dont impede execution ....when called by code. Or,, do they ?? Thanks !!!!

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 14:31:07

quote:
Originally posted by andrewcw

Visakh16 - thank you very much - you anticipated my subsequent question - how to use it !
I do have a quick question - in my SP I have some print statements - do they affect run time performance ?
They are nice for observing different parts when testing. Its easy to comment them out, but why if the dont impede execution ....when called by code. Or,, do they ?? Thanks !!!!

andrewcw

they dont affect performance
but usual practise is to supress them in defaukt execution mode by adding a @Debug parameter with default value 0 and print them only when its 1. this will be extremely helpful while calling and fetching results of procedure in some application other way the these messages can be really annoying when trying to populate objects like recordsets

so sp will be likw


ALTER Procedure [dbo].[CustomerFmLN]

@LN int = 3659,
@OutValue varchar(5) OUTPUT,
@Debug bit = 0
AS
BEGIN
SELECT @OutValue=Customer FROM dbo.CustomerLN WHERE (dbo.CustomerLN.LN= @LN) 
IF @DEbug=1
-- PRINt statements
END


and in case you need to see messages call like

DECLARE @RetParam varchar(5)

EXEC [dbo].[CustomerFmLN] @RetParam OUT,@Debug=1

SELECT @RetParam

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources