Re: Dynamic SQL question

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 12:19:50 +0530

If you are going the Dynamic SQL way, make sure that you read

http://www.sommarskog.se/dynamic_sql.html 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"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

  • 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: Dynamic SQL question
    ... AccountName + ''' ' ... you should note that you're opening yourself up for SQL injection ... arguments and dynamic SQL: ... > declare @ID int ...
    (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)
  • Re: using variables in WHERE clause
    ... For column name in where clause, you have to use dynamic SQL ... declare @sql nvarchar ... exec sp_executesql @sql ...
    (microsoft.public.sqlserver.programming)