conditional WHERE clause

Hi
I have an SP with a few params and I wish to use one of the params in the
WHERE clause, to conditionaly set a WHERE statement, for example:
CREATE PROC sp_Test
@.ProductCode VARCHAR(5)
@.ProcessMonth INT = NULL,
@.ProcessYear INT = NULL
AS
SELECT Table.ID
FROM Table
WHERE
Table.PMonth = @.ProcessMonth
AND Table.PYear = @.ProcessYear
AND
IF @.ProductCode = 'ALL' THEN
BEGIN
Table.ProductCode = ('A','B','C' etc...all codes, need this to
be dynamic)
END
ELSE
BEGIN
Table.ProductCode = @.ProductCode
END
Hope this makes sense, I also don't know how to make the 'ALL' return all
records?
Kind Regards
RickyPWHERE
ProductCode = CASE @.ProductCode WHEN 'ALL' THEN ProductCode ELSE
@.ProductCode END
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||You may benefit by reading the article at:
http://www.sommarskog.se/dyn-search.html
Anith|||Hi
use northwind
go
create proc spproc
@.custid varchar(10)
as
select * from orders where CustomerID=
case when @.custid='all' then CustomerID else @.custid end
--usage
exec spproc 'vinet'
exec spproc 'all'
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Hi Ricky,
How about :
SELECT * FROM MyTable
WHERE (Table.ProductCode = @.ProductCode OR @.ProductCode = 'ALL')
Should do what you're looking for. Personally, rather than 'ALL' I'd use
NULL to indicate that no filter should be applied (i.e. all product codes),
but this will work fine.
Cheers,
Alex
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Great minds think alike - thanks guys for the postings, didn't know you
could do that.
Kind Regards
RickyP
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>

Conditional Column Filter

Hi
I have a field (FieldA) which I need to Filter on, based upon a parameter,
however the filter is only a substring of the FieldA.
e.g
FieldA = H2/Q3/10
the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA
in it's constituent parts, however in a WHERE clause I don't know how to
represent this.
i.e
If the parameter = Q3
then I would do :
WHERE SUBSTRING(FieldA,4,2) = 'Q3'
However if the param supplied was H2, then :
WHERE SUBSTRING(FieldA,4,2) = 'H2'
would not work?
I would therefore need to change the Column on how its being filtered on, is
there a way I can do this?
Kind Regards
RickyTry:
...
where '/' + FieldA + '/' like '%/' + @.s + '/%'
go
Do not expect SQL Server using properly an index by [FieldA] in case it
exists. You can google for "search arguments", for more info.
AMB
"ricky" wrote:

> Hi
> I have a field (FieldA) which I need to Filter on, based upon a parameter,
> however the filter is only a substring of the FieldA.
> e.g
> FieldA = H2/Q3/10
> the filter supplied maybe supplied as Q3, H2,or 10, I how to split up Fiel
dA
> in it's constituent parts, however in a WHERE clause I don't know how to
> represent this.
> i.e
> If the parameter = Q3
> then I would do :
> WHERE SUBSTRING(FieldA,4,2) = 'Q3'
> However if the param supplied was H2, then :
> WHERE SUBSTRING(FieldA,4,2) = 'H2'
> would not work?
> I would therefore need to change the Column on how its being filtered on,
is
> there a way I can do this?
> Kind Regards
> Ricky
>
>|||Any special reason for breaking the normal form by storing three values in a
single column? Have you considered properly normalizing the model?
Anyway, how about using wildcards:
WHERE FieldA like '%H2%'
For a more helpful answer, please provide DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Hi guys
thanks for the replies, won't wildcards be slow? Incidentally, if I use a
wildcard, when I search for 1 in H1Q11, won't it get with H1 or Q1?
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:E4D9EB2A-D620-4794-9220-3442B02B17C4@.microsoft.com...
> Any special reason for breaking the normal form by storing three values in
a
> single column? Have you considered properly normalizing the model?
> Anyway, how about using wildcards:
> WHERE FieldA like '%H2%'
> For a more helpful answer, please provide DDL, sample data and expected
> results.
>
> ML
> --
> http://milambda.blogspot.com/|||Using wildcards and functions in query conditions is slow, in most cases a
scan will be used. To improve the performance by using indexes first conside
r
normalizing the data.
ML
http://milambda.blogspot.com/|||ok ML, will do, thanks for the suggestion and tip.
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:547F6E76-0A5C-43ED-8649-34EEBD431307@.microsoft.com...
> Using wildcards and functions in query conditions is slow, in most cases a
> scan will be used. To improve the performance by using indexes first
consider
> normalizing the data.
>
> ML
> --
> http://milambda.blogspot.com/|||No, the answer Alesandro gave you handles that
(you specified slashes - where have they gone in this followup question?)
Bye
Jan
"ricky" <ricky@.ricky.com> wrote in message
news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Incidentally, if I use a wildcard, when I search for 1 in H1Q11, won't it
get with H1 or Q1?|||Hi Jan
You're quite right, I forgot to place them in.
Well spotted.
Kind Regards
Ricky
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O52SBcijGHA.3440@.TK2MSFTNGP02.phx.gbl...
> No, the answer Alesandro gave you handles that
> (you specified slashes - where have they gone in this followup question?)
> Bye
> Jan
> "ricky" <ricky@.ricky.com> wrote in message
> news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
it
> get with H1 or Q1?
>
>

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIAhttp://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:
>

concatnate multiple results into one field

Hi
I am trying to query a table and return the results all in one field.
My table basicly looks like this:
tblBody:
ID Body
1 This is body 1
2 This is body 2
3 This is body 3
Now i want to select all this data, and concatenate it into one field
So when i do:
SELECT Body FROM tblBody (altered to be the right way of doing this of
course)
The results are:
Body
This is body 1This is body 2This is body 3
and not
Body
1 This is Body 1
2 This is Body 2
3 This is Body 3
TIA
http://www.aspfaq.com/show.asp?id=2529
"Grant Merwitz" wrote:

> Hi
> I am trying to query a table and return the results all in one field.
> My table basicly looks like this:
> tblBody:
> ID Body
> 1 This is body 1
> 2 This is body 2
> 3 This is body 3
> Now i want to select all this data, and concatenate it into one field
> So when i do:
> SELECT Body FROM tblBody (altered to be the right way of doing this of
> course)
> The results are:
> Body
> This is body 1This is body 2This is body 3
> and not
> Body
> 1 This is Body 1
> 2 This is Body 2
> 3 This is Body 3
> TIA
>
>
|||Thanks, that was exactly what i was looking for.
But now i've realised another problem:
The fields i'm trying to join are all of varchar(4000)
So i don't believe there's a variable i can store these in to return in a
Sql query.
Is there?
I may have to return multiple rows and join them in my business layer.
Thanks for you help, any thoughts here?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:63B64C15-2098-4802-B189-60EF51BA668E@.microsoft.com...[vbcol=seagreen]
> http://www.aspfaq.com/show.asp?id=2529
>
> "Grant Merwitz" wrote:

Concatinating two field and insert the result

Hii,
I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?
use northwind

select city, region,([city]+ +[region]) as uniqe
from customers
where region is not null
The resulting records in Quary
Anchorage AK AnchorageAK
Tsawassen BC TsawassenBC
Vancouver BC VancouverBC
San Francisco CA San FranciscoCA

Try it like this:

UPDATE
Customers
SET
city = city + ' ' + region
WHERE
region IS NOT NULL