Use statement Without Exec() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use statement Without Exec()

Author

Topic

Jster
Starting Member

21 Posts

Posted - 2007-12-19 : 11:16:29

I'm curious if there is a way to create a use statement with a variable and not have to run the EXEC(@SQL). We are finding that the EXEC() is causing a slight performance issue. I've come across several threads stating I have to use the EXEC('USE '+@dbName+' SELECT * FROM TABLE'). But I want to run a stored procudure like this:


DECLARE @dbName nvarchar(5)
SET @dbName = 'test'
USE @dbName SELECT * FROM TABLE1

Any help is appreciated.

Thanks,
JOsh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-19 : 11:30:16

why not use the 3 part naming of objects ?

select * from test.dbo.table1

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 11:44:44

You can't.
I think your designed is flawed if yuo have to resort to this type of code.

http://www.datamodel.org/NormalizationRules.html

E 12°55'05.25"
N 56°04'39.16"

jordanam
Yak Posting Veteran

62 Posts

Posted - 2007-12-19 : 11:58:24

When you use EXEC(@SQL), you are dynamically running the statement. These statements, unlike completely static procedures/queries, are not treated the same by the database engine, and are not cached.

If the statement is used heavily, sometimes it will be placed in the cache, but it certainly is nothing to be depended upon.

Are you trying to run a sproc that looks at databases other than the one in which it resides, or are you just being overly explicit? Perhaps there is an easier solution to your problem?

Jster
Starting Member

21 Posts

Posted - 2007-12-19 : 14:09:32

We have about 125 databases that are exactly the same. The reason behind the many DBs is dictated by government regulations for our customers. We were thinking that if we had one stored procedure located on master we had one place to change the stored procedure, instead of going through individual dbs and updating them as well. That's why we are trying to pass the database name into the sproc.

jordanam
Yak Posting Veteran

62 Posts

Posted - 2007-12-19 : 14:25:51

If they are exactly the same, couldn't you just replicate the stored procs across each DB?

You could also write a script that actually creates the procedures on each database, by running through the sys.databases table.

The code below is not elegant, but you could write something like,



DECLARE @SQL nvarchar(max), @DBname nvarchar(1024), @Cnt int 
DECLARE @DBs table (ID int primary key identity, DB nvarchar(1024))


INSERT INTO @DBs (DB)
SELECT
  name
FROM sys.databases
WHERE name like 'whatever%'

SET @Cnt = 1

WHILE @Cnt <= (SELECT MAX(ID) FROM @DBs)
BEGIN
  SET @DBname = (SELECT DB FROM @DBs WHERE ID = @Cnt)
  SET @SQL = 'USE ' + @DBname + ' CREATE PROCEDURE your proc'
  PRINT @SQL
  SET @Cnt = @Cnt + 1 -- left that line out before edit... oops
END

You could then run the output from anywhere, and it would create the sproc on every single database.

I am sure there are other ways... the replication method could prove slick.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 02:30:01

All about Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources