Re: Dynamic SQL question

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 18:01:53 -0400

You're basically looking for something like this:

SET @where =
        '1=1 ' +
        CASE
            WHEN @ID IS NOT NULL THEN 'AND ID=' + CAST(@ID AS VARCHAR) + ' '
            ELSE ''
         END +
        CASE
            WHEN @AccountName IS NOT NULL THEN 'AND AccoutnName=''' +
AccountName + ''' '
            ELSE ''
         END

However, you should note that you're opening yourself up for SQL injection
attacks by doing this. So you should instead use sp_executesql. You should
read the following two articles which will tell you all about dynamic
arguments and dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

"John Dude" <jhd8989@hotmail.com> wrote in message
news:uIZ5bepbEHA.3476@tk2msftngp13.phx.gbl...
> Hi!
>
> I need to build a where clause for my SQL query. Please see the code
> below:
>
> create table #Accounts
> (ID int,
> AccountName varchar(100))
>
> insert into #Accounts values (1, '12345')
> insert into #Accounts values (2, '99999')
>
> declare @SQL varchar(100)
> declare @where varchar(20)
> declare @ID int
> declare @AccountName varchar(10)
>
> set @ID = 2
> set @AccountName = '12345'
>
> set @where = case when @ID is not null then 'ID = ' +
> convert(varchar(20), @ID) end
> case when @AccountName is not null then 'AccountName = ' +
> @AccountName
>
> set @SQL = 'select ID, AccountName from #Accounts where ' + @where
> --print @SQL
> exec (@SQL)
>
> How do I build WHERE clause in such a way that if @ID and @AccountName
> (both variables) are not nulls, the @WHERE clause would construct this
> string:
> ID = 2 AND
> AccountName = '12345'
>
> If one is Null (lets say AccountName is null) then it would construct:
> ID = 2
>
> If both are Nulls, the @where clause would be just blank i.e.
> select ID, AccountName from #Accounts
>
> How do I do this?
>
> THANX for your help!
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Dynamic SQL question
    ... If you are going the Dynamic SQL way, ... > I need to build a where clause for my SQL query. ... > declare @where varchar ... > declare @AccountName varchar ...
    (microsoft.public.sqlserver.programming)
  • Checking the uniqueness of data
    ... I wish to use SQL to interrogate this table to find out how many non-unique ... userNames there are for each accountName. ... I've no idea how to achieve this in SQL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Checking the uniqueness of data
    ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • RE: Checking the uniqueness of data
    ... count(distinct userName) end as count_distinct_userName ... "Griff" wrote: ... > userNames there are for each accountName. ... I've no idea how to achieve this in SQL. ...
    (microsoft.public.sqlserver.programming)
  • Dynamic SQL question
    ... I need to build a where clause for my SQL query. ... AccountName varchar) ... declare @ID int ...
    (microsoft.public.sqlserver.programming)