Dynamic WHERE Clause - SQL Server Forums

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)

Dynamic WHERE Clause

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-08-23 : 16:28:30

I'm building a dynamic WHERE clause based on an article from this site and I've run into a snag. The below works well, but on occasion I need to pass a comma-delimited string as
one of the parameters. My sproc will run in an ASP.NET page which contains a few listboxes and I allow the user to select multiple values within the listbox.

**********************************
CREATE PROCEDURE sp_TopExecSearch
@CompanyName varchar(100) = NULL,
@Ticker varchar(20) = NULL,
@Title varchar(100) = NULL
AS

SELECT CompanyName,
Ticker,
Title
FROM
TCompanies
WHERE
CompanyName LIKE COALESCE('%' + @CompanyName + '%', CompanyName) AND
Ticker = COALESCE(@Ticker,Ticker) AND
Title LIKE COALESCE('%' + @Title + '%', Title)

GO
**********************************

I currently use this inline code to build my SQL statement using the IN function to look for the values in the listbox but how would I migrate this to the stored procedure above?

SqlText += " (Industry IN ('" & Replace(strSearchValue, ",", "','") & "')"

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-08-23 : 16:46:18

If you want to return the Comma Delimited string you can use an OUTPUT parameter and return it to the Asp.net page. Check out the link below:

http://gridviewguy.com/ArticleDetails.aspx?articleID=102

Mohammad Azam
www.azamsharp.net

evanburen
Posting Yak Master

167 Posts

Posted - 2005-08-24 : 22:45:32

I actually want to pass a comma delimited string to the stored procedure - not produce one as output. The string will be used something like this

WHERE
CompanyName LIKE COALESCE('%' + @CompanyName + '%', CompanyName) AND
Ticker = COALESCE(@Ticker,Ticker) AND
Title LIKE COALESCE('%' + @Title + '%', Title) AND
Industry IN ('Transportation','Food','Finance')

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-24 : 22:59:48

For this kind of task is better to use UDF, like:

...
set @CommaDelimitedString = 'Transportation,Food,Finance'
...
WHERE
...
Industry IN (select * from dbo.fn_StringToTable(@CommaDelimitedString)]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources