I mentioned here that SELECT INTO in Sql Server is functionally similar to CREATE TABLE AS in Oracle. Oracle also has SELECT INTO, but it is used for assigning query results to a variable.
Here is a PL/SQL code snippet:
declare MyVariable varchar2(20);
Begin
select ColumnName into MyVariable from MyTable where MyID = SomeInteger;
dbms_output.put_line('Hello ' || MyVariable);
End
In the above example, a column value for a particular record is assigned to MyVariable and printed out.
How do you assign select results into T-Sql variables in Sql Server then? Here is a code sample that does the same thing above:
declare @MyVariable varchar(20) select @MyVariable = ColumnName from MyTable where MyId = SomeInteger print 'Hello ' + @MyVariable
3 responses to “Assign SELECT results into variables”
Hey Haidong, long time since I read your blog, but now it’s back in the feed reader again.
Regarding this post, I prefer the following query:
set @MyVariable = (select ColumnName from MyTable where MyId = SomeInteger)
Note that this is not exactly the same query though. This will result in an error if the select-statement returns multiple rows. The query that you used will not result in an error, @MyVariable will be assigned the value of the column ColumnName in the last row of the resultset. Of course, this might be what you want, but if not I think it is better to fail fast.
Thanks Chris. That is a great point! That is a great defensive coding practice. Better safe than sorry.
Glad you are back. Hope all is well with you and the family.
Exactly, in my opinion a program should fail as fast as possible instead of hiding possible errors and fail in inconsistent ways later.
The family is great, Kiron is 9,5 months and is just starting to walk. He is a fantastic kid, always happy. But I guess most parents feel that way about their own kids. 🙂
Everything good with you and the family as well?