How to pass a list to a stored procedure

Question

I have the following stored procedure which runs as expected:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[view_proc]
    (@startdatetime DATETIME,
     @enddatetime DATETIME,
     @ids VARCHAR(MAX)
    )
AS
BEGIN
    SELECT *
    FROM
        (SELECT 
             [sr_datetime], [sr_id], [sr_value], [sr_dst], [sr_source]
         FROM 
             [Powerlink].[dbo].[scada_data]
         WHERE  
             sr_id IN ('M001007', 'M001008', 'M001020', 'M001021')
             AND sr_datetime >= @startdatetime
             AND sr_datetime <= @enddatetime) AS SourceTable 
    PIVOT 
        (MAX(sr_value) 
            FOR [sr_id] IN (M001007, M001008, M001020, M001021)
        ) AS PVT
    ORDER BY 
        sr_datetime
END

I run this procedure using the following line:

EXEC [dbo].[view_proc] 
           @startdatetime = '2018-01-01 01:00:00', 
           @enddatetime = '2018-01-01 02:00:00', 
           @ids = 'M001007,M001008,M001020,M001021'

However, currently the procedure doesn't use the @ids parameter to change the query in the same way the @startdatetime and @enddatetime parameters change the query.

My question is, what is the simplest way to modify my stored procedure so that it will accept a list of tags as a parameter which it will use to modify the results of my existing query.

I've looked into defining a custom type to use as a parameter, but I can't figure out what syntax I would have to use to represent the data in the call to the procedure.

Any help would be much appreciated.

cheers,

Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Ken White, Aug 29 '18 at 00:38
Also, the number of sr_id pags that gets passed in can vary from 1 to 100. — James, Aug 29 '18 at 00:41
Specifically, [this answer](https://stackoverflow.com/a/337864/61305). — Aaron Bertrand, Aug 29 '18 at 02:45

score 0 · Answer 1 · answered Aug 29 '18 at 00:49

0

The best way to do it is to use Table-Valued Parameters
In your case it would be something like

CREATE TYPE list_of_ids AS TABLE   -- probably should choose a better name
( id VARCHAR(50) );  
GO 
ALTER PROCEDURE [dbo].[view_proc]
    (
     @startdatetime datetime,
     @enddatetime datetime,
     @ids list_of_ids READONLY 
    )

Just remember that @ids will be read-only but you can use it just like a regular table.
If you have a lot of ids(10000+), I would recommend trying to insert it into a temp table first, possibly index is and then use it in a join or WHERE.

answered Aug 29 '18 at 00:49

Ruslan Tolkachev

634
4
9

Can you give me an example of what the line to call the stored procedure would be? or would it be the same as the example I gave? – James Aug 29 '18 at 00:59
assuming that you inserted values in to a `@ids` type of` list_of_ids` EXEC [dbo].[view_proc] @startdatetime = '2018-01-01 01:00:00', @enddatetime = '2018-01-01 02:00:00', @ids=@ids – Ruslan Tolkachev Aug 29 '18 at 01:05

score 0 · Accepted Answer · answered Aug 30 '18 at 01:58

One of the guys at work gave me the following function which takes a string of comma delimited values and returns a table:

USE [Powerlink]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 30/08/2018 1:49:26         PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
  SELECT 1, 1, CHARINDEX(@sep, @s)
  UNION ALL
  SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
  FROM Pieces
  WHERE stop > 0
)
SELECT pn,
  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)

This allows me to call my stored procedure with the following code:

EXEC [dbo].[view_proc] 
    @startdatetime = '2018-01-02 00:00:00',
    @enddatetime = '2018-01-31 23:59:59',
    @ids = 'M001007,M001008,M001020,M001021'

And here is how it is used in the stored procedure:

USE [Powerlink]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[view_proc]
    (
    @startdatetime varchar(32)= '2018-01-01 01:00:00',
    @enddatetime varchar(32)= '2018-01-01 02:00:00',
    @ids varchar (max) = 'M001007,M001008,M001020,M001021'
    )
    AS

BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

SELECT s AS ID INTO #IDS FROM dbo.Split(',',@ids)

select @cols = STUFF((SELECT ',' + QUOTENAME(ID) 
                FROM #IDS
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 
'SELECT *
FROM
(
    SELECT [sr_datetime]
        ,[sr_id]
        ,[sr_value]
        ,[sr_dst]
        ,[sr_source]
    FROM [Powerlink].[dbo].[scada_data]
    WHERE  sr_id IN (SELECT * FROM #IDS)
    AND sr_datetime >= '''+ @startdatetime +'''
    AND sr_datetime <= '''+ @enddatetime +'''
 ) AS SourceTable 
 PIVOT(
        max(sr_value)
        for [sr_id] in (' + @cols + N')
 ) as PVT ORDER BY sr_datetime'

exec sp_executesql @query;
DROP TABLE #IDS
END

How to pass a list to a stored procedure

2 Answers2