CODEWITHSUNDEEP
t-sqlsql-server-2005stored-proceduresxquerydynamic-sql
Rafi
Rafi

Reputation: 57

Dynamic SQL with parameters, when parameter value has predefined operators inside

I have a situation in a T-SQL stored procedure, where in a dynamic SQL, the parameter/s, referencing other variables, whose value has single quotes and other predefined operators. The problem is T-SQL script fails, when such a condition exist.

Attached is a sample code, demonstrating such a situation. Any Idea how to solve such a case?

    DECLARE @TransVocObj  XML,@xmlfragment XML,@SQL NVARCHAR(MAX)
SELECT @TransVocObj  =  '<TransactionVoucherViewModel><TransactionRows></TransactionRows></TransactionVoucherViewModel>'
                DECLARE @Narration varchar(100)
                SET @Narration ='AABBCC''DD''EEFF'-- @Narration ='AABBCCDDEEFF'
                Select @xmlfragment=
                '<TransactionRow>'+'<Description>'+@Narration +'</Description>'+'<DebitAmount>'+CONVERT(VARCHAR(30),500.00)+'</DebitAmount>'+'</TransactionRow>'
                SET @SQL=N' SET @TransVocObj.modify(''insert '+ CONVERT(NVARCHAR(MAX),@xmlfragment)+'   into (/TransactionVoucherViewModel/TransactionRows)[1] '') '
                EXECUTE sp_executesql @SQL,N'@TransVocObj XML Output,@xmlfragment XML',@TransVocObj OUTPUT,@xmlfragment
SELECT T.Item.query('.//Description').value('.','VARCHAR(60)') FROM  @TransVocObj.nodes('//TransactionRows/TransactionRow') AS T(Item)

The database server is MS SQL SERVER 2005

Upvotes: 0

Views: 188

Answers (1)

GilM
GilM

Reputation: 3761

You can double-up your single-quote characters within @Narration using the REPLACE function. So, when you build @xmlfragment it can look like:

Select @xmlfragment= 
                '<TransactionRow>'+'<Description>'+REPLACE(@Narration,'''','''''')+'</Description>'+'<DebitAmount>'+CONVERT(VARCHAR(30),500.00)+'</DebitAmount>'+'</TransactionRow>'

Upvotes: 1

Related Questions