Update string query throws error if executed from within stored procedure

Question

I have a dynamically compiled update query which really has a bunch of statements in it. Now the syntax seems correct as I am able to, in a new query window(SSMS) execute the same string with no errors returned and the changes are indeed made in the table. i.e

SELECT  @updatequerystring = 
' UPDATE  ##Details  
    SET  [FieldName] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( FieldName, Char(92),''___reversesolidusChar___'') 
                                                            , Char(91),''___leftsquarebracketChar___'') 
                                                        , Char(59),''___Semicolon___'') 
                                                    , Char(58),''___ColonChar___'') 
                                                , Char(47),''___SolidusChar___'') 
                                            , Char(46),''___fullstopChar___'') 
                                        , Char(44),''___CommaChar___'') 
                                    , Char(39),''___apostropheChar___'') 
                                , Char(34),''___DoubleQuotesChar___'') 
                            , Char(32),''___SpaceChar___'') )  
;  UPDATE  ##Details  
    SET  [Response] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( Response, Char(92),''___reversesolidusChar___'') 
                                                            , Char(91),''___leftsquarebracketChar___'') 
                                                        , Char(59),''___Semicolon___'') 
                                                    , Char(58),''___ColonChar___'') 
                                                , Char(47),''___SolidusChar___'') 
                                            , Char(46),''___fullstopChar___'') 
                                        , Char(44),''___CommaChar___'') 
                                    , Char(39),''___apostropheChar___'') 
                                , Char(34),''___DoubleQuotesChar___'') 
                            , Char(32),''___SpaceChar___'') )  ; '

EXEC sp_executesql @updatequerystring

However when I set the same string to execute, in exactly the same was above, only from within my query, the following error is thrown instead.

.Net SqlClient Data Provider: Msg 50000, Level 15, State 1, Procedure GenerateActivitiesQuestionResponseResultSet, Line 251 Incorrect syntax near ' UPDATE ##Details SET [FieldName] = (Replace ( Replace ( Replace ( Replace ( Replace ( Replace ( Replace ( Replac'.

Same error is thrown when I try to execute just one of the two update statements in the above. I would appreciate any help in figuring this out. Thank you in advance.

What datatype is your variable? It appears that your string is likely longer than your variable. A bit off topic perhaps but why are you using global temp tables? — Sean Lange, Mar 06 '15 at 21:55
OK but why the global temp table? Do you understand the concurrency issues? — Sean Lange, Mar 06 '15 at 22:10
FYI, you may want to look into a user defined function to hold all of that replace logic. — jlee-tessik, Mar 11 '15 at 05:31
The problem is not clear. Post the exact code that produces the error. It looks like you have a stored procedure called `GenerateActivitiesQuestionResponseResultSet`. Post the exact definition of this procedure and how exactly you are calling it. Simplify it as much as you can at first. It is very likely that you'll figure out what the problem was while you'll be simplifying the code. — Vladimir Baranov, Mar 11 '15 at 11:02
You have included in the question the T-SQL code that works in SSMS. Fine. Now include in the question your .NET code that does **not** work. What do you mean by "when I set the same string to execute from within my query" ? What *my* query? Provide exact text of it. How to execute? Provide actual code. At the moment it is completely unclear. Announcing the bounty doesn't help if you don't provide this information. — Vladimir Baranov, Mar 12 '15 at 22:48

Jen R · Answer 1 · 2015-03-11T02:41:16.870

I reformatted your query to make it more readable...

select @updatequerystring = ' 
UPDATE  ##Details  SET  [FieldName] =  (
Replace ( 
 Replace (
  Replace ( 
   Replace ( 
    Replace ( 
     Replace ( 
      Replace ( 
       Replace ( 
        Replace ( 
         Replace ( FieldName, Char(92),''___reversesolidusChar___'') , 
        Char(91),''___leftsquarebracketChar___'') , 
       Char(59),''___Semicolon___'') , 
      Char(58),''___ColonChar___'') , 
     Char(47),''___SolidusChar___'') , 
    Char(46),''___fullstopChar___'') ,
   Char(44),''___CommaChar___'') , 
  Char(39),''___apostropheChar___'') ,
 Char(34),''___DoubleQuotesChar___'') , 
Char(32),''___SpaceChar___'') )  ;  
UPDATE  ##Details  SET  [Response] =  (
Replace ( 
 Replace ( 
  Replace ( 
   Replace ( 
    Replace ( 
     Replace ( 
      Replace ( 
       Replace ( 
        Replace ( 
         Replace ( Response, Char(92),''___reversesolidusChar___'') , 
        Char(91),''___leftsquarebracketChar___'') , 
       Char(59),''___Semicolon___'') , 
      Char(58),''___ColonChar___'') , 
     Char(47),''___SolidusChar___'') , 
    Char(46),''___fullstopChar___'') , 
   Char(44),''___CommaChar___'') , 
  Char(39),''___apostropheChar___'') , 
 Char(34),''___DoubleQuotesChar___'') , 
Char(32),''___SpaceChar___'') )  ; '

And noticed that you are trying to execute 2 update statements - try it with only one.

Try this, which changes your two UPDATES to one (which is all you need since you are only updating one table):

select @updatequerystring = ' 
UPDATE  ##Details  SET  [FieldName] =  (
Replace ( 
 Replace (
  Replace ( 
   Replace ( 
    Replace ( 
     Replace ( 
      Replace ( 
       Replace ( 
        Replace ( 
         Replace ( FieldName, Char(92),''___reversesolidusChar___'') , 
        Char(91),''___leftsquarebracketChar___'') , 
       Char(59),''___Semicolon___'') , 
      Char(58),''___ColonChar___'') , 
     Char(47),''___SolidusChar___'') , 
    Char(46),''___fullstopChar___'') ,
   Char(44),''___CommaChar___'') , 
  Char(39),''___apostropheChar___'') ,
 Char(34),''___DoubleQuotesChar___'') , 
Char(32),''___SpaceChar___'') ),
[Response] =  (
Replace ( 
 Replace ( 
  Replace ( 
   Replace ( 
    Replace ( 
     Replace ( 
      Replace ( 
       Replace ( 
        Replace ( 
         Replace ( Response, Char(92),''___reversesolidusChar___'') , 
        Char(91),''___leftsquarebracketChar___'') , 
       Char(59),''___Semicolon___'') , 
      Char(58),''___ColonChar___'') , 
     Char(47),''___SolidusChar___'') , 
    Char(46),''___fullstopChar___'') , 
   Char(44),''___CommaChar___'') , 
  Char(39),''___apostropheChar___'') , 
 Char(34),''___DoubleQuotesChar___'') , 
Char(32),''___SpaceChar___'') )  ; '

I just tried it and the same issue persists. I even tried adding TRY-CATCH in there and I am able to execute the query if I only select to do so in a new query window but when I run the same through my stored proc, I get the same exact error from that line in the code. — user272671, Mar 06 '15 at 22:27
I updated this answer with the actual code to use to eliminate the second UPDATE statement — Jen R, Mar 11 '15 at 18:29

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

From within SSMS you can run multiple queries, but from outside you can't. There are several results on SO when you search for 'update multiple tables in 1 statement' and I bet (not having access to SSMS at the moment) this answer might just work for you: How to update two tables in one statement in SQL Server 2005?

edited May 23 '17 at 12:06

Community

1
1

answered Mar 06 '15 at 22:12

Jeroen Flamman

965
6
10

Are you suggesting that even when I run just the one update statement, SQL Server thinks I am running an Update on multiple tables in the same query? – user272671 Mar 06 '15 at 22:32
But the OP is only updating a single table. Also, not sure what you mean that you can't run multiple queries from outside SSMS. Sure you can. – Sean Lange Mar 06 '15 at 22:32
It is likely getting confused since the first statement wasn't terminated with a semicolon but other than that you absolutely can run multiple statements. – Sean Lange Mar 06 '15 at 22:33
The first statement is terminated with a colon there. – user272671 Mar 06 '15 at 22:34
ahh so it is...hard to see in that big ole string. :) – Sean Lange Mar 06 '15 at 22:35
Well, you can rule it out by following @JenR's suggestion - try it with only one statement (e.g. `UPDATE ##Details SET [FieldName]` without the 2nd `UPDATE ##Details SET [Response]` query) and see if that executes without problems. Or combine them into one statement `UPDATE ##Details SET [FieldName] = Replace(...), [Response] = Replace(...)` – Jeroen Flamman Mar 06 '15 at 22:37
That didn't resolve the issue. I still got the same error even with just the one update statement – user272671 Mar 06 '15 at 22:44
If you run just the one UPDATE, what error does it give ? – deroby Mar 13 '15 at 18:35

score 0 · Answer 3 · answered Mar 11 '15 at 16:27

Now this might seem a bit radical but try running this:

declare @updatequerystring nvarchar(4000) = ''
print '1: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + 'UPDATE  ##Details  '
set @updatequerystring = @updatequerystring + 'SET  [FieldName] =  (Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
print '2: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
print '3: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( FieldName, Char(92),''___reversesolidusChar___'') '
set @updatequerystring = @updatequerystring + ', Char(91),''___leftsquarebracketChar___'') '
set @updatequerystring = @updatequerystring + ', Char(59),''___Semicolon___'') '
set @updatequerystring = @updatequerystring + ', Char(58),''___ColonChar___'') '
print '4: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + ', Char(47),''___SolidusChar___'') '
set @updatequerystring = @updatequerystring + ', Char(46),''___fullstopChar___'') '
set @updatequerystring = @updatequerystring + ', Char(44),''___CommaChar___'') '
set @updatequerystring = @updatequerystring + ', Char(39),''___apostropheChar___'') '
set @updatequerystring = @updatequerystring + ', Char(34),''___DoubleQuotesChar___'') '
print '5: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + ', Char(32),''___SpaceChar___'') );  '
set @updatequerystring = @updatequerystring + 'UPDATE  ##Details  '
set @updatequerystring = @updatequerystring + 'SET  [Response] =  (Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
print '6: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
print '7: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Replace '
set @updatequerystring = @updatequerystring + '( Response, Char(92),''___reversesolidusChar___'') '
set @updatequerystring = @updatequerystring + ', Char(91),''___leftsquarebracketChar___'') '
set @updatequerystring = @updatequerystring + ', Char(59),''___Semicolon___'') '
print '8: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + ', Char(58),''___ColonChar___'') '
set @updatequerystring = @updatequerystring + ', Char(47),''___SolidusChar___'') '
set @updatequerystring = @updatequerystring + ', Char(46),''___fullstopChar___'') '
set @updatequerystring = @updatequerystring + ', Char(44),''___CommaChar___'') '
set @updatequerystring = @updatequerystring + ', Char(39),''___apostropheChar___'') '
print '9: ' + cast(len(@updatequerystring) as varchar(20))
set @updatequerystring = @updatequerystring + ', Char(34),''___DoubleQuotesChar___'') '
set @updatequerystring = @updatequerystring + ', Char(32),''___SpaceChar___'') );'
print 'final: ' + cast(len(@updatequerystring) as varchar(20))
exec(@updatequerystring)

I had an issue last week where when setting the value of a variable to some dynamic SQL it was losing data even although I was using nvarchar(max) as the variable I was adding the data to. It ended up being that SQL was unable to set a variable value to more than x characters at the same time, so I had to break it down to chunks, for yours I've made it line by line as it's much shorter. Also I've added some LEN lines in so you can check your dynamic SQL is actually reaching the correct length.

This all works along with your original for me in SQL 2012 Enterprise but thought this diagnosis will help you check that the variable is in fact being set correctly... after all this message I've just thought, why don't you just print it from your procedure before executing it?

Here's my output form the example above:

1: 0
2: 77
3: 127
4: 289
5: 456
6: 557
7: 607
8: 748
9: 908
final: 978

Regards

Liam

@LiamWheldonn, I suspect the truncation issue you mentioned in setting the nvarchar(MAX) variable is because the source expressions were all not MAX data types or long literals. The result is implicitly converted to nvarchar(n) unless at least one of the concatenated expressions is a MAX type. — Dan Guzman, Mar 14 '15 at 04:29

Brennan Pope · Answer 4 · 2015-03-11T20:32:24.560

The one thing we can tell is that the variable @updatequerystring is being truncated. From the information provided, this should not be happening. Thus we will need more information to continue. For further troubleshooting, I would recommend the following:

Declare @updatequerystring as VARCHAR(MAX) or NVARCHAR(MAX)
Use EXEC(@updatequerystring) instead of EXEC sp_executesql @updatequerystring
Determine the length of the variable just before the EXEC(@updatequerystring) statement.
Determine if this variable is being acted upon in any other way than shown in the question.

Also, I would avoid using global temp tables at all cost.

EDIT: The error you are pulling is not from within SSMS. If you do get an error in SSMS, could you please include it?

score 0 · Answer 5 · answered Mar 11 '15 at 19:53

Be aware that concatenations can cut the end of long varchar data types, even when the variables are declared as varchar(max). This is because a literal string is considered varchar(8000) or nvarchar(4000) by default. This datatype will not change if concatenation results in a larger character value. You need to explicitly convert to varchar(max) or nvarchar(max) to get the desired result. See the following, a bit awkward, example:

DECLARE @updatequerystring  VARCHAR(MAX)
      , @updatequerystring2 VARCHAR(MAX)
SELECT @updatequerystring  = 'SELECT Replace ( ' + REPLICATE( 'A', 8123 ) + ', ' + REPLICATE( 'B', 8123 ) + ''''
     , @updatequerystring2 = CONVERT( VARCHAR(MAX),
                             'SELECT Replace ( ' ) + REPLICATE( 'A', 8123 ) + ', ' + REPLICATE( 'B', 8123 ) + ''''

SELECT LEN( @updatequerystring )  AS StringLength   -- 8000
     , LEN( @updatequerystring2 ) AS StringLength2  -- 16020

score 0 · Answer 6 · answered Mar 12 '15 at 02:18

Starting a process of elimination :

1) verify that the string being executed in SSMS and from "within your query" is identical. To do this, you could create a table like this :

CREATE TABLE dbo.TempQueryString (ID INT PRIMARY KEY, QueryString NVARCHAR(MAX))

From within your SSMS code, log your @updatequerystring variable. e.g.

INSERT dbo.TempQueryString(ID, QueryString) VALUES (1, @updatequerystring)

From "within your query" code, log your variable again. e.g.

INSERT dbo.TempQueryString(ID, QueryString) VALUES (2, @updatequerystring)

To verify that the query strings are the same, run something like:

DECLARE @Q1 NVARCHAR(MAX) = (select QueryString from dbo.TempQueryString WHERE ID = 1)
DECLARE @Q2 NVARCHAR(MAX) = (select QueryString from dbo.TempQueryString WHERE ID = 2)

IF @Q1 = @Q2 
    print 'Q1 equal to Q2'
else 
    print 'Q1 not equal to Q2'


IF CAST(@Q1 as varbinary(max)) = CAST(@Q2 as varbinary(max))
    print 'Q1 equal to Q2 (binary comparison)'
else 
    print 'Q1 not equal to Q2 (binary comparison)'

Let us know how this goes.

Tanner · Answer 7 · 2015-03-12T10:48:56.663

Your code works just fine, as you can see if you run this below example, where I've created a global temp table with a single row of data:

DECLARE @updatequerystring NVARCHAR(MAX),
        @fieldName VARCHAR(50) = 'field-[];."',
        @response VARCHAR(50) = 'response-[];."'

SELECT  @fieldName AS FieldName ,
        @response AS Response
INTO    ##Details

SELECT  @updatequerystring = '
UPDATE  ##Details
SET     [FieldName] = ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FieldName,
                                                    CHAR(92),
                                                    ''___reversesolidusChar___''),
                                                    CHAR(91),
                                                    ''___leftsquarebracketChar___''),
                                                    CHAR(59),
                                                    ''___Semicolon___''),
                                                    CHAR(58),
                                                    ''___ColonChar___''),
                                                    CHAR(47),
                                                    ''___SolidusChar___''),
                                                    CHAR(46),
                                                    ''___fullstopChar___''),
                                            CHAR(44),
                                            ''___CommaChar___''),
                                    CHAR(39),
                                    ''___apostropheChar___''),
                            CHAR(34), ''___DoubleQuotesChar___''),
                    CHAR(32), ''___SpaceChar___'') );
UPDATE  ##Details
SET     [Response] = ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Response,
                                                    CHAR(92),
                                                    ''___reversesolidusChar___''),
                                                    CHAR(91),
                                                    ''___leftsquarebracketChar___''),
                                                    CHAR(59),
                                                    ''___Semicolon___''),
                                                    CHAR(58),
                                                    ''___ColonChar___''),
                                                    CHAR(47),
                                                    ''___SolidusChar___''),
                                                    CHAR(46),
                                                    ''___fullstopChar___''),
                                            CHAR(44),
                                            ''___CommaChar___''),
                                    CHAR(39),
                                    ''___apostropheChar___''),
                            CHAR(34), ''___DoubleQuotesChar___''),
                    CHAR(32), ''___SpaceChar___'') ); '

EXEC sp_executesql @updatequerystring

SELECT * FROM ##Details

DROP TABLE ##Details

This leads me to believe the issue is going to be in your global temp table and it's schema. So your code is replacing a bunch of special characters with strings that are much longer than the values being replaced, so I think the underlying error (which seems to be hidden by Msg 50000) is going to be trying to update fields with values that don't fit in the destination.

If you run the below from within your code, you should get the schema for the global temp table:

exec tempdb..sp_help '##Details'

Check the type and length of the fields you are updating, my guess is that the length will be fixed (as in not set to max) on the destination fields. If so, increase to max and re-test.

If you try to update to a value that doesn't fit you get a `binary or string trunctation error` (or something like that); you don't get a 'syntax error near..` — deroby, Mar 13 '15 at 18:29

score 0 · Answer 8 · answered Mar 13 '15 at 13:59

A suggestion, not really an answer (this doesn't fit in a comment), temporarily modify your code as follows:

PRINT '-----------------------------'
PRINT @updatequerystring
PRINT '-----------------------------'

EXEC sp_executesql @updatequerystring

to see precisely what query SQL is attempting to run.

score 0 · Answer 9 · answered Mar 13 '15 at 18:41

Reading through your question and the comments it seems to me that the problem is not a SQL issue, but a .NET issue.

Could you

copy-paste the code that creates the connection & command and executes it ? (I'm not really interested in how the big update-string is created, just how you pass it to the ADO.Net objects)
copy-paste the exact string passed to the server (without the extra 'fluff' to test it in SSMS, really just what is passed to e.g. .CommandText)

Remarks: as already mentioned elsewhere, please try to do

UPDATE myTable
   SET field1 = some_formula(field1),
       field2 = some_formula(field2)

intead of splitting it in 2 operations. That way the server needs to go over the data only once making it (literately) twice as fast.

score 0 · Answer 10 · answered Mar 13 '15 at 19:58

Everyone seems to run your code just fine so I'm thinking the issue may not be in your dynamic statement, but might be a permissions problem or something else behind the scenes. I converted your char(num) to their values. Try it out.

DECLARE @updatequerystring NVARCHAR(4000) = 
N'UPDATE  ##Details  
    SET  [FieldName] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( FieldName,''\'',''___reversesolidusChar___'') 
                                                            , ''['',''___leftsquarebracketChar___'') 
                                                        , '';'',''___Semicolon___'') 
                                                    , '':'',''___ColonChar___'') 
                                                , ''/'',''___SolidusChar___'') 
                                            , ''.'',''___fullstopChar___'') 
                                        , '','',''___CommaChar___'') 
                                    , '''''''',''___apostropheChar___'') 
                                , ''"'',''___DoubleQuotesChar___'') 
                            , '' '',''___SpaceChar___'') )  
; UPDATE  ##Details  
    SET  [Response] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( Response,''\'',''___reversesolidusChar___'') 
                                                            , ''['',''___leftsquarebracketChar___'') 
                                                        , '';'',''___Semicolon___'') 
                                                    , '':'',''___ColonChar___'') 
                                                , ''/'',''___SolidusChar___'') 
                                            , ''.'',''___fullstopChar___'') 
                                        , '','',''___CommaChar___'') 
                                    , '''''''',''___apostropheChar___'') 
                                , ''"'',''___DoubleQuotesChar___'') 
                            , '' '',''___SpaceChar___'') )'
EXEC (@updatequerystring);
--EXEC sp_executesql @updatequerystring;

score 0 · Answer 11 · answered Mar 14 '15 at 16:02

This is a very ugly scenario that should take advantage of abstraction through a function. I've included a hard-coded replacement example below, I've also included a partial alternative just below that.

UPDATE ##Details
SET [FieldName] = dbo.fnCleanInput([FieldName]),
    [Response] = dbo.fnCleanInput([Response]);

Function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fnCleanInput
(
    @Input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF (@Input IS NULL)
        RETURN NULL;

    -- NOTE: This could be stored in a lookup table and done with a CURSOR.  Advantage being, you'd just have to update a lookup table instead of updating your code.
    SET @Input = REPLACE(@Input, Char(92), ___reversesolidusChar___);
    SET @Input = REPLACE(@Input, Char(91), ___leftsquarebracketChar___);
    SET @Input = REPLACE(@Input, Char(59), ___Semicolon___);
    SET @Input = REPLACE(@Input, Char(58), ___ColonChar___);
    SET @Input = REPLACE(@Input, Char(47), ___SolidusChar___);
    SET @Input = REPLACE(@Input, Char(46), ___fullstopChar___);
    SET @Input = REPLACE(@Input, Char(44), ___CommaChar___);
    SET @Input = REPLACE(@Input, Char(39), ___apostropheChar___);
    SET @Input = REPLACE(@Input, Char(34), ___DoubleQuotesChar___);
    SET @Input = REPLACE(@Input, Char(32), ___SpaceChar___);

    RETURN @Input;
END
GO

Alternatively, you could send your replacements in a comma delimited string and have the function parse the input/output characters, then loop over each pair producing your desired output. This should remove your need for dynamic SQL all together.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fnCleanInput2
(
    @Input VARCHAR(MAX),
    @Replacements VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF (@Input IS NULL)
        RETURN NULL;

    -- Note: I've left the function dbo.fnSplitReplacements out because this is theoretical and is dependant upon the delimitted format of @Replacements.
    DECLARE @ToReplace VARCHAR(50),
            @ReplaceWith VARCHAR(50);
    DECLARE Replacements CURSOR FORWARD_ONLY FOR
        SELECT
            ToReplace,
            ReplaceWith
        FROM dbo.fnSplitReplacements(@Replacements);

    OPEN Replacements;
    FETCH Replacements INTO @ToReplace, @ReplaceWith;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Input = REPLACE(@Input, @ToReplace, @ReplaceWith);

        FETCH Replacements INTO @ToReplace, @ReplaceWith;
    END

    CLOSE Replacements;
    DEALLOCATE Replacements;

    RETURN @Input;
END
GO

score 0 · Answer 12 · answered Mar 16 '15 at 11:33

I tried this and it worked perfectly fine for me.

declare @updatequerystring as nvarchar(max)

create table ##Details
(
    FieldName nvarchar(50) , 
    Response nvarchar(50) , 

)

insert into ##Details values('aaaaa','bbbbb')

SELECT  @updatequerystring = 
' UPDATE  ##Details  
    SET  [FieldName] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( FieldName, Char(92),''___reversesolidusChar___'') 
                                                            , Char(91),''___leftsquarebracketChar___'') 
                                                        , Char(59),''___Semicolon___'') 
                                                    , Char(58),''___ColonChar___'') 
                                                , Char(47),''___SolidusChar___'') 
                                            , Char(46),''___fullstopChar___'') 
                                        , Char(44),''___CommaChar___'') 
                                    , Char(39),''___apostropheChar___'') 
                                , Char(34),''___DoubleQuotesChar___'') 
                            , Char(32),''___SpaceChar___'') )  
;  UPDATE  ##Details  
    SET  [Response] =  (Replace 
                            ( Replace 
                                ( Replace 
                                    ( Replace 
                                        ( Replace 
                                            ( Replace 
                                                ( Replace 
                                                    ( Replace 
                                                        ( Replace 
                                                            ( Replace 
                                                                ( Response, Char(92),''___reversesolidusChar___'') 
                                                            , Char(91),''___leftsquarebracketChar___'') 
                                                        , Char(59),''___Semicolon___'') 
                                                    , Char(58),''___ColonChar___'') 
                                                , Char(47),''___SolidusChar___'') 
                                            , Char(46),''___fullstopChar___'') 
                                        , Char(44),''___CommaChar___'') 
                                    , Char(39),''___apostropheChar___'') 
                                , Char(34),''___DoubleQuotesChar___'') 
                            , Char(32),''___SpaceChar___'') )  ; '

EXEC sp_executesql @updatequerystring

select * from ##Details

Drop table ##Details

score 0 · Answer 13 · answered Mar 16 '15 at 16:55

One way is to abandon the nested replace statements and perform the token replacement like this:

declare @FieldName varchar(max);
set @FieldName = '
First \ 
Second [ 
Third ; 
Fourth : 
';

declare @Map table (ReplaceThis varchar(100) primary key, WithThis varchar(100));
insert into @Map
    select '\', '___reversesolidusChar___' union all
    select '[', '___leftsquarebracketChar___' union all
    select ';', '___Semicolon___' union all
    select ':', '___ColonChar___'; -- and so on...


select  @FieldName = replace(@FieldName, ReplaceThis, WithThis)
from    @Map

select @FieldName;

Wrap that in a function and call from your outer update statement.

score 0 · Answer 14 · answered Mar 17 '15 at 13:33

0

everything looks fine... Possible reason for the error could be the size (try giving max for the size) of the @updatequerystring variable.

Please let me know the size of the @updatequerystring, I believe this is a varchar.

answered Mar 17 '15 at 13:33

Vinod Kumar

981
1
6
14

Update string query throws error if executed from within stored procedure

14 Answers14