Trying to avoid using dynamic SQL

[olichap]

Hello,

I'm trying to avoid using dynamic sql as much as possible. In the environment for which I must write some procedures the databases do not have standard names (will have multiple db's of the same structure but with slightly different names on one server).

Most of the procedures on the system are being passed the database name as a string, which is then being incorporated into a lot of dynamic sql. In my case I'm trying to address the issue of coding SQL to be more or less generic, for all db iterations.

I'm trying to do something like this and don't seem to be having too much luck:

declare @TargetDB varChar(7), @sqltxt varchar(100)

set @targetcdidb = 'dbname'
set @sqltxt = 'use ' + @targetcdidb
exec (@sqltxt)
select * from Tbl_In_dbname

My hope was the USE statement would function through EXEC the same way it does in the Query Analyzer, allowing me to change the db with a minimum of dynamic sql. This doesn't seem to change the DB though.

Anyone know how to make something like this work, or know a better way to accomplish this task?

Thanks,

O.

 

[vongrunt]

Actually, database gets changed - between "exec(" and ")". After that it reverts to current DB.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[mrdenny]

Yep, the exec statement gets run in it's own space. Nothing outside the exec command if effected by anything inside the exec statement.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)