How to create a variable in select query and use it in the where clause to pass the paramaeter

Question

Post reply

How to create a variable in select query and use it in the where clause to pass the paramaeter

amar_kaur16

SSC Eights!

Points: 984
More actions
September 9, 2014 at 7:49 pm

#311479

I am writing a stored procedure and have a query where I create a variable from other table
[Code]
Declare @sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision
[/Code]
But I am getting error for Decision1 , Decision2
How can I do that.
Thanks,
Blyzzard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 1

The code is missing the FROM clause in addition to few minor syntax errors, here is a quick correction

😎

Declare @sem varchar (12) = Null

DECLARE @Decision varchar(1) = Null

Select

emplid

,name

, Semester

,Decision1= (select top (1) Decision1 from @tbldecision where reader=1)

,Decision2=(select top (1) Decision1 from @tbldecision where reader=2)

FROM @tbldecision

Where Semester=@Sem

And Decision1=@Decision

Edit: missed the subqueries.

amar_kaur16 SSC Eights! Points: 984 More actions · Answer 2

Thanks for correcting the syntax but I am still getting error for Decision1 in where clause.

Do I need to use the subquery for Decision1 in where clause?

Thanks,

Blyzzard

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amar_kaur16 SSC Eights! Points: 984 More actions · Answer 4

amar_kaur16

SSC Eights!

Points: 984

September 10, 2014 at 7:10 am

#1744417

The error is :

Invalid column name Decision1

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

amar_kaur16 (9/10/2014)
The error is :
Invalid column name Decision1

That means the column isn't in the table. Also, if you are going to use the TOP 1 solution posted by Eirikur you should also include an ORDER BY so you know which row will be top 1.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

chris.johnson.0120 SSC-Addicted Points: 491 More actions · Answer 6

Yes, you need to use the Decision1 subquery in the WHERE clause in place of Decision1. Decision1 in the query you have written is a column alias, meaning the column with the results from the subquery will be labeled Decision1, however column aliases are part of the SELECT clause, which is evaluated by the engine after the WHERE clause, so column aliases can't be used in the WHERE clause.

Hope that makes sense.