Using variables for object names

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

General SQL Server Forums

New to SQL Server Programming

Using variables for object names

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-10-07 : 16:37:56

I am writing an sproc for creating a table, but I don't know how I can use parameter values as object names (for example the name of the table needs to be [@sFile1+'_'+@sLinkName+'_'+@sFile2']. It seems that I could concatenate my whole CREATE TABLE string into a single variable and use EXEC to run it, but I'd prefer to be able to do it in the context of the sProc (I read that EXEC always has the current user's permissions). What is a good technique?

CREATE PROCEDURE dbo.CreateLinkTable
@sFile1 	varchar(50),
@sFile2 	varchar(50),
@sLinkName 	varchar(50)

AS

CREATE TABLE [@sFile1+'_'+@sLinkName+'_'+@sFile2]
(
	[GID_ID]		uniqueidentifier	ROWGUIDCOL	NOT NULL,
	['GID_'+@sFile1]	uniqueidentifier			NOT NULL,
	['GID_'+@sFile2]	uniqueidentifier			NOT NULL,
	CONSTRAINT ['LNK_'+@sFile1+'_'+@sLinkname+'_'+@sFile2]
		FOREIGN KEY	(['GID_'+@sFile2])
			REFERENCES [@sFile2](GID_ID)
			ON UPDATE  NO ACTION
			ON DELETE  NO ACTION
			NOT FOR REPLICATION,
	CONSTRAINT ['LNK_'+@sFile2+'_'+@sLinkname+'_'+@sFile1]
		FOREIGN KEY	(['GID_'+@sFile1])
			REFERENCES [@sFile1](GID_ID)
			ON UPDATE  NO ACTION
			ON DELETE  NO ACTION
			NOT FOR REPLICATION
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 17:17:28

The short answer is that you can't use variables as object names without building a statement and exec-ing (dynamic sql)

It's usually not a good idea to create objects dynamically. Among other things you'll have to be concerned about concurrency the way you have it. If you want to expain the objective I bet you'll get some alternatives.

Be One with the Optimizer
TG

Marioi
Posting Yak Master

132 Posts

Posted - 2005-10-11 : 18:17:19

quote:
Originally posted by TG

The short answer is that you can't use variables as object names without building a statement and exec-ing (dynamic sql)

Thank you so much. I ended up using sp_executesql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources