sp_executesql help

  • Hi,

    I'm trying to write a stored procedure that would allow me to edit products. I will be creating various modules

    that allow certain people to edit a subset of fields in the product table. (e.g. some people can edit only the

    price while others can edit just the description, etc.) However, rather than creating one stored procedure

    for each module, I want to create 1 stored procedure, which is used by all. As the modules will have

    different number of fields, I want to generate the SQL dynamically in the stored procedure.

    However I'm running into some problems when using sp_executesql. Below is a snippet of the stored procedure:

    ALTER PROCEDURE [dbo].[sp_updateProduct]

    -- Add the parameters for the stored procedure here

    @Product_Id int = null,

    @Product_LastModified datetime = null,

    @Product_Name varchar(100) = null,

    @product_description varchar(max) = null,

    @Product_Active bit = null,

    @Product_Points int = null,

    @Product_Price numeric(18, 2) = null,

    @Product_Keywords varchar(500) = null,

    @Product_FeaturedHomePage bit = null,

    @Product_DisplayHomePage bit = null,

    @Product_Metatitle varchar(100) = null,

    @Product_Metadescription varchar(500) = null,

    @Product_Metakeywords varchar(500) = null,

    @Product_Safeurlname varchar(100) = null

    AS

    DECLARE @SQLStatement nvarchar(max)

    DECLARE @ParamDefinition nvarchar(max)

    DECLARE @ParamValues nvarchar(max)

    BEGIN

    SET @SQLStatement = 'UPDATE Product '

    + ' SET '

    + ' Product_Name = @P_Name '

    SET @ParamDefinition = N' @P_Name varchar(100) '

    SET @ParamValues = @Product_Name

    IF @product_description is not null

    BEGIN

    SET @SQLStatement = @SQLStatement + ' , Product_Description = @P_Description '

    SET @ParamDefinition = @ParamDefinition + N', @P_Description varchar(max) '

    SET @ParamValues = @ParamValues + ', ' + @product_description

    END

    IF @Product_Active is not null

    BEGIN

    SET @SQLStatement = @SQLStatement + ' , Product_Active = @P_Active '

    SET @ParamDefinition = @ParamDefinition + N', @P_Active bit '

    SET @ParamValues = @ParamValues + ', @P_Active = @Product_Active '

    END

    IF @Product_Points is not null

    BEGIN

    SET @SQLStatement = @SQLStatement + ' , Product_Points = @P_Points '

    SET @ParamDefinition = @ParamDefinition + N', @P_Points int '

    SET @ParamValues = @ParamValues + ', @P_Points = @Product_Points '

    END

    EXECUTE sp_executesql @SQLStatement, N@ParamDefinition, @ParamValues

    If I comment out the 3 if statements, leaving the product name only, it works fine. But if I add the description

    part, it will error out. I'll get errors like:

    Incorrect syntax near 'N@ParamDefinition'.

    and

    The parameterized query '( @P_Name varchar(100) , @P_Description varchar(max) )UPDATE Pro' expects the parameter '@P_Description', which was not supplied.

    depending on where I put the N for Unicode. I've been looking for a solution to this for some time and any help

    would be appreciated.

    Sincerely,

    Jason

  • You're missing an "END", which you would've been more likely to spot had you formatted your query as below 😉

    ALTER PROCEDURE [dbo].[Sp_updateproduct]

    -- Add the parameters for the stored procedure here

    @Product_Id INT = NULL,

    @Product_LastModified DATETIME = NULL,

    @Product_Name VARCHAR(100) = NULL,

    @product_description VARCHAR(MAX) = NULL,

    @Product_Active BIT = NULL,

    @Product_Points INT = NULL,

    @Product_Price NUMERIC(18, 2) = NULL,

    @Product_Keywords VARCHAR(500) = NULL,

    @Product_FeaturedHomePage BIT = NULL,

    @Product_DisplayHomePage BIT = NULL,

    @Product_Metatitle VARCHAR(100) = NULL,

    @Product_Metadescription VARCHAR(500) = NULL,

    @Product_Metakeywords VARCHAR(500) = NULL,

    @Product_Safeurlname VARCHAR(100) = NULL

    AS

    DECLARE @SQLStatement NVARCHAR(MAX)

    DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE @ParamValues NVARCHAR(MAX)

    BEGIN

    SET @SQLStatement = 'UPDATE Product ' + ' SET ' +

    ' Product_Name = @P_Name '

    SET @ParamDefinition = N' @P_Name varchar(100) '

    SET @ParamValues = @Product_Name

    IF @product_description IS NOT NULL

    BEGIN

    SET @SQLStatement =

    @SQLStatement + ' , Product_Description = @P_Description '

    SET @ParamDefinition =

    @ParamDefinition + N', @P_Description varchar(max) '

    SET @ParamValues = @ParamValues + ', ' + @product_description

    END

    IF @Product_Active IS NOT NULL

    BEGIN

    SET @SQLStatement = @SQLStatement + ' , Product_Active = @P_Active '

    SET @ParamDefinition = @ParamDefinition + N', @P_Active bit '

    SET @ParamValues = @ParamValues + ', @P_Active = @Product_Active '

    END

    IF @Product_Points IS NOT NULL

    BEGIN

    SET @SQLStatement = @SQLStatement + ' , Product_Points = @P_Points '

    SET @ParamDefinition = @ParamDefinition + N', @P_Points int '

    SET @ParamValues = @ParamValues + ', @P_Points = @Product_Points '

    END

    EXECUTE Sp_executesql

    @SQLStatement

    ,n@paramdefinition

    ,@ParamValues

    END

    If you have any further problems, could do with DDL please (see link in my sig)


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Also, I suspect you would be better having one SP for each module with all the SPs using static SQL.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply