Parameter Problem in simple Stored proc

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

SQL Server Development (2000)

Parameter Problem in simple Stored proc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-07 : 07:09:41

Kostas writes "Hello

I am trying to execute the following code:

declare @theparameter nvarchar
declare @thefieldname nvarchar
select @theparameter = 'a'
select @thefieldname = 'fldprodcode'

--select * from tblProducts where @thefieldname like @theparameter + '%'
select * from tblProducts where fldprodcode like @theparameter + '%'

The first select statement, where i use a parameter as the field name will not work. Is there any workaround for this?

Thank you"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 07:13:08

One of the ways is to use Dynamic SQL

Declare @sql varchar(1000)
set @sql='select * from tblProducts where '+@thefieldname+' like '''+@theparameter + '%''')

Exec(@sql)

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 07:42:18

Another way is to pass in one parameter for each possible fieldname. You could potentially avoid dynamic sql that way.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources