Dynamic SQL 1

[vbguy805]

Hi,
I am running series of dynamic sql from stored procedure to update/insert rows in target database from source database. I got almost all statements to work but few I am not able to make it work. Problem is these statements are in middle of stored procedure. I cant take them out either...Here are those statments..If I run its result in query analizer it works fine.

Select @sSQL = N'alter table ' + @ps_DBName + '..member add hrank numeric(18) null, hunit numeric(18) null'
EXEC sp_executesql @sSQL


Select @sSQL = N'update ' + @ps_DBName + '..member set hrank =mr.hmy from ' + @ps_DBName + '..member m, dbsource..ranking r, ' + @ps_DBTargetName + '..temp_rank mr where m.MRank = r.rank_id and r.srankcd = mr.code and mr.MilServiceID = r.servbranchcode'
EXEC sp_executesql @sSQL

Above statement would translate dynamically to...
update dbsource..member set hrank =mr.hmy from dbsource..member m, dbsource..ranking r, dbTarget..temp_rank mr where m.MRank = r.rank_id and r.srankcd = mr.code and mr.MilServiceID = r.servbranchcode

Any work around to this? pleas help..can i use embeded vb script as work around?

 

[SQLSister]

First get rid of those bad joins. Never ever under any circumstances should you code joins like that. SQL 2005 won;t support them at aall and even in SQl 2000 they cn be problematic if you ever user outer joins.

Also if you are running an alter table first and they are inthe same batch whchi they are ina a stored proc then the second one doesn;t work becasue the alter table hasn;t committed yet. It is an extremely bad idea to run alter table statements inthe middle of dynamic stored procs anyway. You can introduce all kinds of erros with that that you haven;t tested for.

Questions about posting. See faq183-874

 

[Qik3Coder]

Now that you have been properly berated.

you may need:

[b]SET[/b] @sSQL = N'alter table ' + @ps_DBName + '..member add hrank numeric(18) null, hunit numeric(18) null'
EXEC sp_executesql @sSQL

try running the concat statement and then displayign that to verify that your statement is building correctly.


-The answer to your problem may not be the answer to your question.

 

[RiverGuy]

SQLSister,
SQL 2005 doesnt support joins in the where clause anymore?

 

[SQLDenis]

SQL server doesn't support Old style joins anymore (=* and *=) only ANSI joins

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[philhege]

I believe she means use

FROM TableA
JOIN TableB on TableB.key = TableA.key
etc.

instead of

FROM TableA, TableB, etc.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[vbguy805]

Qik3Coder,
Statement is building correctly..may be like SQLSister said since its not commited it is giving me an error. Not sure if that is the same case with update or insert statements..Any one treid using embeded vb scripting?

 

[Qik3Coder]

I know this may be "dumb", but have you tried a "GO GO" in between?

-The answer to your problem may not be the answer to your question.

 

[vbguy805]

no I didnt..I will try it..Im not sure either that is right solution.