November 3, 2014 at 5:49 am
How can I use a variable with
AND NiveauZPT IN ('1','2','2+','3','4','5')
I would like to edit the strings in one place, at the beginning of a storedprocedure.
Cheers,
Julian
-- FlexUren
INSERT INTO @T (JAAR, WEEK, AfdelingZPT, FlexUren)
(
SELECTDBO.ISOyear(Begindatum) Jaar, datepart(ISO_WEEK,Begindatum) [Week], AfdelingZPT, sum(Hrs) FlexUren
FROM DRPHrsManual
WHERE LocatieCode = @IDHuis
and AfdelingZPT = @IDAfd
AND DBO.ISOyear(Begindatum) = @Jaar
AND NiveauZPT IN ('1','2','2+','3','4','5')
and DienstGroep = 'flexpool'
GROUP BY DBO.ISOyear(Begindatum), datepart(ISO_WEEK,Begindatum), AfdelingZPT
);
November 3, 2014 at 6:08 am
If you want to create a single variable to hold a delimited list of values you want to query for, you can treat that as a table instead of a string value list. An example of a single variable would look like this:
DECLARE @strList Varchar(255) = '1,2,2+,3,4,5';
Then, in your query, you can use Jeff Moden's DelimitedSplit8K function to return your delimited list as a table.
If you aren't familiar with the function, the article is at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It is well worth the time you spend to get familiar with it.
INSERT INTO @T (JAAR, WEEK, AfdelingZPT, FlexUren)
(
SELECTDBO.ISOyear(Begindatum) Jaar, datepart(ISO_WEEK,Begindatum) [Week], AfdelingZPT, sum(Hrs) FlexUren
FROM DRPHrsManual
WHERE LocatieCode = @IDHuis
and AfdelingZPT = @IDAfd
AND DBO.ISOyear(Begindatum) = @Jaar
AND NiveauZPT IN (select item from dbo.DelimitedSplit8K(@strList, ',')
and DienstGroep = 'flexpool'
GROUP BY DBO.ISOyear(Begindatum), datepart(ISO_WEEK,Begindatum), AfdelingZPT
);
November 3, 2014 at 6:19 am
Thank you!
Cheers,
J.
November 3, 2014 at 7:44 am
No problem. Glad I could help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply