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.

  • It would be interesting to know what you plan to do with @StudentName once it has been assigned a value. – onedaywhen Feb 13 '12 at 9:53
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
  • Gbn: What if the values that are assigned to SET, can be fetched by another select statement – xorpower Feb 13 '12 at 8:47
  • @Xorpower: please update your question then with more examples. – gbn Feb 13 '12 at 8:48
  • I have edited the question. Please post another answer as this script might be useful some day. Thanks – xorpower Feb 13 '12 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 '12 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 – Martin Smith Feb 13 '12 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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