October 15, 2013 at 6:14 am
I am trying to add this query in data set. Basically we are using cascading parameters
SELECT DISTINCT C.COMPANY_SK,
C.COMPANY_DESC FROM DIVISIONMAPPING D
INNER JOIN VW_DIM_COMPANY C ON C.COMPANYID=D.COMPANYID
WHERE D.ACTIVE IN ('YES','NO') AND
(D.BUSINESSDIVISION in @Division OR @Division in ( 'All'))
AND D.EXCLUDEFLAG=0
ORDER BY COMPANY_DESC ASC
please correct me regarding this issue
October 15, 2013 at 7:53 am
IF @Division = 'All'
BEGIN;
SELECT DISTINCT C.COMPANY_SK, C.COMPANY_DESC
FROM DIVISIONMAPPING D
INNER JOIN VW_DIM_COMPANY C ON C.COMPANYID = D.COMPANYID
WHERE D.ACTIVE IN ('YES', 'NO')
AND D.EXCLUDEFLAG = 0
ORDER BY COMPANY_DESC ASC;
END;
ELSE BEGIN;
DECLARE @ITEMS AS TABLE (ItemNumber INT, Item VARCHAR(50));
INSERT INTO @ITEMS
SELECT ItemNumber, Item
FROM dbo.[DelimitedSplit8K](@Division,',');
SELECT DISTINCT C.COMPANY_SK, C.COMPANY_DESC
FROM DIVISIONMAPPING D
INNER JOIN VW_DIM_COMPANY C ON C.COMPANYID = D.COMPANYID
WHERE D.ACTIVE IN ('YES', 'NO')
AND D.BUSINESSDIVISION IN (SELECT Item FROM @ITEMS)
AND D.EXCLUDEFLAG = 0
ORDER BY COMPANY_DESC ASC;
END;
Requires the 8K Splitter (http://www.sqlservercentral.com/articles/Tally+Table/72993/): -[/url]
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy