Sql Case Statement Pass one value or multiple Values
-
This query works fine as is.
It's limitations are that @what could have only one value passed to it..
However it's the @what parameter that i would like to pass
Values as multiple values separated by a comma i.e. '1st Mortgage Payoff', '2nd Mortgage Payoff'
one idea that I had was if
I set @what to varchar(1000)
then call the What in (@what)
however I think my syntax is not correct..
Thanks, I would really appreciate it if you gurus could suggest something
CREATE PROCEDURE prcRptTrackExceptions
(
@TrackDueDateBegin varchar(20),
@TrackDueDateEnd varchar(20),
@What varchar(50),
@State varchar(2),
@Office varchar(12)
 
AS
BEGIN
SELECT
c.FirmFile,
dbo.Returnvaliddate (b.DuDt) AS DuDt,
a.County,
a.LocWho,
b.What,
a.StateLet,
case
when b.DuDt =0 then 0
else
DateDiff(dd,dbo.Returnvaliddate(b.DuDt),getdate() )
end as Turnaround,
a.SettStat,
b.Checklist
FROM Search a
INNER JOIN TrackItems b ON a.FirmFile = b.FirmFile
INNER JOIN DatesTimes c ON a.FirmFile = c.FirmFile
INNER JOIN MiscText1 d ON a.FirmFile = d.FirmFile
WHERE (b.Stat <> 'Completed')
AND (b.CpBy IS NULL)
AND(
(b.RqDt >= @TrackDueDateBegin) AND (b.RqDt <= @TrackDueDateEnd)
or
(b.DuDt >= @TrackDueDateBegin) AND (b.DuDt <= @TrackDueDateEnd)
 
--***********Trackitem Case*******************************
and b.what in
(case @What when'All' then b.what
else @What
end)
--***************State Case*******************************
and a.StateLet like
(case @State when 'Al' then a.StateLet
when 'AR' then 'AR'
when 'TN' then 'TN'
else @State
end)
--************Office Case*********************************
and d.Office =
(
case @office when 'All' then d.office
else @office
end
)
and b.what in ( '1st Mortgage Payoff', '2nd Mortgage Payoff', '3rd Party Deed Request', 'Pest Inspection', 'Petition to Redeem Taxes', 'Update Title Report', 'Survey')
ORDER BY Turnaround ,b.DuDt, b.FirmFile
END
GO
-
You need something like this :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
-
Write an article about this Remi!!!!
This question gets asked all the time so title it well so it comes up quick on a search. Either that, or I will copy it to a document and beat you at posting it....
(I would also suggest having the user create a Number table outside the SP to speed things up....).
I wasn't born stupid - I had to study.
-
(I would also suggest having the user create a Number table outside the SP to speed things up....).
Did you actually read the thing????
I'll think about posting it
. -
Not close enough, appearantly...
I wasn't born stupid - I had to study.
-
To sum it up I appreciate simplicity ...
-
The final call is really simple :::
Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')
It doesn't get much simpler than this
. -
-
Thanks all !!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply