CODEWITHSUNDEEP
t-sqlsql-server-2008-r2sql-manager
TekkGuy
TekkGuy

Reputation: 107

Dynamic SQL Error with REPLACE Statement

I am trying to create a script that builds a SQL statement dynamically and then executes it.

Here is the string that is built & stored in @strSQL (verified by using PRINT)

UPDATE DNN_RSM_Exams 
SET ScoringDates = REPLACE(ScoringDates, '/2015', '/2016') 
WHERE SchoolYear = 2015

It executes the variable as follows:

EXECUTE (@strSQL)

However I get the following error:

Conversion failed when converting the nvarchar value 'UPDATE DNN_RSM_Exams SET ScoringDates = REPLACE(ScoringDates, '/' to data type int.

It seems to terminate the EXECUTE when it hits the first forward-slash (/). I tried escaping it using a double slash (//), an open bracket ([), and a backslash (\). None if it worked.

Can anyone help me please?

UPDATE #01: 8/17/15

Sorry if I didn't give enough information initially...

ScoringDates is an NVARCHAR(MAX) field.

The Code that builds the SQL Statement is:

SET @strSQL = 'UPDATE ' + @strTableName +
              ' SET ScoringDates = REPLACE(ScoringDates, ''/' + LTRIM(RTRIM(STR(@intSchoolYear_CopyFrom + 1))) + ''', ''/' + LTRIM(RTRIM(STR(@intSchoolYear_CopyFrom + 2))) + ''')' +
              ' WHERE SchoolYear = ' + LTRIM(RTRIM(STR(@intSchoolYear_CopyTo)))

ScoringDates is a string based field that holds data in an INI-like formatted string. I want to change the year portion of ANY date found in the string, but I want to avoid accidental changes of any other numbers that may match. So I am specifically looking to replace "/YYYY" with a different "YYYY" value. The "/" preceding the year value is to ensure that what is being preplaced is the YEAR and not another numeric value within the string.

UPDATE #02: 8/18/15

So I am completely flabbergasted...after banging my head on this script for hours yesterday, I went home defeated. Come in today, start up my PC and run the script again so I can see the error message again...and it worked!

I've never come across this with SQL Management Studio, but it is possible that SQL Management Studio somehow lost it's marbles yesterday and needed a reboot? I thought the SQL was process by the server directly. Could it be that some is processed by the studio first before handing it off to the server and if the studio had "issues" then it would cause strange errors?

In any case, thank you so much guys for your input, I am sorry that it was a wheel spinner. It never occurred to me that a reboot would fix my issue, I just assumed my code was wrong.

Upvotes: 0

Views: 2952

Answers (1)

Jason Geiger
Jason Geiger

Reputation: 2129

You want to replace '/ with ''

So your set statement will be something like...

SET @strSQL = 'UPDATE DNN_RSM_Exams 
    SET ScoringDates = REPLACE(ScoringDates, ''2015'', ''2016'') 
    WHERE SchoolYear = 2015'

EDIT: How is your code different than this below? (I will edit this again and clean it up after. Code just won't fit into comments)

DECLARE @TableName TABLE (ScoringDates VARCHAR(100), SchoolYear INT)

DECLARE @strTableName VARCHAR(MAX)
DECLARE @intSchoolYear_CopyFrom VARCHAR(MAX)
DECLARE @intSchoolYear_CopyTo VARCHAR(MAX)

SET @strTableName = '@TableName'
SET @intSchoolYear_CopyFrom = '2009'
SET @intSchoolYear_CopyTo = '2010'

DECLARE @strSQL VARCHAR(MAX)

SET @strSQL = 'DECLARE @TableName TABLE (ScoringDates VARCHAR(100), SchoolYear INT); UPDATE ' + @strTableName +
              ' SET ScoringDates = REPLACE(ScoringDates, ''/' + LTRIM(RTRIM(STR(@intSchoolYear_CopyFrom + 1))) + ''', ''/' + LTRIM(RTRIM(STR(@intSchoolYear_CopyFrom + 2))) + ''')' +
              ' WHERE SchoolYear = ' + LTRIM(RTRIM(STR(@intSchoolYear_CopyTo)))

PRINT @strSQL

EXECUTE (@strSQL)

Upvotes: 0

Related Questions