July 12, 2010 at 6:24 pm
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
July 13, 2010 at 3:14 am
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)
July 13, 2010 at 3:19 am
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