Prev: Select only columns with contents
Next: Collect csv from ftp server and bulk import
From: kizildag on 30 Jun 2010 08:31
Hi,

I want to retrieve data under some conditions like below;

SELECT T.A,T.B
FROM TABLE1 T
WHERE T.X = PARAM1 AND T.Y = PARAM2

what i need to do is, to retrieve data ;
1) if no params (PARAM1 and PARAM2) entered
2) if one of the params (PARAM1 or PARAM2) entered
3) both of the params (PARAM1 and PARAM2) entered

Is there a way to construct this kind of SELECT statement?

From: Tom Cooper on 30 Jun 2010 08:59
There is a good web page at
http://www.sommarskog.se/dyn-search.html
which will lead you to one of two pages depending on which version of SQL
you are running. They have a good discussion of the methods available to do
this.

Tom

"kizildag" <u61477(a)uwe> wrote in message news:aa4ca4d2c329e(a)uwe...
> Hi,
>
> I want to retrieve data under some conditions like below;
>
> SELECT T.A,T.B
> FROM TABLE1 T
> WHERE T.X = PARAM1 AND T.Y = PARAM2
>
> what i need to do is, to retrieve data ;
> 1) if no params (PARAM1 and PARAM2) entered
> 2) if one of the params (PARAM1 or PARAM2) entered
> 3) both of the params (PARAM1 and PARAM2) entered
>
> Is there a way to construct this kind of SELECT statement?
>

From: deeg on 30 Jun 2010 14:27
On Jun 30, 7:59 am, "Tom Cooper" <tomcoo...(a)comcast.net> wrote:
> There is a good web page athttp://www.sommarskog.se/dyn-search.html
> which will lead you to one of two pages depending on which version of SQL
> you are running.  They have a good discussion of the methods available to do
> this.
>
> Tom
>
>
>
> "kizildag" <u61477(a)uwe> wrote in messagenews:aa4ca4d2c329e(a)uwe...
> > Hi,
>
> > I want to retrieve data under some conditions like below;
>
> > SELECT T.A,T.B
> > FROM TABLE1 T
> > WHERE T.X = PARAM1 AND T.Y = PARAM2
>
> > what i need to do is, to retrieve data ;
> > 1) if no params (PARAM1 and PARAM2) entered
> > 2) if one of the params (PARAM1 or PARAM2) entered
> > 3) both of the params (PARAM1 and PARAM2) entered
>
> > Is there a way to construct this kind of SELECT statement?- Hide quoted text -
>
> - Show quoted text -

declare @sql as varchar(500)

select @sql = 'SELECT T.A, T.B FROM TABLE1 T WHERE 1 = 1 '

if PARAM1 NOT IS NULL
begin
select @sql = @sql + ' and T.X = ' + PARAM1
end

if PARAM2 NOT IS NULL
begin
select @sql= @sql + ' and T.Y = ' + PARAM2
end

select @sql = @sql + ';'

exec (@sql);

If the PARAM variables are not strings they will need to be converted
to a varchar format for appending and if they are strings you will
need to add extra ' marks to encase the PARAM appends in quotes.
From: Peso on 1 Jul 2010 04:18
SELECT T.A,T.B
FROM TABLE1 T
WHERE (T.X = @PARAM1 OR @PARAM1 IS NULL)
AND (T.Y = @PARAM2 OR @PARAM2 IS NULL)

//Peso

 | 
Pages: 1
Prev: Select only columns with contents
Next: Collect csv from ftp server and bulk import