Hard Stored Procedure?

I'm writing a stored procedure that will look for 'string' in the 'n'th
column. For instance,
exec QueryTable 1,'aString'
...should look for the string 'aString' in the first column of a specific
table. Does anyone know how to do this in a generic way, so that I don't hav
e
a big IF statement where I write the query once for each column?Try this, salt to taste (and add better exception handling):
create procedure ap_get_rows
@.table sysname,
@.field int,
@.value varchar(4000)
as
declare @.fieldname sysname
declare @.sql varchar(4000)
select @.fieldname = [name]
from syscolumns
where id = object_id(@.table) and colid = @.field
set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] = '''
+ @.value + ''''
print @.sql
exec (@.sql)
go
Mike|||courtesy of Steve Kass:
http://www.users.drew.edu/skass/sql...lTables.sql.txt
-oj
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:EAF72EFC-B55A-4F56-B6AF-DF48BB05177A@.microsoft.com...
> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't
> have
> a big IF statement where I write the query once for each column?|||I forgot the disclaimer: "This stored procedure uses dynamic SQL which is
known to perform less efficiently than compiled SQL statements. Use with
caution especially in performance sensitive operations."
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23LXQyFeRFHA.4028@.tk2msftngp13.phx.gbl...
> Try this, salt to taste (and add better exception handling):
>
> create procedure ap_get_rows
> @.table sysname,
> @.field int,
> @.value varchar(4000)
> as
> declare @.fieldname sysname
> declare @.sql varchar(4000)
> select @.fieldname = [name]
> from syscolumns
> where id = object_id(@.table) and colid = @.field
> set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] =
'''
> + @.value + ''''
> print @.sql
> exec (@.sql)
> go
>
> Mike
>|||You have to use dynamic sql.
Example:
use northwind
go
create procedure dbo.proc1
@.ts sysname = N'dbo',
@.tn sysname,
@.ordinal_position int,
@.value varchar(50)
as
set nocount on
declare @.sql nvarchar(4000)
declare @.cn sysname
select
@.cn = column_name
from
information_schema.columns
where
table_schema = @.ts
and table_name = @.tn
and ordinal_position = @.ordinal_position
if @.cn is not null
begin
set @.sql = N'select ' + quotename(@.cn) + N' from ' + quotename(@.ts) + '.' +
quotename(@.tn) + N' where ' + quotename(@.cn) + N' like ''%' + replace(@.value
,
'''', ''') + N'%'''
print @.sql
exec sp_executesql @.sql
end
go
exec dbo.proc1 @.tn = N'customers', @.ordinal_position = 2, @.value = 'La
maison d''Asie'
go
exec dbo.proc1 @.tn = N'employees', @.ordinal_position = 4, @.value =
'Representative'
go
drop procedure proc1
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Ken" wrote:

> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't h
ave
> a big IF statement where I write the query once for each column?