declare dynamic variables in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

declare dynamic variables in sql server

Author

Topic

lohumi
Starting Member

6 Posts

Posted - 2013-08-01 : 04:49:51

how can i declare variables at run time.
for example-

declare @i int =1
while (@i< 3)
begin
declare @col+@i as varchar(100)
--print @col+@i
set @i=@i+1
end

or in other words i want to declare variables as the number of columns in any table.for ex-
SELECT COLUMN_NAME, DATA_TYPE + COALESCE('(' + CASE
WHEN DATA_TYPE = 'XML' THEN NULL
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'max'
ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
END + ')', '(' + Cast(NUMERIC_PRECISION AS NVARCHAR(5)) + ',' + Cast(NUMERIC_SCALE AS NVARCHAR(5)) + ')', '') + ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
ELSE 'NOT NULL'
END + COALESCE(' DEFAULT' + COLUMN_DEFAULT, '')
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Company'

Thanks
Neeraj Lohumi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 04:52:22

you'll need to form dynamic string declaring the required variables and use EXEC() or sp_executesql to execute the created string

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lohumi
Starting Member

6 Posts

Posted - 2013-08-01 : 04:56:46

can you describe in more detail or would appreciate, if provide any website/forum links.

Thanks

Lohumi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 05:07:40

something like below


DECLARE @ColumnList varchar(max),@SQL varchar(max)


SELECT @ColumnList = COALESCE(@ColumnList+ ',','') + '@' + COLUMN_NAME + ' ' +  DATA_TYPE + COALESCE('(' + CASE
WHEN DATA_TYPE = 'XML' THEN NULL
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'max'
ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
END + ')', '(' + Cast(NUMERIC_PRECISION AS NVARCHAR(5)) + ',' + Cast(NUMERIC_SCALE AS NVARCHAR(5)) + ')', '') + ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
ELSE 'NOT NULL'
END + COALESCE(' DEFAULT ' + COLUMN_DEFAULT, '')
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Company'

SELECT @SQL = 'declare ' + @ColumnList

EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 05:08:27

Can I ask the purpose behind doing this?
The variables will obviously be in scope inside dynamic string alone and you wont be able to use them outside.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lohumi
Starting Member

6 Posts

Posted - 2013-08-02 : 02:18:27

sorry for late reply!i am working in a datawarehouse system.where i need to import the record from many tables.so i am trying to make a script where we will pass the table name as param and it will list the total columns.and accordingly make that number of variables for cursor to get the column data & insert into our DW system. can this be done in any better way? Also want to know how Would i assign values to the variable declared in memory?(refer the code above).
please help !!

Lohumi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-02 : 03:54:55

quote:
Originally posted by lohumi

sorry for late reply!i am working in a datawarehouse system.where i need to import the record from many tables.so i am trying to make a script where we will pass the table name as param and it will list the total columns.and accordingly make that number of variables for cursor to get the column data & insert into our DW system. can this be done in any better way? Also want to know how Would i assign values to the variable declared in memory?(refer the code above).
please help !!

Lohumi

why do you need to create cursor for that? are you telling you cant determine columns beforehand? whats the purpose of creating single code for handling all tables? There should be separate populate scripts for each table in the database.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources