Pass column name as parameter - SQL Server Forums

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)

Pass column name as parameter

Author

Topic

spikeyredchick
Starting Member

5 Posts

Posted - 2007-01-17 : 05:26:23

Can someone tell me why this will not work and what I could do to get it to work please.

CREATE PROCEDURE [dbo].[sp_ThisTest]

 
@SearchCriteria varchar(11),

@ToSearch varchar(30)

 
AS

 
SELECT * FROM MyTable WHERE @SearchCriteria = @ToSearch  ORDER BY UniqueID desc

GO

How could I pass the column name as a variable?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-17 : 05:40:22

you can't.
in order for this to work you'll have to use dynamic sql.
note that with that you're opening yourself to a potential security problems
and doing like that isn't a ver good idea.

declare @stmt nvarchar(2000)
select @stmt = 'SELECT * FROM MyTable WHERE ' + @SearchCriteria + ' = ' + @ToSearch + ' ORDER BY UniqueID desc'
exec sp_executeSQL @stmt

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spikeyredchick
Starting Member

5 Posts

Posted - 2007-01-17 : 05:53:37

Thanks Spirit1,

I thought that was possibly the case. Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources