Dynamically declare the variables for a table (to use with insert/update)

twaligora — Fri, 25 Jul 2008 22:53:53 GMT

This will only work if your column names have no Spaces for example column PoNum is Ok, Po Num wont work

The update will need some tweaking

Declare @TableName varchar(100)

set @TableName = 'FT_ALLOWANCES_TYPE' --Pass your table name inhere

declare @TempVariables table (DecVars varchar(100))

insert into @TempVariables

SELECT

CASE WHEN DATA_TYPE = 'varchar' THEN

'@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH) + ')'

ELSE

CASE WHEN DATA_TYPE = 'numeric' or DATA_TYPE = 'decimal'

THEN

'@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CONVERT(VARCHAR,NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR, NUMERIC_SCALE) + ') '

ELSE

'@' + COLUMN_NAME + ' ' + DATA_TYPE

END

FROM

INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE

TABLE_NAME = @TableName

declare @ReturnStr varchar(100), @MyVariables varchar(5000)

DECLARE MyVariableCursor CURSOR FOR

select * from @TempVariables

Open MyVariableCursor

fetch next from MyVariableCursor into @ReturnStr

while @@FETCH_STATUS = 0

begin

if @MyVariables is null

begin

set @MyVariables = @ReturnStr

end

else

begin

set @MyVariables = @MyVariables + ', ' + @ReturnStr

end

fetch next from MyVariableCursor into @ReturnStr

end

Close MyVariableCursor

Deallocate MyVariableCursor

select @MyVariables as MyParameters

-------------------------------------------NOW GET THE INSERT

declare @TempInsert table ([ID] [int] IDENTITY(1,1) NOT NULL, DecInst varchar(100))

insert into @TempInsert

SELECT

COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE

TABLE_NAME = @TableName

declare @MyInsert varchar(5000)

DECLARE MyInsertCursor CURSOR FOR

select DecInst from @TempInsert

Open MyInsertCursor

fetch next from MyInsertCursor into @ReturnStr

while @@FETCH_STATUS = 0

begin

if @MyInsert is null

begin

set @MyInsert = 'Insert into ' + @TableName + ' ('+ @ReturnStr

end

else

begin

set @MyInsert = @MyInsert + ', ' + @ReturnStr

end

fetch next from MyInsertCursor into @ReturnStr

end

Close MyInsertCursor

Deallocate MyInsertCursor

set @MyInsert = @MyInsert + ')'

select @MyInsert as MyInsert

-------------------------------------------NOW GET THE VALUES

declare @TempValues table ([ID] [int] IDENTITY(1,1) NOT NULL, DecVals varchar(100))

insert into @TempValues

SELECT

'@' + COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE

TABLE_NAME = @TableName

declare @MyValues varchar(5000)

DECLARE MyValueCursor CURSOR FOR

select DecVals from @TempValues

Open MyValueCursor

fetch next from MyValueCursor into @ReturnStr

while @@FETCH_STATUS = 0

begin

if @MyValues is null

begin

set @MyValues = 'Values (' + @ReturnStr

end

else

begin

set @MyValues = @MyValues + ', ' + @ReturnStr

end

fetch next from MyValueCursor into @ReturnStr

end

Close MyValueCursor

Deallocate MyValueCursor

set @MyValues = @MyValues + ') '

select @MyValues as MyValues

-------------------------------------------- Lastly do the Update

delete from @TempVariables

insert into @TempVariables

select

ti.DecInst + ' = ' + tv.DecVals

from

@TempInsert ti

inner join @TempValues tv on ti.ID = tv.ID

--select * from @TempVariables

declare @MyUpdate varchar(5000)

DECLARE MyUpdateCursor CURSOR FOR

select * from @TempVariables

Open MyUpdateCursor

fetch next from MyUpdateCursor into @ReturnStr

while @@FETCH_STATUS = 0

begin

if @MyUpdate is null

begin

set @MyUpdate = 'Update ' + @TableName + ' Set ' + @ReturnStr

end

else

begin

set @MyUpdate = @MyUpdate + ', ' + @ReturnStr

end

fetch next from MyUpdateCursor into @ReturnStr

end

Close MyUpdateCursor

Deallocate MyUpdateCursor

set @MyUpdate = @MyUpdate + ' Where'

select @MyUpdate as MyUpdate

SQL Scripts

Dynamically declare the variables for a table (to use with insert/update)