Dynamic SQL with parameters

Question

I have a SQL query stored in a table that contains parameter names. I need to know how to execute it properly in a stored procedure.

This is my SQL code in the procedure

PROCEDURE [spMassUpdateSKUs]
@SKU AS NVARCHAR(20)
,@FIELD AS NVARCHAR(50)
,@VALUE AS NVARCHAR(50)
,@RESULT as Int = Null Output
AS
BEGIN
IF EXISTS(SELECT CODENUMBER FROM INVENTORY_MASTER WHERE CODENUMBER=@SKU)
    BEGIN   
    DECLARE @SQLQUERY AS NVARCHAR(50)
    SET @SQLQUERY=(SELECT SQLUPDATE FROM MASS_UPDATE WHERE DROPDOWNLABEL=@FIELD)
    EXEC SP_EXECUTESQL @SQLQUERY
    END

and this is the sql query from the table

update inventory_master_flex set departmentid=@value where codenumber=@sku

I've tried replacing with the real parameters but that doen't work.

SELECT REPLACE(REPLACE(@SQLQUERY,'@VALUE',@VALUE),'@SKU',@SKU)

When you executed this SELECT REPLACE(REPLACE(@SQLQUERY,'@VALUE',@VALUE),'@SKU',@SKU) what did the update query look like? Did it look correct or not? What do you mean it doesn't work? If departmentid and codenumber are strings you need single quotes around them. — Rick S
– Rick S, Commented Dec 12, 2016 at 20:05
The capitalization is different. By default, SQL Server is case-insensitive, but your table, database, or server might be configured differently. — Gordon Linoff
– Gordon Linoff, Commented Dec 12, 2016 at 20:05
Your values won't be quoted with your current REPLACE code. You should pass the parameters properly though, using sp_executesql — Blorgbeard
– Blorgbeard, Commented Dec 12, 2016 at 20:07

IVNSTN · Accepted Answer · 2016-12-12 20:11:14Z

1

-- 50 is too short for sure; you may try 1000 or different number
DECLARE @SQLQUERY AS NVARCHAR(MAX)

-- for debug purpose
PRINT @SQLQUERY

-- params
EXEC SP_EXECUTESQL @SQLQUERY, N'@Value NVARCHAR(50), @sku NVARCHAR(50)`, @Value, @sku

REPLACE is not good in case of strings with quotes and so on which would brake the @sqlquery code.

answered Dec 12, 2016 at 20:11

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2016-12-12 20:08:40Z

1

Pass the parameters in using sp_executesql, not replace():

IF EXISTS(SELECT CODENUMBER FROM INVENTORY_MASTER WHERE CODENUMBER=@SKU)
    BEGIN   
        DECLARE @SQLQUERY AS NVARCHAR(MAX);

        SET @SQLQUERY = (SELECT SQLUPDATE FROM MASS_UPDATE WHERE DROPDOWNLABEL = @FIELD);

        EXEC SP_EXECUTESQL @SQLQUERY, N'@SKU VARCHAR(255), @VALUE VARCHAR(255)', @SKU = @SKU, @VALUE = @VALUE
    END;

I don't know what the types are. But if one or both are strings or dates, then you would need single quotes in your implementation. However, you are already using sp_executesql so go whole-hog and pass in parameters as well.

answered Dec 12, 2016 at 20:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL with parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related