Assign variable in sql query select

Question

Declare @cardNumber;
select name, address, 
 (select @cardNumber = select top 1 value from card cd
         inner join cardNotuse cdnu on cd.id = cdnu.cardId
         where cdnu.type = 'CreditCard'),
case when (@cardNumber = 1) then 'TopPriority'
     when (@cardNumber = 2) then 'SecondPriority'
     Else 'Normal' end as PriorityLeval

from customer

I can't assign value to @cardNumber by somehow. Error I've got is

'Incorrect syntax near '='.

I couldn't figure out why I couldn't use the query like that.

Also, it is not permitted in sql server to return result from a select query as well as assign some value in a variable in same query. — Deep
– Deep, Commented Apr 30, 2015 at 9:56
Even fixing the syntax I doubt the query does what you want. Every single @cardNumber is the same. — paparazzo
– paparazzo, Commented Apr 30, 2015 at 10:16

Deepshikha · Accepted Answer · 2015-04-30 10:36:23Z

1

As variable @cardNumber is not related to customer table you can write two independent queries AS:

Declare @cardNumber int;
set @cardNumber =  (SELECT  top 1 value 
                       from card cd
                       inner join cardNotuse cdnu on cd.id = cdnu.cardId
                       where cdnu.[type] = 'CreditCard')
select  name,
        address, 
        @cardNumber,
        case when (@cardNumber = 1) then 'TopPriority'
             when (@cardNumber = 2) then 'SecondPriority'
             Else 'Normal' end as PriorityLeval
from customer

answered Apr 30, 2015 at 10:36

Deepshikha

10.3k2 gold badges24 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay2707 · Accepted Answer · 2015-04-30 10:16:35Z

0

Please check the sample and comment.

Declare @cardNumber varchar(50) --here datatype missing

select name, address, 
        (--select @cardNumber =  --you can not do 2 thing in one select query, either set variable or get result, not both in a single query
        select top 1 value from card cd
         inner join cardNotuse cdnu on cd.id = cdnu.cardId
         where cdnu.type = 'CreditCard'
         ),
case when (@cardNumber = 1) then 'TopPriority'
     when (@cardNumber = 2) then 'SecondPriority'
     Else 'Normal' end as PriorityLeval

from customer 

--1. Either set statment to get value 
set @cardNumber =  
        (select top 1 value from card cd
         inner join cardNotuse cdnu on cd.id = cdnu.cardId
         where cdnu.type = 'CreditCard'
         )
--2.  or in select query you can set,but result always be a single value as you taken top 1 will not work for you.
select @cardNumber = --this gives error as top clause will not work
        top 1 value from card cd
         inner join cardNotuse cdnu on cd.id = cdnu.cardId
         where cdnu.type = 'CreditCard'

--This will work. If a single value there, it return a value. If more than one value, set last record.
select @cardNumber = value _Id from card cd
             inner join cardNotuse cdnu on cd.id = cdnu.cardId
             where cdnu.type = 'CreditCard'

edited Apr 30, 2015 at 10:16

answered Apr 30, 2015 at 9:57

Ajay2707

5,8286 gold badges44 silver badges61 bronze badges

1 Comment

Deep Over a year ago

select @cardNumber = top 1 value from card cd invalid SQL Server statement. select top 1 @cardNumber = value from card cd

Collectives™ on Stack Overflow

Assign variable in sql query select

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related