Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
khusiie
Yak Posting Veteran
78 Posts |
 Posted - 2010-03-26 : 10:33:53
|
| i have used one case statement in below procedure..CREATE PROCEDURE ProcType@Type varchar(100)AS SELECT TypeID, TypeName,TypeOrg,TypeTitle,Type = @Type ,TypeDate,TypeLocation,TypeAddress,From TypeTab WHERE TypeNum = 1 AND @Type= ( select case when (Type.Part = 'True' OR = Type.Part'False') then Type='ALL Types' when Type.Part = 'True' then Type='Major Types' when Type.Part = 'False' then Type='Minor Types'End as Type)i m getting results for only ALL Types not for Major Types and Minor types. i know because of parameter it will give first value but what should i need to change here in condition? any suggestion?Thanks. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-26 : 10:39:10
|
It is not clear what you are trying to do.Can you explain using examples?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
khusiie
Yak Posting Veteran
78 Posts |
Posted - 2010-03-26 : 10:45:49
|
| well, there is table typetab and in that column called type.part so if its true or false than will ahve to display for all types and if its only tue than display results for major types and if false than display results for minor types. and @type parameter which will be passed there will be 3values for that all types, major types and minor types. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-26 : 10:47:00
|
| (Type.Part = 'True' OR = Type.Part'False')Firstly, you're not selecting from a table named Type. Secondly OR = is a syntax error. Perhaps you meant "OR Type.Part = 'False'". In any case, there's no FROM clause in your subquery.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
khusiie
Yak Posting Veteran
78 Posts |
Posted - 2010-03-26 : 10:56:24
|
| i modified it..but now it says Subquery returned more than 1 value..CREATE PROCEDURE ProcType@Type varchar(100)AS SELECT TypeID, TypeName,TypeOrg,TypeTitle,Type = @Type ,TypeDate,TypeLocation,TypeAddress,From TypeTab WHERE TypeNum = 1 AND @Type In( select case when (Type.Part = 'True' OR = Type.Part = 'False') then Type='ALL Types' when Type.Part = 'True' then Type='Major Types' when Type.Part = 'False' then Type='Minor Types'End from typetab as Type) |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-26 : 11:18:53
|
I've cleaned up some syntax error below, however I'm still not sure what you're after. Also the highlighted part of the case statement will never produce a match, because they'll be matched by the first WHEN clause. CASE returns the first match, so if (Type.Part = 'True' OR = Type.Part'False') evaluates to true, it will return 'All Types', leaving the remaining WHEN sections redundant.I don't think selecting the entire table in the WHERE subquery is doing what you want. Perhaps you could go into more detail about what you're trying to achieve. What are you passing as a parameter to the sp? What is stored in the typetab.Part fields? CREATE PROCEDURE ProcType@Type varchar(100)ASSELECTTypeID,TypeName,TypeOrg,TypeTitle,Type = @Type ,TypeDate,TypeLocation,TypeAddressFrom TypeTabWHERE TypeNum = 1AND @Type In( select case when (Type.Part = 'True' OR Type.Part = 'False') then 'ALL Types'when Type.Part = 'True' then 'Major Types'when Type.Part = 'False' then 'Minor Types'End from typetab as Type) There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
|
|
|
|
|
case query