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))