returning 2 output params - 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

SQL Server Development (2000)

returning 2 output params

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-07-17 : 09:33:19

Hi,
I have following code in a stored procedure as folows to return two output variables. As you can see i'm running the same query twice to get the return value for each varaible.

set @emaillist = (select emailList from GSF.dbo.ftpDestination where destinationid = @ftpdestinationid)

set @ftpdestinationname = (select [name] from GSF.dbo.ftpDestination where destinationid = @ftpdestinationid)

However i can return the two values using the query just onne to get both variables like so

select
emailList, [name]
from
GSF.dbo.ftpDestination
where
destinationid = @ftpdestinationid

The problem i am having is how to set both return variables just using the above query once.

Any help greatly appreciated

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 13:22:17

select @emaillist = emailList ,
@ftpdestinationname = [name]
from GSF.dbo.ftpDestination
where destinationid = @ftpdestinationid

==========================================
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.

Edited by - nr on 07/17/2003 13:22:41

Edited by - nr on 07/17/2003 13:22:57

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-17 : 13:42:41

When you are trying to put a column from a table into a variable, do not use SET. Use SELECT @Variable1 = Column1 FROM Table1 method. Use SET for things like this: SET @Variable2 = 0, SET @Variable3 = 'SQLTeam'.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 14:09:01

Or don't use SET at all...

Has anyone made a good argument for it's use?

Isn't:

SET @Variable2 = 0, SET @Variable3 = 'SQLTeam'

Mor overhead than

SELECT @Variable2 = 0, @Variable3 = 'SQLTeam'

Doesn't the optimizer have to prepare 2 statements rather than 1?

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 14:49:05

From bol
It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.

Not that's much of a reason.

==========================================
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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-17 : 14:53:41

I thought I had read that SET should be used, so thanks Nigel for that.

Putting it on one line just allows you to write less code.

In my previous post, the comma was used to separate the two examples.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 15:31:46

[homer]doooooooooooooooooooooooooooooooooooooooh[/homer]

Well it would be less reads BECAUSE I FORGOT THE DECLARE's IN THE SECOND STRING

Tried it again and they're the same

[homer]doooooooooooooooooooooooooooooooooooooooh[/homer]

Well, it looks like SET is more effecient

The following 2 SQL's show that there are more reads associated with the SELECT (not that I understand what SQL Profiler means specifically by "Reads")

DECLARE @X int, @y char, @z int, @a int, @b int, @c int
SELECT @x = 0, @y = 'x', @z = 0, @b = 0, @c = 0
GO

Is Less effecient than

DECLARE @X int, @y char
SET @x = 0
SET @y = 'x'
SET @z = 0
SET @a = 0
SET @b = 0
SET @c = 0
GO

FROM profiler (the numbers in red are the reads)

SQL:BatchCompleted DECLARE @X int, @y char, @z int, @a int, @b int, @c int
SELECT @x = 0, @y = 'x', @z = 0, @b = 0, @c = 0
SQL Query Analyzer sa 0 12 0 0 330 72 2003-07-17 15:27:59.707
SQL:BatchCompleted DECLARE @X int, @y char
SET @x = 0
SET @y = 'x'
SET @z = 0
SET @a = 0
SET @b = 0
SET @c = 0
SQL Query Analyzer sa 0 8 0 0 330 72 2003-07-17 15:27:59.740

Brett

8-)

Edited by - x002548 on 07/17/2003 15:33:22

Edited by - x002548 on 07/17/2003 15:36:35

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-17 : 18:49:28

While it's strictly a matter of preference, I like the fact that one SELECT can assign multiple variables, but more importantly, you can't use SET to set a variable equal to the results of a query/table. You'd have to use SELECT for that anyway. Almost every time I write a bit of code using SET @var = something I end up needing to add another var, or change it to get results from a table.

I've also settled on the philosophy that SET should be used for system settings, like SET ROWCOUNT or SET ANSI_NULLS ON. That is a nice, clean delineation for me that's pretty easy to follow...just natural for me I guess.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 18:57:07

set @t = 1
set @t = (select top 1 id from sysobjects)

Oops - wouldn't want to do 2 selects so
select @t = id, @n = name
from (select top 1 * from sysobject) a

Or - and I think this is a good reason for not using set to set variables

select @error = @@error, @rowcount = @@rowcount

unless anyone knows how to do that with set statements.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources