Declare Object Names? - 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 2000 Forums

Transact-SQL (2000)

Declare Object Names?

Author

Topic

em00guy
Starting Member

6 Posts

Posted - 2006-12-05 : 13:39:49

I'd like to declare an object name and call that instance when referencing an object. Here is an example of what I'm trying to do

DECLARE @Service_name varchar(50)

SET @Service_name = 'service1'

IF EXISTS (SELECT * FROM sys.services WHERE name = @Service_name)
DROP SERVICE @Service_name

This obviously does not work.
The purpose behind this is to have a single location at which one can change object names in a long script that creates and alters many objects. Any insights into how to accomplish this goal? Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 14:38:22

You'll have to use dynamic sql

Jay
to here knows when

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:39:46

You can't use variable for object names, so you basically have two choices

1. Just search and replace when you want to make changes in your scripts.

2. Use dynamic SQL - in which case your code becomes

DECLARE @Service_name nvarchar(50)

SET @Service_name = N'service1'

IF EXISTS (SELECT * FROM sys.services WHERE [name] = @Service_name)
BEGIN
    DECLARE @sql nvarchar(4000)
    SET @sql = N'DROP SERVICE [' + @Service_name + N']'
    EXEC sp_executesql @sql
END

em00guy
Starting Member

6 Posts

Posted - 2006-12-05 : 16:45:55

Thanks guys

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 10:44:13

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