Execute multiple update statement in dynamic query in SQL Server

Question

I am using SQL Server 2014. I want to update one column, then after this column use in second update statement in dynamically generated query.

My query looks like this:

declare @squery varchar(max)
set @squery =   'if('+CHAR(39)+@AppendType+CHAR(39)+' = ''Address'')
begin
    update tblchild set name=(REPLACE(LEFT(phone,2)+LEFT(a,1),' ',''))
    update a  set a.a=a.name,a.name = b.name,a.email= b.email,a.b = '1' from tblchild as a , tblmaster as b where a.phone = b.phone
    update a  set a.name = b.name,a.phone = b.phone,a.a=b.a,a.b = '1' from tblchild as a , tblmaster as b where a.email = b.email and (a.b IS NULL or a.b = '')
end'

print(@squery)  
exec (@squery)

select * from tblchild

but this is not working.

My table looks like this:

name    email          phone            a   b
----------------------------------------------
s       [email protected]          111            NULL 1
u       [email protected]        222            NULL 1
x       qww             333            NULL 1
ik      [email protected]     1234567890     NULL 1
kinjal  [email protected]     7894561230     NULL 1

score 1 · Accepted Answer · 2015-07-03 11:44:37Z

1

the single quotes inside a string must be "doubled". It should look like this:

declare @squery nvarchar(max)
declare @AppendType nvarchar(max) = N'Address'

set @squery =   N'if('+CHAR(39)+@AppendType+CHAR(39)+N' = ''Address'')
begin
    update tblchild set name=(REPLACE(LEFT(phone,2)+LEFT(ISNULL(a,''''),1),'' '',''''))
    update a  set a.a=a.name,a.name = b.name,a.email= b.email,a.b = ''1'' from tblchild as a , tblmaster as b where a.phone = b.phone
    update a  set a.name = b.name,a.phone = b.phone,a.a=b.a,a.b = ''1'' from tblchild as a , tblmaster as b where a.email = b.email and (a.b IS NULL or a.b = '''')
end'

print(@squery)  
execute sp_executesql @squery

select * from tblchild

edited Jul 3, 2015 at 11:44

answered Jul 3, 2015 at 8:48

user4622594

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

2 Comments

user4622594 Over a year ago

can you please provide sample data for tblchild and tblmaster? to which table belong the sample data in your question?

Kinjal Patel Over a year ago

this data is for tblChild

Kinjal Patel · Accepted Answer · 2015-07-03 09:41:37Z

0

I found the problem!!!

I create key from phone and a column and than update. But my column a is null so it's can't merge.

use

update tblchild set name=(REPLACE(LEFT(phone,2)+LEFT(ISNULL(a,''''),1),'' '',''''))

instead of

update tblchild set name=(REPLACE(LEFT(phone,2)+LEFT(a,1),' ',''))

answered Jul 3, 2015 at 9:41

Kinjal Patel

833 silver badges12 bronze badges

1 Comment

user4622594 Over a year ago

ok so there was a data-driven problem too - didn't see that but i edited my answer

Collectives™ on Stack Overflow

Execute multiple update statement in dynamic query in SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related