May 7, 2008 at 5:19 pm
Hi:
I have problems in concatenating a sql statement using EXECUTE and Update.
Here's my code:
execute("declare @lcdbname char(11), @lclanguage char(2), @lccurrdb char(11) " +
"select @lccurrdb = db_name() " +
"declare curcomp cursor for " +
"select rtrim(cdbname), clanguage from foreign" +
"open curcomp " +
"fetch next from curcomp into @lcdbname, @lclanguage " +
"while @@fetch_status <> -1 " +
"begin " +
"if @lclanguage = 'US' " +
"begin " +
" print( " +
" ' if exists(select * from master..sysdatabases where name = ''' + @lcdbname + ''')' + " +
" ' begin ' + " +
" ' use ' + @lcdbname + " +
" ' if exists(select * from syscolumns where id = object_id(''china'') and name = ''clang'')' + " +
" ' execute ( '' update ' + @lccurrdb + '..foreign set fld1 = a.clang, fld2 = ''TIN'' from ' + @lcdbname + '..china a where a.clang <>'''' and fld1 = '''' and fld2 = '''''') ' + " +
" ' use ' + @lccurrdb + " +
" ' end ') " +
" end " +
" fetch next from curcomp into @lcdbname, @lclanguage " +
"end " +
"close curcomp " +
"deallocate curcomp")
As you can see the code above I use PRINT function to view the result/debugg and here it is:
.....execute ( ' update machine..foreign set fld1 = a.clang, fld2 = 'TIN' from db1..china a where a.clang <>'' and fld1 = '' and fld2 = ''') use machine end
My question here is how am I going to put a double quote in the update statement area? or is it possible?
Reason here I'm asking because I'm getting an error message if you try to run the statement with single quote.
Hoping for your kind feedbacks.
Thanks,
Jan
May 7, 2008 at 5:54 pm
jan michael woo cerna (5/7/2008)
Hi:I have problems in concatenating a sql statement using EXECUTE and Update.
Here's my code:
execute("declare @lcdbname char(11), @lclanguage char(2), @lccurrdb char(11) " +"select @lccurrdb = db_name() " +
"declare curcomp cursor for " +
"select rtrim(cdbname), clanguage from foreign" +
"open curcomp " +
"fetch next from curcomp into @lcdbname, @lclanguage " +
"while @@fetch_status <> -1 " +
"begin " +
"if @lclanguage = 'US' " +
"begin " +
" print( " +
" ' if exists(select * from master..sysdatabases where name = ''' + @lcdbname + ''')' + " +
" ' begin ' + " +
" ' use ' + @lcdbname + " +
" ' if exists(select * from syscolumns where id = object_id(''china'') and name = ''clang'')' + " +
" ' execute ( '' update ' + @lccurrdb + '..foreign set fld1 = a.clang, fld2 = ''TIN'' from ' + @lcdbname + '..china a where a.clang <>'''' and fld1 = '''' and fld2 = '''''') ' + " +
" ' use ' + @lccurrdb + " +
" ' end ') " +
" end " +
" fetch next from curcomp into @lcdbname, @lclanguage " +
"end " +
"close curcomp " +
"deallocate curcomp")
As you can see the code above I use PRINT function to view the result/debugg and here it is:
.....execute ( ' update machine..foreign set fld1 = a.clang, fld2 = 'TIN' from db1..china a where a.clang <>'' and fld1 = '' and fld2 = ''') use machine endMy question here is how am I going to put a double quote in the update statement area? or is it possible?
Reason here I'm asking because I'm getting an error message if you try to run the statement with single quote.
Hoping for your kind feedbacks.
Thanks,
Jan
May I say this is an error prone and ill-performing way to do things. a cursor created from within dynamic sql, and then another dynamic sql call from the inner loop of the cursor? ouch.
If it were me, I'd probably write the script you want to execute as a .sql file and then run it from SQLCMD with substitution parameters.
Anyway, it appears the reason it's failing for you is you haven't escaped the single quotes enough. You probably need four single quotes instead of two single quotes in that inner call to EXECUTE().
---------------------------------------
elsasoft.org
May 7, 2008 at 5:58 pm
to be more clear, it should work when you have it printing this instead of what you have now (notice the extra single quotes around TIN and in other places):
execute ( ' update machine..foreign set fld1 = a.clang, fld2 = ''TIN'' from db1..china a where a.clang <>'''' and fld1 = '''' and fld2 = ''''')
---------------------------------------
elsasoft.org
May 8, 2008 at 9:58 am
Thanks! I got it...:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply