Pass List of strings to a stored procedure

Question

Using, SQL Server 2012, I would like to create a stored procedure, that passes in a list of string and checks it for each entry.Iv added the list to one comma separated string 'UserGroupsAllowedToViewMap'. This was working for one entry but I need to check it for a number of entries.

public DataTable GetMapsWithWorkspaceForUserGroups(int workspaceID, string UserGroupsAllowedToViewMap)
{
        DataTable mapDets = new DataTable();

        SqlCommand oComm = new SqlCommand();
        SqlParameter spParam_WrkSpaceId = new SqlParameter();
        SqlParameter spParam_ViewMap = new SqlParameter();
        SqlParameter[] spParams = new SqlParameter[2];

        SqlDataAdapter daUserMaps = new SqlDataAdapter();
        try
        {
            spParam_WrkSpaceId.ParameterName = "@workspaceID";
            spParam_WrkSpaceId.Value = workspaceID;
            spParams[0] = spParam_WrkSpaceId;

            spParam_ViewMap.ParameterName = "@ViewMap";
            spParam_ViewMap.Value = UserGroupsAllowedToViewMap;
            spParams[1] = spParam_ViewMap;

            oComm = CreateCommand("GetWorkspaceMapDetailsForUserByGroups", spParams, TypeOfConnectionString.GeoAppBuilder);
            daUserMaps.SelectCommand = oComm;
            daUserMaps.Fill(mapDets);
        }
        catch (Exception e)
        {
            throw (e);
        }
        finally
        {
            CloseConnection();
        }

        return mapDets;
}




  USE [App]
GO
/****** Object:  StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]    Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]   
    @workspaceID int,
    @viewMap nvarchar(256)
 AS 

SELECT 
  m.*
FROM 
  GeoAppMapDef m
WHERE
 m.workspaceID = @workspaceID
 and m.IsDeleted = 0
 and m.ViewMap = @viewMap

I am unsure how to iterate through the string in SQL. Ive looked at Passing List<> to SQL Stored Procedure and C# SQL Server - Passing a list to a stored procedure but still none the wiser. any help appreciated.

Don't pass a string. It's never a good idea. You can pass a table-valued parameter with the values you want and treat it as a table inside your stored procedure. In fact, both of the links you posted propose exactly this. Did you have a problem implementing them? If so, you need to find a work around for this, not throw away the good solution and go back to the ... not exactly good one. — Panagiotis Kanavos, Feb 16 '15 at 12:12

Panagiotis Kanavos · Answer 1 · 2015-02-16T12:30:49.817

This is really a duplicate of the links you posted. Instead of trying to parse a list of values, pass a table-valued parameter.

First create the parameter's type in the database (only once).

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] int NULL
);

Then create a procedure that accepts this parameter:

CREATE PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
    @workspaceID int,
    @groupIds IdList READONLY
AS
BEGIN
SELECT 
  m.*
FROM GeoAppMapDef m 
    inner join @groupIds on m.ViewMap=@groupIds.Id
WHERE
    m.workspaceID = @workspaceID
    and m.IsDeleted = 0
END

On the client's side, create a DataTable with a single int-typed column called Id, fill it with the IDs you want then use it as the value of the @groupIds parameter

var table = new DataTable();
table.Columns.Add("Id", typeof(int));

for (int i = 0; i < 10; i++)
    table.Rows.Add(i);

var pList = new SqlParameter("@groupIds", SqlDbType.Structured);
pList.TypeName = "dbo.IdList";
pList.Value = table;

I've copied this from the duplicate question with a few modifications.

thanks for the reply but went the other route. Also the table was STRING not ID — John, Feb 17 '15 at 09:11
Why? You never mentioned what's wrong with the optimal solution (or the table schema). If you insist on using workarounds with strings though, at least pass an XML-typed value instead of converting and splitting in T-SQL. — Panagiotis Kanavos, Feb 17 '15 at 09:17

Saravana Kumar · Accepted Answer · 2015-02-16T12:38:29.067

2

Convert the comma seperated value to table using the XML. Use this updated procedure.

USE [App]
GO
/****** Object:  StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]    
     Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]   
    @workspaceID int,
    @viewMap nvarchar(256)
 AS 

SELECT 
  m.*
FROM 
  GeoAppMapDef m
WHERE
 m.workspaceID = @workspaceID
 and m.IsDeleted = 0
 and m.ViewMap IN 
 (
  SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
  FROM  
  (
    SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS 
  ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)

edited Feb 16 '15 at 12:38

answered Feb 16 '15 at 12:14

Saravana Kumar

3,669
5
15
35

4

Why do that when SQL Server already supports table valued parameters? Not to mention, there are easier ways to split strings than going through XML. Even if you did, it would be better to use an XML-typed parameter and avoid the overhead of parsing and casting. – Panagiotis Kanavos Feb 16 '15 at 12:17
@kumar thanks for the reply, this flags error Msg 2209, Level 16, State 1, Procedure GetWorkspaceMapDetailsForUserByGroups, Line 19 XQuery [A.CVS.value()]: Syntax error near ',' I cant see why – John Feb 16 '15 at 12:34
@John: i have updated the result.check it and update the status. – Saravana Kumar Feb 16 '15 at 12:39
@Kumar cheers for the help. worked great! – John Feb 17 '15 at 09:09
1

You should only use xml extensions to split a string if you are **100% sure** it will not contain any xml characters, if, for example, you try and split `">,<,1,2,3"` you will get an error. You should however still use the other answer, this is a workaround at best, whereas the correct solution is to use table valued parameters. You should read [this article](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) and the articles referenced within it. – GarethD Feb 17 '15 at 22:20

Pass List of strings to a stored procedure

2 Answers2

Linked