February 21, 2008 at 1:54 pm
I 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
----------
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
February 22, 2008 at 2:54 am
is that mean I cant do it using generic user-defined stored procedure ?
my app would really be easier if I can stick to use the normal stored proc ......
February 22, 2008 at 3:14 am
I am new to this , so can you please elaborate how to execute it, I notice you were using system stored proc .
I run all the dynamic - sql code you gave and its says the normal msg: the command(s) completed successfully . and next how to make it returns result set output in rows of data by giving let say 'title' for @columns and 'me' for @categorytext ?
February 22, 2008 at 3:36 am
This code presented by Adam should return rows in normal way, provided there are data for given combination of parameters.
How many columns do you have? If a few only, I would create 'normal' stored procedures, separate for each of columns. I suppose query optimizer may have a hard time trying to prepare proper execution plan with such dynamic sql - this may cause strange performance issues.
Piotr
...and your only reply is slàinte mhath
February 22, 2008 at 6:58 pm
I 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 ?
February 23, 2008 at 2:41 am
Thank you with the solution Adam .
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(150) ,
@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...
February 23, 2008 at 8:58 am
You might care to consider not always putting the leading % in there if you don't absolutely have to. This forces a scan of whatever is returned by the rest of the where clause, which is a rather inefficient operation, and costly It also will limit your ability to do searches for "begins with" or "end with".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2008 at 1:58 pm
As Matt said, I would not hard code the % sign. I would send a string, with percent signs, if needed.
exec searchfilterbycolumn2 'Sale','%Title%'
This way if you do not need to use like, you can execute like this.
exec searchfilterbycolumn2 'Sale','Title'
February 23, 2008 at 5:58 pm
In addition to dynamic SQL you could also make use of SQL's ability to do short-circuit evaluation.
In the following example @columns= is evaluated first. Only if it's true will it evaluate the 2nd condition.
CREATE PROCEDURE dbo.searchfilterbycolumn2
(
@categorytext varchar(150) ,
@columns varchar(30) ='%'
)
AS
SELECT *
FROM Employees
WHERE
(
(@columns = 'COL1' AND (COL1 LIKE @categorytext)) OR
(@columns = 'COL2' AND (COL2 LIKE @categorytext)) OR
(@columns = 'COL3' AND (COL3 LIKE @categorytext))
)
GO
-- Usage:
EXEC searchfilterbycolumn2 @columns = 'COL3', @categortytext = '%me%'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply