CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Coloplast
Coloplast

Reputation: 95

Update a field using stored procedure, where the value for the field contains multiple ' '

I have a stored procedure where I am trying to update a column with the content of @Value.

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [PowerApp].[UpdateTableRequestSingle]
    (@Solution nvarchar(100),
     @ColumnName nvarchar(100),
     @Value nvarchar(255),
     @Record nvarchar(100),
     @CreatedBy nvarchar(50),
     @ExecutionFlag nvarchar(1)) 
AS
BEGIN
    DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10) --Linebreak
    DECLARE @SQLStatement VARCHAR(MAX)

    SET @SQLStatement = 'UPDATE [Extract' + @Solution + '].[TableRequest]' + @CRLF + 
                        'SET [' + @ColumnName + '] = ' + '''' + @Value + '''' + @CRLF +
                        'WHERE TableName = ' + '''' + @Record + ''''

    DECLARE @SQLStatementHistoryLog VARCHAR(MAX)
    SET @SQLStatementHistoryLog = 'INSERT INTO [Extract' +  @Solution + '].[TableRequestHistoryLog] ([SQLStatement], [CreatedBy])' + @CRLF +
'VALUES(''' + REPLACE(@SQLStatement,'''','''''') + ''',''' + @CreatedBy + ''')'

    IF @ExecutionFlag = '0'
    BEGIN
        PRINT @SQLStatement
        PRINT @SQLStatementHistoryLog
    END

    IF @ExecutionFlag = '1'
    BEGIN
        BEGIN TRANSACTION;
            EXEC (@SQLStatement)
            EXEC (@SQLStatementHistoryLog)
        COMMIT TRANSACTION;
    END
END;

And I am executing it with this:

/*
[PowerApp].[UpdateTableRequestSingle] 
           @Solution = 'SF', 
           @ColumnName = 'TableDataRequestWhereCustomDescription', 
           @Value = 'CRM_CP_Owner_Company_Number__c IN ('190', '230', '440', '450', '480')',
           @Record = 'Account',
           @CreatedBy = 'DKRAH',
           @ExecutionFlag = 1
*/

If I pass

@Value = 'CRM_CP_Owner_Company_Number__c IN (190, 230, 440, 450, 480)'

it works but I want it to work with 

@Value='CRM_CP_Owner_Company_Number__c IN ( '190', '230', '440', '450', '480')'

Does anyone know how to edit the T-SQL to make it work.

Upvotes: 0

Views: 35

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You don't escape the single quotes in @Value and therefore end up with unescaped single quotes in you dynamic query.

You should use parameterization using sp_executesql. You should also use quotename() to get the correctly quoted identifiers.

...

SET @SQLStatement = 
'UPDATE ' + quotename('Extract' + @Solution) + '.[TableRequest]' + @CRLF + 
'SET ' + quotename(@ColumnName) + ' = @Value' + @CRLF +
'WHERE TableName = @Record';

...

EXECUTE sp_executesql @SQLStatement, '@Value nvarchar(255), @Record nvarchar(255)', @Value = @Value, @Record = @Record;

...

(And analog for @SQLStatementHistoryLog)

for @ColumnName and maybe even @Solution you should better use the type sysname which is the type for identifiers.

Upvotes: 1

Related Questions