How to Parse a Varchar Passed to a Stored Procedure in SQL Server

How to parse a VARCHAR passed to a stored procedure in SQL Server?

Another alternative is to use 'indirection' (as I've always called it)

You can then do..

create proc Sp_ReturnPrdoucts
@BrandIds varchar(500) = '6,7,8'
AS
BEGIN
    if (isnumeric(replace(@BrandIds,',',''))=1) 
    begin
        exec('SELECT * FROM tbl_Products as p join tbl_Brands b on p.ProductBrandId=b.BrandId WHERE b.BrandId IN ('+@BrandIds+')')
    end
END

This way the select statement is built as a string, then executed.

I've now added validation to ensure that the string being passed in is purely numeric (after removing all the commas)

Stored procedure to parse a string

One possible solution is use XML

DECLARE @text VARCHAR(1000) 
        ,@xml xml

SELECT @text = 'City=Hyderabad | Mobile=48629387429 | Role=User | Name =Praveen'

SELECT @text = REPLACE(@text,'|','"')
    ,@text = REPLACE(@text,'=','="')
    ,@text = '<row ' + @text + '"/>'

SELECT @xml = CAST(@text AS XML)

select 
    line.col.value('@Name[1]', 'varchar(100)') AS Name
    ,line.col.value('@City[1]', 'varchar(100)') AS City
    ,line.col.value('@Mobile[1]', 'varchar(100)') AS Mobile 
    ,line.col.value('@Role[1]', 'varchar(100)') AS Role 
FROM @xml.nodes('/row') AS line(col)

Pass List of strings to a stored procedure

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)
)

How to convert varchar to integer in SQL Server stored procedure?

The numbers table solution I posted over on this question will be your most efficient solution. Print bullet before each sentence + new line after each sentence SQL will patch code in once I'm home

Edit

The base unit of work is the inline table-valued function. You might have heard about TVF and how they suck in SQL Server but that relates to the multi-statement types. Inlines are fine as the optimizer can make sense of them and not make terrible plans.

dbo.StringSplit returns a single column (varchar) table with the values split based on the supplied delimiter. You can cut down the lines of code required (derived tables L0 to L5) if you already have numbers table or a fast number generator in your data. I assume you don't. The technique of using a numbers table to split data is not mine but I trust the SQL luminaries who have done the analysis.

You asked for a proc so I have supplied dbo.StringSplitToInts to comply but all it's doing is calling the TVF with the proper parameters. You can extract the select statement and cast into inline code or wherever you need it.

-- This function splits a delimited string with good performance
-- characteristics
CREATE FUNCTION dbo.StringSplit
(
    @input varchar(8000)
,   @delimiter char(1) = ','
)
RETURNS
    table
RETURN
-- L0 to L5 simulate a numbers table
-- http://billfellows.blogspot.com/2009/11/fast-number-generator.html
WITH L0 AS
(
    SELECT
        0 AS C
    UNION ALL
    SELECT
        0
)
, L1 AS
(
    SELECT
        0 AS c
    FROM
        L0 AS A
        CROSS JOIN L0 AS B
)
, L2 AS
(
    SELECT
        0 AS c
    FROM
        L1 AS A
        CROSS JOIN L1 AS B
)
, L3 AS
(
    SELECT
        0 AS c
    FROM
        L2 AS A
        CROSS JOIN L2 AS B
)
, L4 AS
(
    SELECT
        0 AS c
    FROM
        L3 AS A
        CROSS JOIN L3 AS B
)
, L5 AS
(
    SELECT
        0 AS c
    FROM
        L4 AS A
        CROSS JOIN L4 AS B
)
, NUMS AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM
        L5
)
, SOURCE_DATA (ID, content) AS
(
    -- This query simulates your input data
    -- This implementation could be simplified as our function
    -- only accepts 1 row of data but this could be applied to 
    -- any category of problem, not just a single line of input
    SELECT 1, @input 
)
, MAX_LENGTH AS
(
    -- this query is rather important. The current NUMS query generates a 
    -- very large set of numbers but we only need 1 to maximum lenth of our
    -- source data. We can take advantage of a 2008 feature of letting
    -- TOP take a dynamic value
    SELECT TOP (SELECT MAX(LEN(SD.content)) AS max_length FROM SOURCE_DATA SD)
        N.number
    FROM
        NUMS N
)
, MULTI_LINES AS
(
    -- This query will make many lines out a single line based on the supplied delimiter
    -- Need to retain the ID (or some unique value from original data to regroup it
    -- http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
    SELECT 
        SD.ID
    ,   LTRIM(substring(SD.content, Number, charindex(@delimiter, SD.content + @delimiter, Number) - Number)) AS lines
    FROM
        MAX_LENGTH
        CROSS APPLY
            SOURCE_DATA SD
    WHERE
        Number <= len(SD.content)
        AND substring(@delimiter + SD.content, Number, 1) = @delimiter
)
SELECT
    ML.lines
FROM
    MULTI_LINES ML
GO

-- This is overkill as the function is more versatile but
-- in the spirit of delivering what was asked for, this proc
-- calls the function and casts the data to the appropriate type
CREATE PROCEDURE dbo.StringSplitToInts
(
    @input varchar(8000)
,   @delimiter char(1) = ','
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT
        CAST(SS.lines AS int) AS int_tokens
    FROM
        dbo.StringSplit(@input, @delimiter) SS

END
GO

-- Over 9000!
EXECUTE dbo.StringSplitToInts '100,200,300,500,9000'

How to pass a list of strings as a parameter in a stored procedure in SQL?

You will have to use table valued parameters

1. Define new type as follows

   CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );
   

2. then use this type in your stored procedure

    create procedure [dbo].[proc_aggregation]
    @Prod_Code Prod_Code READONLY,
    @Prod_Desc varchar (30)
    as
    ......
   

3. Now before calling the stored procedure fill the table

    declare @PC Prod_Code;
    insert @PC VALUES ('12012'), ('12011'), ('12014')
   

4. Now Call the sp like this

    EXEC dbo.proc_aggregation @PC, @Prod_Desc;
   

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.

Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/

----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

---

Related Topics