Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

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
share|improve this answer
    
Gbn: What if the values that are assigned to SET, can be fetched by another select statement –  Romil N 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 –  Romil N 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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