CODEWITHSUNDEEP
sql-server
TheNewone
TheNewone

Reputation: 97

Add column name as parameter in stored procedure not working

I have a stored procedure where I want to add a column name as a parameter. The procedure updates some columns like this.

UPDATE Reservedele
SET Bemærkning = @Bemærkning
    ,Art = @Art
    ,Type = @Type
    ,Lev = @Lev
    ,@stockNumber = @stockCount
WHERE Varenummer = @Varenummer

I want to update a column named as stock1 as I pass in as @StockNumber, but all it does is setting @stockNumber equal to @Varenummer. (Which probably what I am telling it to do).

How can I do this?

I am new to SQL and I need to read up on dynamic SQL.

My full code looks like this, but the update statement is not doing anything.

ALTER PROCEDURE [dbo].[updateItemsInSqlNoImage]
@Bemærkning nvarchar(200),
@Varenummer nchar(20),
@Art nvarchar(50),
@Type nvarchar(50),
@Lev nvarchar(50),
@Bruger nvarchar(50),
@Dato nvarchar(50),
@Tid nvarchar(50),
@Ip nvarchar(50),
@stockCount int,
@stockNumber varchar(10)

as  

BEGIN
            DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    SET @SQL = N'UPDATE Reservedele' + @CRLF+
               N',Bemærkning=@Bemærkning'+ @CRLF+
               N',Art=@Art' + @CRLF+
               N',Type=@Type' + @CRLF+
               N',Lev=@Lev'+ @CRLF+
               N'    ,' + QUOTENAME(@stockNumber) + N' = @stockCount' + @CRLF +
               N'WHERE Varenummer=@Varenummer;';

    INSERT INTO Ændrede_dele(Varenummer,Dato,Tid,Bruger,Ip)
    VALUES (@Varenummer,@Dato,@Tid,@Bruger,@stockNumber)
END

Upvotes: 0

Views: 229

Answers (1)

Thom A
Thom A

Reputation: 95534

That isn't how SQL works, it isn't a scripting language, it's a query language. A variable cannot be used to replace a something that needs to be a literal value. @stockNumber is being seen as the parameter @stockNumber, not the value of the parameter, @stockNumber. As a result it makes it look like you have a SET operation that is part of an UPDATE but also trying to assign the value of variables/parameters (which you can't do).

You would need to use dynamic SQL to achieve this:

--All data types are guessed, replace appropriately
CREATE PROC dbo.YourProc @Bemærkning varchar(10),
                         @Art int,
                         @Type int,
                         @Lev decimal(6,2),
                         @stockNumber sysname, --apart from this one, this is correct
                         @stockCount int,
                         @Varenummer varchar(25) AS
BEGIN
    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'UPDATE Reservedele' + @CRLF +
               N'SET Bemærkning = @Bemærkning' + @CRLF +
               N'    ,Art = @Art' + @CRLF +
               N'    ,Type = @Type' + @CRLF +
               N'    ,Lev = @Lev' + @CRLF +
               N'    ,' + QUOTENAME(@stockNumber) + N' = @stockCount' + @CRLF +
               N'WHERE Varenummer = @Varenummer;';

    --PRINT @SQL; --Your debugging friend

    --Again, all data types are guessed, replace appropriately
    EXEC sys.sp_executesql @SQL, N'@Bemærkning varchar(10), @Art int, @Type int, @Lev decimal(6,2), @stockCount int, @Varenummer varchar(25)', @Bemærkning, @Art, @Type, @Lev, @stockCount, @Varenummer;
END;

Upvotes: 7

Related Questions