Using parameterized table/column names in a sp

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)

Using parameterized table/column names in a sp

Author

Topic

ayaznazir
Starting Member

1 Post

Posted - 2007-11-12 : 15:14:23

Hi,
I am trying to develop a 'Search' utility for a web based application. SQL Server 2000 is being used at the backend. The aim of the utility is to provide users a list of tables (using sp_tables stored procedure of SQL Server 2000 system catalog). The user selects a table from the list. Then user selects a column from the list (obtained by using sp_columns stored procedure). The user then provides a search string. The names of the selected table, column and the string to be searched are passed to a stored procedure (say sp_mysearch) as parameters via Microsoft ADODB connection.

Now my three questions are:

(1) How to pass table and column names as parameter to a stored procedure say sp_mysearch? Would there be any difference from passing normal value type parameters?

(2) How will I recieve and declare these parameters (table/column type or what..) in the stored procedure sp_mysearch?

(3) How will I use these parameters in sql SELECT query inside my stored procedure sp_mysearch.

Looking forward
Ayaz

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 15:26:03

there are probably scripts around that already do this, but if you want to try from scratch this may be of some help...

declare @table_name sysname
declare @column_name sysname
declare @search_value nvarchar(1000)

declare @sql nvarchar(4000)
select @sql =
'SELECT * FROM [' +
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
'] WHERE CONVERT(nvarchar(1000), [' + INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME + ']) = ' +
char(39) + @search_value + char(39)
from INFORMATION_SCHEMA.COLUMNS
where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @table_name
and INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = @column_name

print @sql

exec(@sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources