CODEWITHSUNDEEP
sql-server
Amit Sharma
Amit Sharma

Reputation: 21

Msg 137, Must declare the scalar variable

Declare @SKUCode1 varchar(30), @SKUCode2 varchar(30), @SKUCode3 varchar(30),
        @SKUCode4 varchar(30), @SKUCode5 varchar(30), @SKUCode6 varchar(30),
        @SKUCode7 varchar(30), @SKUCode7 varchar(30), @SKUCode8 varchar(30),
        @SKUCode10 varchar(30), @Temp_SKU varchar(30), @SKUCodeS varchar(30),
        @sp1 nvarchar(max),@Index int

Set @SKUCodeS = 'SKUCode'
Set @Index = 0

While @Index < 10   
Begin
    Set @Index = @Index + 1  

    Select @Temp_SKU = SKUCode  
    From dbo.UDA_Order  
    Where Areaname = 'LMC-TYRE BUILDING'

    Set @sp1 = 'Set'+' '+ @SKUCodeS+CONVERT(varchar,@Index)+' '+'='+' '+'@Temp_SKU'  

    Exec sp_executesql @sp1  
End

This is throwing error

Msg 137,Level 15, State 2, Line 1
Must declare the scalar variable "@Temp_SKU"

I have no clue why it is happening.

Please help.

Upvotes: 2

Views: 20454

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15150

You're using @Temp_SKU in the dynamic query. When the dynamic query is fired you have to declare it in the scope of the dynamic query, I'd advise using a different name:

Set @sp1 = 'Set'+' '+ @SKUCodeS+CONVERT(varchar,@Index)+' '+'='+' '+'@Temp_SKUDYN'  
Exec sp_executesql @sp1  N'@Temp_SKUDYN varchar(30)' @Temp_SKUDYN = @@Temp_SKU

However, what are you actually trying to do? It looks like you're trying to set a local variable dynamically. In that case you have to use something like:

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

(Copied from here)

EDIT

Not at all sure now this will be a solution for you. Better you explain what exactly you are trying to accomplish.

EDIT 2

I still think it could be easier, but this is at least clearer (untested, may need some work):

Declare @SKUCode1 varchar(30), @SKUCode2 varchar(30), @SKUCode3 varchar(30),
        @SKUCode4 varchar(30), @SKUCode5 varchar(30), @SKUCode6 varchar(30),
        @SKUCode7 varchar(30), @SKUCode8 varchar(30), @SKUCode9 varchar(30),
        @SKUCode10 varchar(30), @Temp_SKU varchar(30), @SKUCodeS varchar(30),
        @sp1 nvarchar(max),@Index int

CREATE TABLE ##codes (i INT, Val VARCHAR(30))
;

Set @SKUCodeS = 'SKUCode'
Set @Index = 0

While @Index < 10   
Begin
    Set @Index = @Index + 1  

    Select @Temp_SKU = SKUCode  
    From dbo.UDA_Order  
    Where Areaname = 'LMC-TYRE BUILDING'
        AND MachineNumber = @Index

    Set @sp1 = 'insert into ##codes(i, Val) VALUES('+ @Index + ',' + @Temp_SKU + ' )'-- 'Set'+' '+ @SKUCodeS+CONVERT(varchar,@Index)+' '+'='+' '+'@Temp_SKU'  

    Exec sp_executesql @sp1  
END

SELECT      @SKUCode1 = 1
,           @SKUCode2 = 2
,           @SKUCode3 = 3
,           @SKUCode4 = 4
,           @SKUCode5 = 5
,           @SKUCode6 = 6
,           @SKUCode7 = 7
,           @SKUCode8 = 8
,           @SKUCode9 = 9
,           @SKUCode10 = 10
FROM        (
                SELECT  *
                FROM    ##codes AS C
                PIVOT   (
                            MAX(VAL)
                            FOR i IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
                        ) p
            ) piv

DROP TABLE ##codes

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239794

Okay, so the first task - assign appropriate values to each variable, can be done like this:

--Sample data
declare @t table (SKUCode varchar(30) not null,MachineNo int not null)
insert into @t(SKUCode,MachineNo) values
('abc',1),
('def',2),
('ghi',3),
('jkl',4),
('mno',5),
('pqr',6),
('stu',7),
('vwx',8),
('uzA',9),
('BCD',10)

--Actual query
Declare @SKUCode1 varchar(30), @SKUCode2 varchar(30), @SKUCode3 varchar(30),
        @SKUCode4 varchar(30), @SKUCode5 varchar(30), @SKUCode6 varchar(30),
        @SKUCode7 varchar(30), @SKUCode8 varchar(30), @SKUCode9 varchar(30),
        @SKUCode10 varchar(30)

select @SKUCode1 = [1], @SKUCode2 = [2], @SKUCode3 = [3],
       @SKUCode4 = [4], @SKUCode5 = [5], @SKUCode6 = [6],
       @SKUCode7 = [7], @SKUCode8 = [8], @SKUCode9 = [9],
       @SKUCode10 = [10]
from (select * from @t pivot (MAX(SKUCode) for
      MachineNo in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) u
      where 1=1 --Areaname, couldn't be bothered to include it here
      ) t

select @SKUCode5

Which prints mno, as it should. And we've avoided any nasty loops, dynamic SQL, etc.

Whether the next part of your problem is now easily solved, I don't know.

Upvotes: 1

steenbergh
steenbergh

Reputation: 1771

You add @Temp_SKU to the string that gets executed dynamically. However, sp_execute is unaware of your context and doesn't have a reference to @TEMP_SKU. Try adding the value of @Temp_SKU in stead of a refecence to the variable by removing the quotes:

 Set @sp1 = 'Set'+' '+ @SKUCodeS+CONVERT(varchar,@Index)+' '+'='+' '+ @Temp_SKU

Try printing @sp1 before the call to sp_execute to see what actually gets executed.

Upvotes: 0

Related Questions