simple question on stored proc using LIKE & column

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

Transact-SQL (2005)

simple question on stored proc using LIKE & column

Author

Topic

mattzyzy
Starting Member

4 Posts

Posted - 2008-02-23 : 03:53:29

am currently working on search using simple filtering based on selectable column from a table. but , below sp not work as I wanted , and obviously the @columns variable must be the correct column name (to prevent user from inputting the wrong column name , I planning to use combobox/dropdownlist for this in my app)
---------
create PROCEDURE dbo.searchfilterbycolumn
(
@categorytext varchar(100) ,
@columns varchar(30) ='%'
)
AS
SELECT *
FROM tenders
WHERE @columns like @categorytext
---------
the result set when I execute : exec searchfilterbycolumn 't%','titlename' with recompile
;produces all data rows from the table tenders , obviously it only return rows of data ( all of them) when the @categorytext match the @columns. Help needed....
--------------
and then I tried below sp , just change the @columns to [@columns],also failed , the error is : invalid column name in @columns
------
create PROCEDURE dbo.searchfilterbycolumn
(
@categorytext varchar(100) ,
@columns varchar(30) ='%'
)
AS
SELECT *
FROM tenders
WHERE [@columns] like @categorytext
----------
obviously I want a solution using normal user-defined stored procedure. I am planning to check only 2 or 3 columns with the same data type-varchar(150) , and from a table join to another table . the result set would return 7-8 columns per row of data .
-Could you explain to me how this can be done using normal stored proc ?

I only want this sp to return some data rows by word matches ,@categorytext ,from the column name selected,@columns.Help and thank you in advance .

----------
merci

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 04:00:01

You need to use dynamic sql for this. try like this:-

create PROCEDURE dbo.searchfilterbycolumn
(
@categorytext varchar(100) ,
@columns varchar(30) ='%'
)
AS
DECLARE @Sql varchar(8000)

SET @Sql='SELECT *
FROM tenders
WHERE ['+ @columns + '] like ''' + @categorytext + ''''

EXEC(@Sql)
GO

mattzyzy
Starting Member

4 Posts

Posted - 2008-02-23 : 04:33:26

Thank you with the solution visakh .
I made a lil improvement to accomodate %searchword% for the LIKE using Northwind db ,might be useful to others out there.
-----------
CREATE PROCEDURE dbo.searchfilterbycolumn2
(
@categorytext varchar(100) ,
@columns varchar(30) ='%'
)
AS
DECLARE @Sql varchar(8000)

SET @Sql='SELECT *
FROM Employees
WHERE ['+ @columns + '] like ''%' + @categorytext + '%'''

EXEC(@Sql)
GO
-----
sample execute :
exec searchfilterbycolumn2 'Sale','Title'
will return all employees records whose have the word 'sale' in their position title...

----------
merci

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 04:34:57

Thanks matt zyzy for posting the complete solution. It will definitely benefit others.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-23 : 06:15:22

But if possible avoid passing object names as parameters
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources