asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)

SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER

Regards,

Fernando

|||

It sude did!
Thanks!

assigning Select results to local vars in SP

Hi. I'd like to assign the results of a select statement to a local
variables in my stored procedure. My intent is something like this:
SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
only, some how I'd like to get the field2,field3 into variables. Can
this be done?
Thanks in advanceFirst of all, do not use TOP without using ORDER BY, unless selecting
somewhat random results is required (which I gues is not).
Other than that, this is the way to go:
select @.variable_name = owner.table.colum
from owner.table
where (owner.table.another_column = @.parameter)
Don't forget to look up using local variables in Books Online.
ML|||Johnny,
Something like this:
USE Pubs
GO
CREATE PROC TESTPROC
@.AID varchar(11)
AS
DECLARE @.FName varchar(30)
DECLARE @.LName varchar(30)
SELECT @.FName = au_fname, @.LName = au_lname
FROM authors
WHERE au_id = @.AID
PRINT @.FName + ' ' + @.LName
GO
EXEC TESTPROC '172-32-1176'
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127950731.353336.297780@.g43g2000cwa.googlegroups.com...
> Hi. I'd like to assign the results of a select statement to a local
> variables in my stored procedure. My intent is something like this:
> SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
> only, some how I'd like to get the field2,field3 into variables. Can
> this be done?
> Thanks in advance
>|||Thanks Jerry, I'll try this out!|||Just be careful! If the SELECT returns more than 1 rows, you will *not* get
an error. The
variable(s) will contain the value for an unspecified row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127953579.251983.177820@.o13g2000cwo.googlegroups.com...
> Thanks Jerry, I'll try this out!
>