Help on passing a column name to a stored procedur

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

Transact-SQL (2000)

Help on passing a column name to a stored procedur

Author

Topic

Apprentice
Starting Member

3 Posts

Posted - 2005-01-24 : 04:34:30

Is this possible? I would like to pass a column_name into a stored procedure. im using sql server 2000

i tried this code but it didn't work

create procedure test @field varchar(30),@query varchar(30)
OUTPUT AS select * from table where @field=@query
RETURN 0

execute test 'fieldname', 'string'

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2005-01-24 : 04:38:15

you need to use dynamic sql ...
something like

create procedure test
@field varchar(30),
@query varchar(30)
AS
begin
declare @SQL nvarchar(1000)
set @sql ='select * from table where '+@field+'='+@query

sp_executesql @sql
end

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com
http://www.sqljunkies.com/weblog/sqldude

Apprentice
Starting Member

3 Posts

Posted - 2005-01-26 : 01:32:00

Thank you very much for the tip.

this is what i did

create procedure sample @field nvarchar(10),@query nvarchar(10) as
declare @sqlstatement nvarchar(4000)
set_quoted_identifier off
set @sqlstatement='select * from table where ' + @field +'= "' + @query + '"'
exec sp_executesql @sql
return 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources