March 25, 2010 at 3:35 am
Hi,
I'm trying to build an sp that will return a list of companies where the company first letter is specified. I also want to cater for the situation where a user can specify ALL (will return all companies) or OTHER (will return all companies that don't start with letters A-Z).
Here's what I've tried but it doesn't work:
ALTER PROCEDURE [dbo].[SelCompany](
@CompanyLetter varchar(10)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT CompanyID,
CompanyName,
Active,
LastModified
FROM tblCompany
WHERE
CASE
WHEN @CompanyLetter = 'ALL' THEN LEFT(UPPER(CompanyName),1) LIKE '%'
WHEN @CompanyLetter IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN LEFT(UPPER(CompanyName),1) LIKE @CompanyLetter
ELSE LEFT(UPPER(CompanyName),1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
END
END
March 25, 2010 at 3:58 am
dec_obrien (3/25/2010)
Hi,I'm trying to build an sp that will return a list of companies where the company first letter is specified. I also want to cater for the situation where a user can specify ALL (will return all companies) or OTHER (will return all companies that don't start with letters A-Z).
Here's what I've tried but it doesn't work:
ALTER PROCEDURE [dbo].[SelCompany](@CompanyLetter varchar(10)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT CompanyID,
CompanyName,
Active,
LastModified
FROM tblCompany
WHERE
CASE
WHEN @CompanyLetter = 'ALL' THEN LEFT(UPPER(CompanyName),1) LIKE '%'
WHEN @CompanyLetter IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
THEN LEFT(UPPER(CompanyName),1) LIKE @CompanyLetter
ELSE LEFT(UPPER(CompanyName),1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
END
END
You can do it by dynamic sql
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 25, 2010 at 3:59 am
Try this as your Where Clause
WHERE 1 = CASE
WHEN @CompanyLetter = 'ALL'
THEN 1
WHEN ASCII( UPPER( @CompanyLetter ) ) BETWEEN 60 AND 90 AND LEFT( UPPER( CompanyName), 1 ) LIKE @CompanyLetter
THEN 1
WHEN @CompanyLetter = 'OTHER' AND ASCII( LEFT( UPPER( CompanyName ), 1 ) ) NOT BETWEEN 65 AND 90
THEN 1
ELSE 0
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2010 at 4:01 am
Unlike other languages in T-SQL case is not use as a control statements that decides which part of the code should be run (Sort of an improved IF statement). Instead in SQL server case is a function that returns a single value according to specified conditions. Your where clause should look like this:
Where CompanyName like CASE WHEN @Param = ‘All’ THEN ‘%’
WHEN @Param IN ( ‘A’, ‘B’…’Z’) THEN @Param + ‘%’ END
By the way, most chances are that I would have done it with 2 different statements and an IF statement that would check which select statement to run. In case of a big table, I’d might use different procedures so each case (getting all recrords or part of the records) will get its own query plan (but I have to admit that most times Companies table is not a big table, so most chances are that performance won’t be a problem.)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 25, 2010 at 4:06 am
Hi,
Kingston's answer works.
Thanks for both answers,
Dec.
March 25, 2010 at 4:07 am
Yet another way:
--CREATE TABLE tblCompany(
-- CompanyID int,
-- CompanyName varchar(max),
-- Active varchar(5),
-- LastModified dateTime
-- )
--INSERT INTO tblCompany(CompanyID, CompanyName, Active, LastModified)
--SELECT 1, 'A Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 2, 'Another Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 3, 'Yet Another Company', 'No', '01 JAN 2010'
--UNION
--SELECT 4, '123 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 5, 'XYZ Company', 'No', '01 MAR 2000'
--UNION
--SELECT 6, '123 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 7, '012 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 8, 'Useless Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 9, 'More Company', 'Yes', '01 MAR 2010'
select * from tblCompany
DECLARE @CompanyLetter VarChar(5)
SET @CompanyLetter = 'U'
SELECT CompanyID,
CompanyName,
Active,
LastModified
FROM tblCompany
WHERE
(@CompanyLetter = 'ALL')
OR
((@CompanyLetter = 'OTHER') AND NOT(CompanyName LIKE '[a-z]%'))
OR
((@CompanyLetter LIKE '[a-z]') AND (CompanyName LIKE @CompanyLetter + '%'))
March 25, 2010 at 4:08 am
Hi Adi,
Thanks for your answer: that is exactly where I went wrong.
Dec.
March 25, 2010 at 4:14 am
BrainDonor (3/25/2010)
Yet another way:
--CREATE TABLE tblCompany(-- CompanyID int,
-- CompanyName varchar(max),
-- Active varchar(5),
-- LastModified dateTime
-- )
--INSERT INTO tblCompany(CompanyID, CompanyName, Active, LastModified)
--SELECT 1, 'A Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 2, 'Another Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 3, 'Yet Another Company', 'No', '01 JAN 2010'
--UNION
--SELECT 4, '123 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 5, 'XYZ Company', 'No', '01 MAR 2000'
--UNION
--SELECT 6, '123 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 7, '012 Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 8, 'Useless Company', 'Yes', '01 MAR 2010'
--UNION
--SELECT 9, 'More Company', 'Yes', '01 MAR 2010'
select * from tblCompany
DECLARE @CompanyLetter VarChar(5)
SET @CompanyLetter = 'U'
SELECT CompanyID,
CompanyName,
Active,
LastModified
FROM tblCompany
WHERE
(@CompanyLetter = 'ALL')
OR
((@CompanyLetter = 'OTHER') AND NOT(CompanyName LIKE '[a-z]%'))
OR
((@CompanyLetter LIKE '[a-z]') AND (CompanyName LIKE @CompanyLetter + '%'))
BrainDonor, very creative
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 25, 2010 at 5:32 am
hi I have the same problem except my case statement should look something like this:
Select distinct staffno
from dtiheadcount dt inner join reportingstructure re
on dt.costcenter = re.costcenter
Where period = 201002
and transfertype = 'transferin'
and headcount In (case when transfertype = 'transferin' then ('Transfercluster','TransferConsolidate'))
please help
March 25, 2010 at 5:36 am
Trybbe (3/25/2010)
hi I have the same problem except my case statement should look something like this:
Select distinct staffnofrom dtiheadcount dt inner join reportingstructure re
on dt.costcenter = re.costcenter
Where period = 201002
and transfertype = 'transferin'
and headcount In (case when transfertype = 'transferin' then ('Transfercluster','TransferConsolidate'))
please help
Start this in another thread, and try to include table creation details and test data, as in:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
BrainDonor
March 25, 2010 at 6:37 am
WHERE
(@CompanyLetter = 'ALL')
OR
((@CompanyLetter = 'OTHER') AND NOT(CompanyName LIKE '[a-z]%'))
OR
((@CompanyLetter LIKE '[a-z]') AND (CompanyName LIKE @CompanyLetter + '%'))
Can you explain how it works ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 25, 2010 at 8:22 am
Bhuvnesh (3/25/2010)
@BrainDonor
WHERE(@CompanyLetter = 'ALL')
OR
((@CompanyLetter = 'OTHER') AND NOT(CompanyName LIKE '[a-z]%'))
OR
((@CompanyLetter LIKE '[a-z]') AND (CompanyName LIKE @CompanyLetter + '%'))
Can you explain how it works ?
Look at how the brackets divide it into three separate conditions - it will only run one of these depending upon the value of @CompanyLetter.
If @CompanyLetter = 'ALL' then it will only execute the first condition, which will result in the entire table being selected because it doesn't have anything else to check against.
If the @CompanyLetter = 'OTHER' then it will satisfy the second condition only, and that will only select rows where the CompanyName does not start with a letter. I could have probably used AND (CompanyName LIKE '[^a-z]%')), but it never occurred to me at the time and I'm too busy to try it now.
If the CompanyName is only one letter ([a-z]) then only the third condition is satisfied that is used as the wildcard.
Try running it with only one of the three conditions enabled to get a better understanding of it. Pick it apart and have a play with it - there isn't a massive amount of code there.
BrainDonor.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply