Concatenated variable/key names

Question

Concatenated variable/key names

ChrisLuv

SSC Veteran

Points: 252
More actions
July 17, 2003 at 3:34 am

#82726

Is it possible, within T-sql, to name a variable or key and include the value of another variable within that name.
I imagine the code may look something like this:
set @myvar = 'new'
alter table mytable
add constraint fk_+@myvar
foreign key (col2)
references myothertable(col3)
Obviously this code will not run, but what I am trying to do is name the foreign key as fk_new, or fk_ followed by whatever &myvar happens to be.
Can anyone help me?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hello Chris,

quote:
Is it possible, within T-sql, to name a variable or key and include the value of another variable within that name.
I imagine the code may look something like this:
set @myvar = 'new'
alter table mytable
add constraint fk_+@myvar
foreign key (col2)
references myothertable(col3)

should be possible using dynamic sql.

You may take a look at sp_executesql in BOL

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ChrisLuv SSC Veteran Points: 252 More actions · Answer 2

quote:
should be possible using dynamic sql.
You may take a look at sp_executesql in BOL
Cheers,
Frank

Thanks for the help.

Ok so I've looked at dynamic sql and now I have a problem with the following code:



CREATE PROCEDURE myproc

@TableName varchar(50),

@ColName varchar(50),

AS

declare @deletestring nvarchar(500)

set @deletestring = N'delete from delTableName where @delColName is null'

execute sp_executesql 

          @deletestring,

          N'@delTableName varchar(50),@delColName varchar(50)',

          @tablename,@colname

When I run the procedure (it compiles OK) with the code:



EXECUTE CreateRelationship 'mytable,'DTPModel','mycol'

it returns the error message:

Must declare the variable '@delTableName'.

What am I doing wrong?

Edited by - chrisluv on 07/17/2003 04:34:30 AM

Edited by - chrisluv on 07/17/2003 04:35:23 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Chris,

there seems to be a little misunderstanding. Hopefully this code snippet will help.

This is the T-SQL script



use faiattach

go



DECLARE @table_name varchar(50)



SET @table_name = 'dv_vordrucke'



exec sp_createbinarytable @table_name

go

..and this is the sp



CREATE PROCEDURE sp_createbinarytable @tname nvarchar(50)  AS



DECLARE @stmt nvarchar(400)



SET @stmt = N'CREATE TABLE ' + @tname + 

' ( [id] [int] IDENTITY (1, 1) NOT NULL, 

 [image] NULL ,

[filename] [varchar] (50) NULL ,

[description] [varchar] (100) NULL ,

[sender] [varchar] (50) NULL) 

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC sp_executeSQL @stmt



SET  @stmt = N'CREATE  UNIQUE  CLUSTERED  INDEX [SK_ID] ON [dbo].['+@tname+']([id]) ON [PRIMARY]'

EXEC sp_executeSQL @stmt



SET @stmt = N'ALTER TABLE [dbo].['+@tname+'] WITH NOCHECK ADD 

CONSTRAINT [PK_'+@tname+'_1] PRIMARY KEY  NONCLUSTERED 

(

[id]

)  ON [PRIMARY] '

EXEC sp_executeSQL @stmt



GO

BTW, it's just fair to make you aware that the flexibility of dynamic SQL comes at a cost. Check this out http://www.algonet.se/~sommar/dynamic_sql.html

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]