CODEWITHSUNDEEP
sqlsql-server
Johnny Bones
Johnny Bones

Reputation: 8402

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?

Upvotes: 0

Views: 41

Answers (1)

M.Ali
M.Ali

Reputation: 69494

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

Upvotes: 2

Related Questions