How to declare variable of type Column

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)

How to declare variable of type Column

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2009-08-17 : 18:20:45

Hi ... Hope all of you will be fine and happy with mercy of God.
I need a solution query of a problem.
I need to create a procedure such that the column name in the Where clause should be dynamic i.e.

a Query must be like this
declare @Column as (Column Type)
declare @Value as varchar or (variable type)

Select * from TableName where @Column = @Value

Now how can I do it? Can anybody tell me is it possible or not?
One more thing. As data type of each column is different, so @Value must be depend upon that Column type.

I mean as ContactID is integer, so @Column must accept integer @Value and if ContactName is varchar, then @Column must accept Varchar @Value.

I am waiting for a useful reply. Thanks

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-17 : 18:52:07

[code]Create Proc myProc
@column sysname,
@value varchar(255)
AS

EXEC ('SELECT * FROM TableName WHERE [' + @column + '] = ' + @value)[/code]

azharrahi
Starting Member

44 Posts

Posted - 2009-08-17 : 20:23:32

thanks .... this query is workig for equal operator (i.e =) .... and for integer id
But when I use the same query for Like it does not work. e.g.

declare @Column as sysname
declare @Value as varchar(100)

set @Column = 'Land'
set @Value = '%Germany%'

Exec ('select * from ADR_STAMM where [' + @Column + '] Like ' + @Value)

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 20:31:08

read more about Dynamic SQL at The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

azharrahi
Starting Member

44 Posts

Posted - 2009-08-17 : 22:27:26

well after debugging query what I got is here

first here is a query

declare @Column as sysname
declare @Value as varchar(100)

set @Column = 'adr_id'
set @Value = '1000'

Exec ('select * from ADR_STAMM where [' + @Column + '] Like ' + @Value )

when I pass name of Column of integer type to @Column and pass integer value to @Value it runs fine .... The bebugger shows the query as follows ...

select * from ADR_STAMM where [adr_id] Like 1000

but when I pass name of column of Varchar type and want to pass the varchar it throws error.

declare @Column as sysname
declare @Value as varchar(100)

set @Column = 'land'
set @Value = 'Germany'

what debugger shows is as follows:

select * from ADR_STAMM where [land] Like Germany ....

that is it takes Germany also as integer type and I need to show it as varchar type....
How can I pass varchar value to @Value which is already decalred as varchar. Is it possible something like

set @Value = '"Germany"' .... I need to pass string (varchar) to value ... can anybody help me ?

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 22:54:01

[code]'] Like ''' + @Value + ''''[/code]

KH
[spoiler]Time is always against us[/spoiler]

azharrahi
Starting Member

44 Posts

Posted - 2009-08-18 : 12:34:21

Thanks for the help. It worked for me.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-19 : 03:57:33