-3

I expect to see an output of tables whtat have a date less then 201511 however I keep getting an error: Conversion failed when converting the varchar value '

IF  (201504 < 201511) 
        begin
            (0 + 1 )  
        end' to data type int.

declare     @TableMonth int,
            @CurrentMonth int = 0,

@Table_Name =   'xx_'+CAST(@TableMonth as varchar)+'_T' ,
set @CurrentMonth = 'IF  (' + cast(@TableMonth as varchar) + ' < 201511) 
begin
            (' + cast(@CurrentMonth as varchar) + ' + 1 )  
end';
Miss R
  • 23
  • 6

1 Answers1

1

You are trying to assign a varchar to an integer variable, so it shouldn't be surprising this fails - it's as if you're trying to build dynamic SQL without executing it.

Fortunately, the solution is rather simple:

set @CurrentMonth = case when @TableMonth < 201511 then @CurrentMonth + 1 end

I assume you handle year rollovers elsewhere.

Luaan
  • 62,244
  • 7
  • 97
  • 116
  • So I tried this first but I get a syntax error near end of line so i do – Miss R Nov 30 '15 at 09:02
  • heres the actual thread: http://stackoverflow.com/questions/33936975/return-data-before-current-month/33938749?noredirect=1#comment55641415_33938749 – Miss R Nov 30 '15 at 09:05
  • @MissR What kind of syntax error? It works fine for me. Maybe you need to use `select @CurrentMonth = case ...` on an older version of MS SQL, I'm not sure when it became possible to use `set` with any expression. – Luaan Nov 30 '15 at 09:08
  • @MissR Oh, I see now - you have to remove the `,` before the `set`. – Luaan Nov 30 '15 at 09:09
  • @MissR Well, must be something wrong with the code you have after this, then. Try to isolate the problem. "Still wouldnt work" doesn't really give us much to help you :) – Luaan Nov 30 '15 at 09:21