MS SQL query for advance search

Question

I need to write a ms sql query for advance search which is based on different filters like Title, Description, Category, Author, Language & Dates

I need this query as a stored procedure but i am not sure how i can build query if CatID = 0, WriterID = 0 and so on. I can build this query easy in c# (ASP.Net) using IF Statement & pass whole query, but i am not sure how to build it as T-SQL or Store Procedure

DECLARE @keyword nvarchar(300)
DECLARE @CatID int
DECLARE @WritterID int
DECLARE @IssueID int
DECLARE @sDate date
DECLARE @eDate date
DECLARE @LangID int
    SET @keyword = 'xyz';
    SET @CatID = 1;
    SET @WritterID = 1;
    SET @IssueID = 1;
    SET @sDate = '1/01/2012';
    SET @eDate = '1/01/2013';
    SET @LangID = 1


SELECT ArticleID,ArticleTitle,ArticleCategoryID,ArticleAuthorID,IssueID,ArticlePublishDate FROM art_Articles
    WHERE  ArticleTitle LIKE '%'+ @keyword +'%' 
    OR   ArticleDesc LIKE '%'+ @keyword +'%'

Code example in C#

    strSql = "SELECT ArticleID, ArticleTitle, ArticleDesc, ArticlePublishDate FROM art_Articles WHERE ";
    strSql += "( (ArticleVisible = 1 AND ArticleActive =1 AND LanguageID =" + LangID + " ))";
        if (CatID > 0)
        { strSql += " AND  ArticleCategoryID ="+ CatID; }
        if (WriterID > 0)
        { strSql += " AND  ArticleAuthorID ="+ WriterID; }

I think you could benefit from reading this: [Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search-2008.html) — Mikael Eriksson, Mar 11 '13 at 06:27
Check this : http://stackoverflow.com/questions/263801/sql-server-dynamic-queries — Ravi Singh, Mar 11 '13 at 06:33
Can't emphasize enough the importance of reading the article linked by @MikaelEriksson — Remus Rusanu, Mar 11 '13 at 08:41

Tom Bowen · Accepted Answer · 2013-03-12T07:51:47.357

2

You can build up your SQL in SqlServer in the same way that you do in your C# code.

Declare a text variable and use this to concatenate together your desired SQL.

This is known as Dynamic SQL.

You can then execute this code using the "EXEC" command

Example:


DECLARE @SQL VARCHAR(100)
DECLARE @LangID VARCHAR(20)
DECLARE @CatID VARCHAR(20)
DECLARE @WriterID VARCHAR(20)

SET @SQL = 'SELECT ArticleID, ArticleTitle, ArticleDesc, ArticlePublishDate FROM art_Articles WHERE '

SET @SQL = @SQL + '( (ArticleVisible = 1 AND ArticleActive =1 AND LanguageID =' + @LangID

IF (@CatID > 0)
    SET @SQL = @SQL + ' AND  ArticleCategoryID =' + @CatID

IF (@WriterID > 0)
    SET @SQL = @SQL + ' AND  ArticleAuthorID ='+ @WriterID

EXEC (@SQL)

However, something you should really watch out for when using this method is a security problem called "SQL Injection".

You can read up on that here: http://msdn.microsoft.com/en-gb/library/ms161953(v=sql.105).aspx

One way to guard against SQL injection is to validate against it in your C# code before passing the variables to SQL-Server.

An alternative way (or probably best used in conjecture) is instead of using the "EXEC" command, use a built in stored procedure called "sp_executesql".

Details of which can be found here: http://msdn.microsoft.com/en-gb/library/ms188001.aspx How it is used is detailed here: http://msdn.microsoft.com/en-gb/library/ms175170(v=sql.105).aspx

You'll have to build your SQL slightly differently and pass your parameters to the stored procedure as well as the @SQL.

edited Mar 12 '13 at 07:51

answered Mar 11 '13 at 11:13

Tom Bowen

8,214
4
22
42

I have implement similar approach to solve this problem & i am aware of sql injection vulnerabilities in this approach. I used the same approach which i posted as a answer. I agree with you about the vulnerabilities which need to be taken care of using strong validation. I would appreciate if you can suggest any other approach which is much safer. – Learning Mar 12 '13 at 04:15
I also noticed in your approach you have used `Varchar()` datatype for every variable which can make validation difficult inside T-SQL & if you define Integer variables as `INT` Then you get conversion error `Conversion failed when converting the varchar value`. I am not able to find reason for this but validation can be handles by code so that one get a clean sql query. It will be difficult to handle sql injection specially if sql injection attach has HEX code is that what they call it SQL Query with HEX... i am not sure how to filter if they insert HEX Code. Any this is a different Q? – Learning Mar 12 '13 at 04:30
I've updated my answer with a little more detail on how to guard against SQL Injection. This is mainly the use of "sp_executesql" instead of "EXEC". – Tom Bowen Mar 12 '13 at 07:53
that is what i am doing exactly i used this link to build my sql http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure – Learning Mar 12 '13 at 07:57
I believe that is the best way to procede. Does it cover everything you are unsure about? Do you have any more questions? – Tom Bowen Mar 12 '13 at 08:09

score 0 · Answer 2 · answered Mar 11 '13 at 07:57

0

I found good solution on codeproject.com for dynamic sql queries within stored procedure

http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

answered Mar 11 '13 at 07:57

Learning

19,469
39
180
373

MS SQL query for advance search

2 Answers2