Dynamically Create Update SQL In Stored Procedure

Question

I try to create a stored procedure to update a table record whose sql statement is dynamically created. I wrote some codes but am stoped in to run this query dynamically, How can i run this query or is there a better solution for this problem.

How this SP work?=> I send the columns names,values and datatype of the record that need update to SP like below

<e columnName=''PaymentStatus'' value=''99'' type=''nvarchar''/>
<e columnName=''HotelProvider'' value=''GAT2'' type=''nvarchar''/>

Then travel the nodes and create an Update statement, but can't execute it :))

I am giving a part of SP to understand the question better.

    DECLARE @UpdateXml xml = '
<xml>
<e columnName=''PaymentStatus'' value=''99'' type=''nvarchar''/>
<e columnName=''HotelProvider'' value=''GAT2'' type=''nvarchar''/>
</xml>';


DROP TABLE ##UpdateFields
SELECT
t.c.value('@columnName', 'varchar(max)') AS ColumnName,
t.c.value('@value', 'varchar(max)') AS Value,
t.c.value('@property', 'varchar(max)') AS PropertyOf,
t.c.value('@type', 'varchar(max)') AS ColumnType
INTO ##UpdateFields
from @UpdateXml.nodes('/xml/e') as t(c)

DECLARE @SQL nvarchar(MAX) = 'UPDATE HotelBooking ';

DECLARE @SQLUpdatePart nvarchar(MAX);
SET @SQLUpdatePart = 'SET ';
SELECT @SQLUpdatePart= @SQLUpdatePart+ColumnName +'='+'@QP_'+ColumnName+',' FROM ##UpdateFields WHERE PropertyOf IS NULL;

DECLARE @SQLWherePart nvarchar(MAX);
SET @SQLWherePart = ' WHERE Id=2';

DECLARE @ParmDefinition nvarchar(MAX)='';
SELECT @ParmDefinition = @ParmDefinition+'@QP_'+ColumnName+' '+ColumnType+',' FROM ##UpdateFields;


SELECT @ParmDefinition
SELECT @SQL + @SQLUpdatePart + @SQLWHerePart;

Last two select statements results are:

@QP_PaymentStatus nvarchar,@QP_HotelProvider nvarchar,@QP_TransactionId uniqueidentifier,@QP_UpdatedDate datetime

and

UPDATE HotelBooking SET PaymentStatus=@QP_PaymentStatus,HotelProvider=@QP_HotelProvider,UpdatedDate=@QP_UpdatedDate,TransactionId=@QP_TransactionId WHERE Id=2

Now How can I give the @QP parameters to sp_executesql() method dynamically?

Nikola Markovinović · Accepted Answer · 2012-04-11 18:31:49Z

1

You can do it by wrapping sp_executesql call in another exec:

declare @updateStr nvarchar(1000)
-- @updateStr = N'select * from ATable where ID = @p1'
set @updateStr = N'N''select * from ATable where ID = @p1'''
declare @paramStr nvarchar(100)
-- @paramStr = N'@p1 int'
set @paramStr = N'N''@p1 int'''
declare @actualParameters nvarchar(100)
set @actualParameters = N'@p1 = 10'
-- Concatenate parts of query into a variable
declare @sql nvarchar(max)
set @sql = N'sp_executesql ' + @updateStr + ',' + @paramStr + ', ' +  @actualParameters
-- And voila!
exec (@sql)

answered Apr 11, 2012 at 18:31

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamically Create Update SQL In Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related