March 17, 2016 at 9:59 am
Is there a type that I can declare as a variable that works in an IN clause? With it accepting multiple values, I would think it is an array of the declared field type.
DECLARE @FromClaimaint INT
SET @FromClaimaint = 1827522
;
DECLARE @EffectiveDates VARCHAR
SET @EffectiveDates = '06/05/1995'
;
SELECT
[ClaimaintID]
,CONVERT(VARCHAR, [EffectiveDate], 101) AS 'EffectiveDate'
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
FROM
ClaimaintName
WHERE [ClaimaintID] = @FromClaimaint
AND CONVERT(VARCHAR, [EffectiveDate], 101) IN ('06/05/1995')
;
SELECT
[ClaimaintID]
,CONVERT(VARCHAR, [EffectiveDate], 101) AS 'EffectiveDate'
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
FROM
ClaimaintName
WHERE [ClaimaintID] = @FromClaimaint
AND CONVERT(VARCHAR, [EffectiveDate], 101) IN (@EffectiveDates)
March 17, 2016 at 10:16 am
No; but there are two common ways around this. Either resolve out the string to a table of values and join to it, or write the statement as dynamic sql.
Incidentally, this structure
CONVERT(VARCHAR, [EffectiveDate], 101)
will render ineffective any indexes you might have on the column [EffectiveDate]. If the purpose of this is to strip a datetime column of the time component, then cast it to a date - it's one of the very few functions you can use on a column used as a predicate which will not affect SARGability.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2016 at 10:22 am
scott.t.rider (3/17/2016)
Is there a type that I can declare as a variable that works in an IN clause? With it accepting multiple values, I would think it is an array of the declared field type.DECLARE @FromClaimaint INT
SET @FromClaimaint = 1827522
;
DECLARE @EffectiveDates VARCHAR
SET @EffectiveDates = '06/05/1995'
;
SELECT
[ClaimaintID]
,CONVERT(VARCHAR, [EffectiveDate], 101) AS 'EffectiveDate'
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
FROM
ClaimaintName
WHERE [ClaimaintID] = @FromClaimaint
AND CONVERT(VARCHAR, [EffectiveDate], 101) IN ('06/05/1995')
;
SELECT
[ClaimaintID]
,CONVERT(VARCHAR, [EffectiveDate], 101) AS 'EffectiveDate'
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
FROM
ClaimaintName
WHERE [ClaimaintID] = @FromClaimaint
AND CONVERT(VARCHAR, [EffectiveDate], 101) IN (@EffectiveDates)
First, the answer to your question is no there isn't. If you have a comma delimited list of dates in a variable you need to do something like this:
SELECT
[ClaimaintID]
,CONVERT(VARCHAR, [EffectiveDate], 101) AS 'EffectiveDate'
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
FROM
ClaimaintName
WHERE
[ClaimaintID] = @FromClaimaint
AND [EffectiveDate] IN (select Item from dbo.DelimitedSplit8K(@EffectiveDates,','));
This assumes that the dates in the string are in the correct format to be converted to a date or datetime data type.
I dropped the conversion of the EffectiveDate column to a character string in your original query as that prevents SQL Server from using an index that may exist on that column.
You can find the dbo.DelimitedSplit8K function in the resources of the following article: http://www.sqlservercentral.com/Forums/Topic1101315-203-1.aspx
There are other alternatives as well. This is just one option.
March 17, 2016 at 10:27 am
No, there is no type that supports IN with a delimited string
One solution is to convert the dates to rows in a datetable
DECLARE @datetable TABLE(d date NOT NULL)
TODO: invent date array to table conversion a la split
insert into @datetable(d) VALUES('20160101')
insert into @datetable(d) VALUES('20160102')
select * from table where date in (select d from @datetable)
Another hack is to
CREATE TABLE dbo.t1(birthdate date)
INSERT INTO t1(birthdate) VALUES ('20160101')
INSERT INTO t1(birthdate) VALUES ('20160102')
INSERT INTO t1(birthdate) VALUES ('20160103')
DECLARE @List varchar(MAX) = '20160101,20160102'
SELECT * FROM t1 WHERE @List LIKE '%'+CONVERT(varchar(10), birthdate, 112)+'%'
March 17, 2016 at 11:15 am
Thank you all for your quick responses.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply