create variable name dynamically

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 2008 Forums

Transact-SQL (2008)

create variable name dynamically

Author

Topic

pota0311
Starting Member

4 Posts

Posted - 2010-08-08 : 08:36:56

Hello,

is it possible somehow to achieve following logic:

declare @var1 as int
declare @name as nvarchar(10)

set @name = 'var1'

getvalue('@' + @name)

Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 08:49:52

please explain a little more what do you need/want.

pota0311
Starting Member

4 Posts

Posted - 2010-08-08 : 08:58:20

thanks for prompt reply

I would like to know is it possible to access variable by name.

Parameters are passed to sql command, without my internal declaration (radgrid update command). The list of paramters (in fact @ColumnName) I can get, as I can retrieve column names from another table. Content of this table is dynamic, but coresponds always to shown grid.

so, in my t-sql I have to go through loop of all retrieved column names, and get passed parameter value.
If name could be constructed with '@' + @ColumnName or in similar way

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 09:06:34

If I understand you correctly, try this:

select 
ordinal_position
,column_name 
,'@'+ column_name as variable_column_name
from information_schema.columns 
where table_name = 'MyTableName' 
order by ordinal_position asc

this is a system view to retrieve all the relevant information on a desired table. You can use column ordinal_position for your while loop to get to each of the column names and pass the column_names to another parameter/variable for your web purposes.

pota0311
Starting Member

4 Posts

Posted - 2010-08-08 : 09:18:41

this is not what I need

in rad grid update, changed value is passed to t-sql via parameters.

For example TelephoneNumber column in parameter @TelephoneNumber.
I do not declare in my t-sql these parameters, but must use their values.
as I use same t-sql for different tables, i want to do it dyanmically, obtain names of used columns, and get associated parameter value.

Basically this is getting back to my initial post:

set @name = 'TelephoneNumber'

getvalue('@' + @name)

where @TelephoneNumber is in fact passed to my t-sql

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 09:29:57

i do not understand your situation.
besides that

getvalue

is not t-sql statement/command but property statement in C#.

pota0311
Starting Member

4 Posts

Posted - 2010-08-08 : 09:37:26

exactly

that is the reason I asked
"is it possible somehow to acheive the logic"

and logic is to get value of variable, where variable name is composed as string, by concatenation of 2 substrings

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 09:49:00

not that i would know.
is there a way to retrieve this values from a table?

parody
Posting Yak Master

111 Posts

Posted - 2010-08-11 : 04:50:51

If I understand you correctly, I do this sometimes when I have repeated queries across many tables but with different column names.

I achieve it by using a control table, like:

RowID TableName SelectColumns
1 T1 TelNumber AS Phone
2 T2 Phone AS Phone
3 T3 Tel AS Phone
4 T4 Num AS Phone

Then construct and execute a dynamic SQL query something like

DECLARE @SQL varchar(max)

SELECT
@SQL = ISNULL(@SQL,'')
+ 'SELECT ' + SelectColumns + ' FROM ' + TableName
FROM ControlTable
ORDER BY RowID

EXEC (@SQL)

You could union them or do something else too like add predicates to the control and query construction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources