0

I have been using SET keyword to assign the values to parameters declared in SQL Server something like,

Declare @StudentName Nvarchar(max) 
SET @StudentName='ABC'

I'm not sure how to write this if they are more than 1 student name.

Say student names are being fetched from another Select Statement (Select student_name from student_tbl)

Will that be

SET @StudentName= 'Select student_name from student_tbl'

I'm not sure how SET works in case of multiple values.

1
  • It would be interesting to know what you plan to do with @StudentName once it has been assigned a value.
    – onedaywhen
    Feb 13, 2012 at 9:53

1 Answer 1

4

Multiple values shouldn't be assigned to a scalar variable. It makes no sense

You need a table variable (or temporary table).

Declare @StudentName TABLE (sname Nvarchar(max));
INSERT @StudentName (sname) VALUES ('ABC'),('DEF'),('PQR'),('XYZ');

You can JOIN etc to this as you would a normal table. Or use it in an IN clause

WHERE
    X.SomeCol IN (SELECT sname FROM @StudentName)

Edit, after comments

INSERT @StudentName (sname) 
SELECT ... -- the other query here
7
  • Gbn: What if the values that are assigned to SET, can be fetched by another select statement
    – xorpower
    Feb 13, 2012 at 8:47
  • @Xorpower: please update your question then with more examples.
    – gbn
    Feb 13, 2012 at 8:48
  • I have edited the question. Please post another answer as this script might be useful some day. Thanks
    – xorpower
    Feb 13, 2012 at 8:51
  • @Xorpower: I've already edited. A set of rows goes into a table (whatever type), not a scalar variable
    – gbn
    Feb 13, 2012 at 8:52
  • 2
    @Xorpower - If you are trying to use a table variable in a join you need to give it an alias or reference it with square brackets to avoid the error Must declare the scalar variable "@Foo". Is that what you mean? e.g. use DECLARE @T TABLE(X INT); SELECT * FROM sys.objects o JOIN @T ON [@T].X = o.object_id Feb 13, 2012 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.