t-sql 2012 with parameter with multiple values

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  August 28, 2018 at 11:35 am

  #362620

  https://i1.social.s-msft.com/Forums/RequestReduceContent/12fc8aee3d461a86ceff6cdf4a6b810d-bed69a07bbb100a480885bf876e4c1a2.png); background-origin: padding-box; background-position-x: -27px; background-position-y: 0px; background-repeat: no-repeat; background-size: auto; border-bottom-color: rgb(118, 118, 118); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(118, 118, 118); border-left-style: none; border-left-width: 0px; border-right-color: rgb(118, 118, 118); border-right-style: none; border-right-width: 0px; border-top-color: rgb(118, 118, 118); border-top-style: none; border-top-width: 0px; font-family: "Segoe UI","Lucida Grande",Verdana,Arial,Helvetica,sans-serif; font-style: normal; font-weight: 400; height: 25px; list-style-type: none; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: invert; outline-style: none; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: none; width: 17px;" alt="Sign in to vote" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001">

  0

  Sign in to vote

  
  

  I have the following t-sql 2012 where I would like to be able to select lots of @calendarID values that are 4 numbers long:

   declare @calendarID varchar(04)= '8214'
   SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
         from Term AS Term WITH (NOLOCK)    
       WHERE (Term.startDate IS NOT NULL)
      AND Term.calendarID in (@calendarID)
   group by     term.name, Term.seq       
      ORDER BY TermName, seq

  The above sql works for one calendarID value but I would like to have up to 20 values entered for @calendarID.

  Thus can you tell me what I can change in the sql to make it work correctly?

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  August 28, 2018 at 12:29 pm

  #2003689

  wendy elizabeth - Tuesday, August 28, 2018 11:35 AM

  I have the following t-sql 2012 where I would like to be able to select lots of @calendarID values that are 4 numbers long:

   declare @calendarID varchar(04)= '8214'
   SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
         from Term AS Term WITH (NOLOCK)    
       WHERE (Term.startDate IS NOT NULL)
      AND Term.calendarID in (@calendarID)
   group by     term.name, Term.seq       
      ORDER BY TermName, seq

  The above sql works for one calendarID value but I would like to have up to 20 values entered for @calendarID.

  Thus can you tell me what I can change in the sql to make it work correctly?

  If you were using SQL Server 2016 or higher  you could use the STRING_SPLIT function. But it's not available in 2012 so you will need to add your own function.
  I use this one:
  -- Splits a string into rows
CREATE FUNCTION [dbo].[chrSplitList]
(
  @list   nvarchar(MAX),
  @separator nvarchar(MAX) = ';'
)
RETURNS @table TABLE (Value nvarchar(4000))
AS BEGIN

   DECLARE @position INT, @previous INT
 SET @list = @list + @separator
 SET @previous = 1
 SET @position = CHARINDEX(@separator, @list)
 WHILE @position > 0 BEGIN
  IF @position - @previous > 0
   INSERT INTO @table VALUES (SUBSTRING(@list, @previous, @position - @previous))
  IF @position >= LEN(@list) BREAK
  SET @previous = @position + LEN(@separator)
  SET @position = CHARINDEX(@separator, @list, @previous)
 END
 RETURN
END
GO
  If you install the function above then the following query should return the results you want.
  declare @calendarID varchar(100)= '8214,8145,8767'
SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
   from Term AS Term
     inner join dbo.chrSplitList(@calendarID,',') X
        on x.Value = Term.calendarID
  WHERE (Term.startDate IS NOT NULL)
group by  term.name, Term.seq  
  ORDER BY TermName, seq

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 28, 2018 at 1:23 pm

  #2003708

  Jonathan AC Roberts - Tuesday, August 28, 2018 12:29 PM

  wendy elizabeth - Tuesday, August 28, 2018 11:35 AM

  I have the following t-sql 2012 where I would like to be able to select lots of @calendarID values that are 4 numbers long:

   declare @calendarID varchar(04)= '8214'
   SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
         from Term AS Term WITH (NOLOCK)    
       WHERE (Term.startDate IS NOT NULL)
      AND Term.calendarID in (@calendarID)
   group by     term.name, Term.seq       
      ORDER BY TermName, seq

  The above sql works for one calendarID value but I would like to have up to 20 values entered for @calendarID.

  Thus can you tell me what I can change in the sql to make it work correctly?

  If you were using SQL Server 2016 or higher  you could use the STRING_SPLIT function. But it's not available in 2012 so you will need to add your own function.
  I use this one:
  -- Splits a string into rows
CREATE FUNCTION [dbo].[chrSplitList]
(
  @list   nvarchar(MAX),
  @separator nvarchar(MAX) = ';'
)
RETURNS @table TABLE (Value nvarchar(4000))
AS BEGIN

   DECLARE @position INT, @previous INT
 SET @list = @list + @separator
 SET @previous = 1
 SET @position = CHARINDEX(@separator, @list)
 WHILE @position > 0 BEGIN
  IF @position - @previous > 0
   INSERT INTO @table VALUES (SUBSTRING(@list, @previous, @position - @previous))
  IF @position >= LEN(@list) BREAK
  SET @previous = @position + LEN(@separator)
  SET @position = CHARINDEX(@separator, @list, @previous)
 END
 RETURN
END
GO
  If you install the function above then the following query should return the results you want.
  declare @calendarID varchar(100)= '8214,8145,8767'
SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
   from Term AS Term
     inner join dbo.chrSplitList(@calendarID,',') X
        on x.Value = Term.calendarID
  WHERE (Term.startDate IS NOT NULL)
group by  term.name, Term.seq  
  ORDER BY TermName, seq

  You've got the right idea, but the fastest string splitter on the planet is a better choice, and can be found
  here:  http://www.sqlservercentral.com/articles/72993/

  Go to the end of the article to find the link.

  Thus the following query:
  DECLARE @calendarID AS varchar(100) = '8214,8145,8767';

  SELECT DISTINCT
    T.name AS TermName,
    T.seq AS Seq
FROM Term AS T
INNER JOIN dbo.Delimited8K_LEAD(@calendarID, ',') AS S
    ON T.calendarID = S.Item
WHERE T.startDate IS NOT NULL
GROUP BY
    T.name,
    T.seq
ORDER BY
    T.Name,
    T.seq;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 29, 2018 at 12:49 am

  #2003762

  For an ad-hoc query, you can just use a table or table variable:
  
Declare @CalendarIDs Table(ID VarChar(4))

  Insert Into @CalendarIDs Values('8403','8404','8405')

  SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
  from Term AS Term
  WHERE (Term.startDate IS NOT NULL) 
  AND Term.calendarID in @CalendarIDs
  group by term.name, Term.seq
  ORDER BY TermName, seq

  If this needs to end up as a parameter to a stored procedure, then look into Table Valued Parameters. And as an aside, don't put the nolock hint on queries unless you entirely understand how horribly wrong it can cause your answers to be, you almost certainly don't need it.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 10, 2018 at 10:48 am

  #2005115

  andycadley - Wednesday, August 29, 2018 12:49 AM

  For an ad-hoc query, you can just use a table or table variable:
  
Declare @CalendarIDs Table(ID VarChar(4))

  Insert Into @CalendarIDs Values('8403','8404','8405')

  SELECT DISTINCT term.name AS TermName, Term.seq AS Seq
  from Term AS Term
  WHERE (Term.startDate IS NOT NULL) 
  AND Term.calendarID in @CalendarIDs
  group by term.name, Term.seq
  ORDER BY TermName, seq

  If this needs to end up as a parameter to a stored procedure, then look into Table Valued Parameters. And as an aside, don't put the nolock hint on queries unless you entirely understand how horribly wrong it can cause your answers to be, you almost certainly don't need it.

  Andy,

  Be aware that table variables are bad news for query estimates, and thus for query plans.   They don't ever get statistics, and are always assumed to have exactly 1 row.   Much better off using a temp table for any data that has more than one row.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  September 10, 2018 at 11:16 am

  #2005121

  As is often the case with SQL Server, it depends. For small numbers of rows, statistics likely won't make any difference and can indeed hurt query performance due to SQL Server's habit of "resurrecting" dropped temp tables along with their statistics, if you run the query a lot with high variance in your parameters.

  I suspect having a long string of Values to parse is liable to cause a performance problem long before a table variable becomes an issue in an ad-hoc query like that (although it helps if you get the syntax correct, unlike in my example!)

  Your milage may vary, performance can go down as well as up, conditions apply etc.... 😉

• Michael L John

  One Orange Chip

  Points: 27044

  More actions

  September 10, 2018 at 11:26 am

  #2005123

  Is this procedure for an SSRS report?  If so, are the values from a multi-select parameter?
  If that;s the case, the values will be passed in as a comma-separated list. 
  '1234,4567,8978'

  You would probably get your best results by using the DelimitedSplit8k function.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 11, 2018 at 6:34 am

  #2005217

  andycadley - Monday, September 10, 2018 11:16 AM

  As is often the case with SQL Server, it depends. For small numbers of rows, statistics likely won't make any difference and can indeed hurt query performance due to SQL Server's habit of "resurrecting" dropped temp tables along with their statistics, if you run the query a lot with high variance in your parameters.

  I suspect having a long string of Values to parse is liable to cause a performance problem long before a table variable becomes an issue in an ad-hoc query like that (although it helps if you get the syntax correct, unlike in my example!)

  Your milage may vary, performance can go down as well as up, conditions apply etc.... 😉

  Yep, your mileage may vary, "it depends", etc., are all certainly a part of my SQL vocabulary.  However, I have to ask the question...   Why take an absolutely unnecessary risk of throwing off the query optimizer's estimates?   It's totally unnecessary, and adds risk.   To me, NOT doing that is in the "no brainer" category.   After all, we all have the occasional syntax issue, and SQL is already challenging enough without adding needless risk to the equation.   As long as we are all still in the "human beings make mistakes" category, I'll stay away from table variables as often as possible.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)