Error Running SP [SOLVED]

[jojoba2011]

Hi , When i run this Stored Procedure I get this error : Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Pages'. AND my SP is :

declare @ModuleSettings NVarChar(max) = N'

0False5Top<Title>توليد کنندگان مواد اوليه / Suppliers of raw materials</Title>TrueTrueTrueFalse429TitleAbbrCreateDateContentKeywordCategoryFileIconwriterSource

'

declare @param nvarchar(max)= N'update [Management].[PageModules] set '
if 100 is not NULL
BEGIN
set @param = @param + '[AreaId]=''' + cast(100 as nvarchar(3)) + ''','
End
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + @ModuleSettings + ''','
END
if 0 is not NULL
BEGIN
set @param = @param + '[Election]=''' + cast(0 as nvarchar(3)) + ''','
End
if 'sdfg' is not NULL
BEGIN
set @param = @param + '[Title]=''' + 'sdfg' + ''','
End
if 418 is not NULL
BEGIN
set @param = @param + '[CategoryId]=''' + cast(418 as nvarchar(17)) + ''','
End
if NULL is not NULL
BEGIN
set @param = @param + '[DepartmentId]=''' + cast(NULL as nvarchar(9)) + ''','
End
if 38 is not NULL
BEGIN
set @param = @param + '[CultureId]=''' + cast(38 as nvarchar(5)) + ''','
End
if 15 is not NULL
BEGIN
set @param = @param + '[ModuleId]=''' + cast(15 as nvarchar(5)) + ''','
End
if 1 is not NULL
BEGIN
set @param = @param + '[PageId]=''' + cast(1 as nvarchar(9)) + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where Id='

[Lost User]

Change this;

N'

to this;

N'

..and do the same for the "ucl name".

Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]

[Lost User]

Of course you get an error for that. What kind of SQL is "

Why is common sense not common? Never argue with an idiot. They will drag you down to their level where they are an expert. Sometimes it takes a lot of work to be lazy Please stand in front of my pistol, smile and wait for the flash - JSOP 2012

[Richard Deeming]

Building a dynamic query in SQL, even inside a stored procedure, will not prevent SQL injection[^]. Your example could easily be rewritten as a simple UPDATE statement. (NB: Constant values like 100 are never null!) If any of the new values are passed in as parameters, then your query is open to SQL injection.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[jojoba2011]

Hi Please help .. Solutions are not usefull! but its SP and for example i fill info in that!

[jojoba2011]

Hi, I my Self solved the problem via this strategy Change the bellow code :

if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + @ModuleSettings + ''','
END

**With :**

if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39)) + ''','
End

conclusion : you should replace : @ModuleSettings with : replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))