0

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.

TekkGuy
  • 107
  • 2
  • 15

1 Answers1

0

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)
Jason Geiger
  • 1,912
  • 18
  • 32
  • Hi @Hogan if you reread the question you will see that the person is assigning SQL to a variable and then executing the SQL in that variable. I too was confused until I reread it a few times. – Jason Geiger Aug 17 '15 at 17:16
  • Yes, that is the definition of dynamic SQL. But according to the question, what is being shown on the screen is the result of executing print on that string not the code that generates the string. But I agree with you... that is probably where the problem is, we just can't see that code yet. I added a comment. – Hogan Aug 17 '15 at 17:18
  • The way that I read it is that he is verifying the output of the string by using print. I think we need some verification from the original question writer. – Jason Geiger Aug 17 '15 at 17:20
  • Look at edit above. Run that, does it still show the same error? – Jason Geiger Aug 17 '15 at 20:03