Johnny Bones Johnny Bones - 6 months ago 11
SQL Question

Declaring a table variable with a field that is a variable

I have this:

declare @t1 table
(
[TableName] [nvarchar](100),
[UniqueName] [nvarchar](100),
[FieldName] [nvarchar](100),
[TransID_1] [nvarchar](2000),
[TransID_2] [nvarchar](2000)
)


I need something like this:

declare @t1 table
(
[TableName] [nvarchar](100),
[UniqueName] [nvarchar](100),
[FieldName] [nvarchar](100),
[TransID_'' + @ID1 + ''] [nvarchar](2000),
[TransID_'' + @ID2 + ''] [nvarchar](2000)
)


because the two TransID fields are going to have different numbers and the field names need to include those numbers. There's about 3,000 lines of code overall, and just trust me when I say I need to name them this way.

Anyway... Can this be done? If so, how?

Answer

You would need dynamic sql for this , something like this.....

Declare @ID1 varchar(10) = '20' ,@ID2 varchar(10) = '30'

Declare @Sql Nvarchar(max) 

set @Sql = N'declare @t1 table       
            (
                [TableName] [nvarchar](100), 
                [UniqueName] [nvarchar](100), 
                [FieldName] [nvarchar](100), 
                [TransID_' + @ID1 + '] [nvarchar](2000), 
                [TransID_' + @ID2 + '] [nvarchar](2000)
            )
            select * from @t1'

Exec sp_executesql @sql 
Comments