sp_executesql / dynamic sql help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_executesql / dynamic sql help

Author

Topic

jason.y
Starting Member

1 Post

Posted - 2010-07-12 : 20:28:13

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-12 : 21:40:07

why do you have the N there before the @ParamDefinition ? Remove it

EXECUTE sp_executesql @SQLStatement, @ParamDefinition, @ParamValues

Also better read this The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 02:52:20

@ParamValues needs to be a list of values (see below for alternative)

There is no requirement to only define parameters that are actually used, so you could do:



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

IF @Product_Description is not null
BEGIN
SET @SQLStatement = @SQLStatement + ' , Product_Description = @P_Description '
END


IF @Product_Active is not null
BEGIN
SET @SQLStatement = @SQLStatement + ' , Product_Active = @P_Active '
END


IF @Product_Points is not null
BEGIN
SET @SQLStatement = @SQLStatement + ' , Product_Points = @P_Points '
END

EXECUTE sp_executesql @SQLStatement, 
	N' @P_Name varchar(100), @P_Description varchar(max), @P_Active bit@P_Points int',
	@P_Name = @Product_Name, 
	@P_Description = @Product_Description,
	@P_Active = @Product_Active,
	@P_Points = @Product_Points

If you want / need to do it the way you had it you will have to do the WHOLE sp_ExecuteSQL statement as dynamic SQL too - something like:

EXECUTE (N'sp_executesql ' + @SQLStatement + N', ' + @ParamDefinition + N', ' + @ParamValues)

However, if you want to avoid updating where certain values are NULL you could just do:


UPDATE Product
SET	Product_Name = @P_Name
	, Product_Description = COALESCE(@Product_Description, Product_Description)
	, Product_Active = COALESCE(@P_Active, Product_Active)
	, Product_Points = COALESCE(@P_Points, Product_Points)

and just in case you don't have it in hand! you need a WHERE clause otherwise this will update every row in the table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources