CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Kiera Smith
Kiera Smith

Reputation: 331

Must declare the scalar variable date in case satatement

I am trying to update a column with the current period for a few months of the year. As such, I use a switch statement with a month variable. However, I get the error:

Must declare the scalar variable

I have tried the following:

DECLARE @DATE_AP dateTime
DECLARE @month varchar(max)

SET @DATE_AP= DATEADD(year, 0, GETDATE())
SET @month_AP = DATENAME(Month, @DATE_AP)
SELECT CAST(@month as VARchar(10))

 select @periodsetvar = 'select CASE @month 

     when    ''October'' then 
     ''Update tbltimes set Periodyr = 01''

     when    ''November'' then 
     ''Update tbltimes set Periodyr  = 02''

     when    ''December'' then 
    ''Update tbltimes set Periodyr  = 03''

     when    ''January'' then 
    ''Update tbltimes set Periodyr  = 04''

     when    ''February'' then 
     ''Update tbltimes set Periodyr  = 05''

END'

exec (@periodsetvar)

and:

DECLARE @DATE_AP dateTime
DECLARE @month varchar(max)

SET @DATE_AP= DATEADD(year, 0, GETDATE())
SET @month_AP = DATENAME(Month, @DATE_AP)
SELECT CAST(@month as VARchar(10))

 select @periodsetvar = 'select CASE'+   ' '+ @month+'

     when    ''October'' then 
     ''Update tbltimes set Periodyr = 01''

     when    ''November'' then 
     ''Update tbltimes set Periodyr  = 02''

     when    ''December'' then 
    ''Update tbltimes set Periodyr  = 03''

     when    ''January'' then 
    ''Update tbltimes set Periodyr  = 04''

     when    ''February'' then 
     ''Update tbltimes set Periodyr  = 05''

END'

exec (@periodsetvar)

Upvotes: 0

Views: 5502

Answers (3)

Indian
Indian

Reputation: 527

If you essentially want to use dynamic SQL, you need to declare your @month variable in dynamic query:

DECLARE 
    @DATE_AP dateTime,
    @month_AP varchar(max),
    @periodsetvar varchar(max)

SET @DATE_AP= DATEADD(year, 0, GETDATE())
SET @month_AP= DATENAME(Month, @DATE_AP)

SELECT @periodsetvar =
 'declare @month varchar(max)
  set @month = ''' + @month_AP+ '''

    select 
        CASE @month 
            when ''October'' then ''Update tbltimes set Periodyr = 01''
            when ''November'' then ''Update tbltimes set Periodyr  = 02''
            when ''December'' then ''Update tbltimes set Periodyr  = 03''
            when ''January'' then ''Update tbltimes set Periodyr  = 04''
            when ''February'' then ''Update tbltimes set Periodyr  = 05''

END'

exec (@periodsetvar)

Upvotes: 0

Christian Barron
Christian Barron

Reputation: 2755

Instead of using Dynamic SQL which is susceptible to SQL Injection use a simple statement like this:

DECLARE @DATE_AP dateTime
Declare @periodsetvar char(2)

SET @DATE_AP= DATEADD(year, 0, GETDATE())

Set @periodsetvar =  CASE DATENAME(Month, @DATE_AP) 
                        when 'October' then '01'
                        when 'November' then '02'
                        when 'December' then '03'
                        when 'Janurary' then '04'
                        when 'February' then '05'
                     end
Update tbltimes 
set Periodyr  = @periodsetvar

If you are 100% determined to use Dynamic SQL then you should make sure you do it a bit more safe and parameterised such as below:

DECLARE @DATE_AP dateTime
DECLARE @month varchar(10)

SET @DATE_AP= DATEADD(year, 0, GETDATE())
SET @month = DATENAME(Month, @DATE_AP)

 select @periodsetvar = 'select CASE @month 

     when    ''October'' then 
     ''Update tbltimes set Periodyr = 01''

     when    ''November'' then 
     ''Update tbltimes set Periodyr  = 02''

     when    ''December'' then 
    ''Update tbltimes set Periodyr  = 03''

     when    ''January'' then 
    ''Update tbltimes set Periodyr  = 04''

     when    ''February'' then 
     ''Update tbltimes set Periodyr  = 05''

END'

exec sp_executesql @periodsetvar, N'@month varchar(10)', @month

sp_executesql will allow you to declare parameters within dynamic sql making it much safer to execute.

Upvotes: 5

Uberzen1
Uberzen1

Reputation: 415

You need to add declarations for all of your variables:

DECLARE @DATE_AP dateTime
DECLARE @month varchar(max)
DECLARE @month_AP varchar(max)
DECLARE @periodsetvar varchar(max)

SET @DATE_AP= DATEADD(year, 0, GETDATE())
SET @month_AP = DATENAME(Month, @DATE_AP)
SELECT CAST(@month as VARchar(10))

select @periodsetvar = 'select CASE'+   ' '+ @month+'

 when    ''October'' then 
 ''Update tbltimes set Periodyr = 01''

 when    ''November'' then 
 ''Update tbltimes set Periodyr  = 02''

 when    ''December'' then 
''Update tbltimes set Periodyr  = 03''

 when    ''January'' then 
''Update tbltimes set Periodyr  = 04''

 when    ''February'' then 
 ''Update tbltimes set Periodyr  = 05''

END'

exec (@periodsetvar)

Upvotes: 0

Related Questions