Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>