October 7, 2005 at 2:57 pm
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
)
October 7, 2005 at 3:25 pm
You can do it using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html
A good technique is not to generate tables from within a stored procedure, to manually create all well formed/normalized tables and not to do it dynamically.
IMHO. But cannot always be avoided.
October 8, 2005 at 11:56 am
Thank you so much for the link to that great article. My book has quite a bit on Exec(), but this is even more in depth. Re manually creating tables, we are porting a tool whose central feature is dynamic schema.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply