April 21, 2011 at 2:49 am
I write an stored procedure...
the select statement returns more than one value.. how i assign that to a variable .. is it possible
April 21, 2011 at 3:14 am
If all the values are the same then you could use group by.
CREATE TABLE #tmp (f1 INT)
INSERT INTO#tmp VALUES (1)
INSERT INTO#tmp VALUES (1)
INSERT INTO#tmp VALUES (1)
--SELECT * FROM #tmp T
DECLARE @myval INT
SELECT @myval = f1 FROM #tmp GROUP BY f1
PRINT @myval
April 21, 2011 at 5:09 am
If you're assigning variables from a SELECT statement you just do this:
DECLARE @FirstVariable int, @SecondVariable int, @ThirdVariable int
SELECT @FirstVariable = FirstColumn,
@SecondVariable = SecondColumn,
@ThirdVariable = ThirdColumn
FROM MyTable
But, that query can only return a single row. If it returns multiple rows, only the last one returned will end up in the variables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 5:23 am
usharani.t (4/21/2011)
I write an stored procedure...the select statement returns more than one value.. how i assign that to a variable .. is it possible
What you are trying to do with that stored procedure?
Why you need to get only one value is the requriment is like that?
Why do you want to assign to a variable?
create procedure proc1 (<input>)
as
Set Nocount ON
Begin
Select Top 1 * from your_table where condition=<input>
order by <your condition> asc/desc
End
Set Nocount OFF
Thanks
Parthi
April 21, 2011 at 6:03 am
Thanks For you reply.......
April 21, 2011 at 6:03 am
Thanks For Your Reply......
April 21, 2011 at 6:04 am
Thanks For Your Reply......
April 21, 2011 at 6:07 am
usharani.t (4/21/2011)
Thanks For Your Reply......
I think for three members you replied 3-Times thanks 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 21, 2011 at 10:10 am
Grant Fritchey (4/21/2011)
If it returns multiple rows, only the last one returned will end up in the variables.
I formed a habit from the beginning to explicitly grab one row when assigning into variables so I could be sure to get what I need, or get nothing, but this is good info for reviewing code...I never tried it on multiple rows to see which values would end up in the variables.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply