SmithiM SmithiM - 6 months ago 8
SQL Question

SQL - parametrized procedure with multiple parameters as array

I have very simple procedure:

CREATE PROCEDURE [Report]
@statusValue varchar(200) = '%'
AS
BEGIN
SELECT * FROM SomeTable
WHERE Something LIKE UPPER(@statusValue)
END


I'd like to provide user set multiple statusValue. Because there is 6 levels of statusValue in my table, I'd like to provide user to define required statusValue into procedure parameters - something like array.

I don't know, how it exactly works - I'm very new in this area - but I'm supposing to have procedure like this one:

EXEC Report @statusValue = 'statusValue1|statusValue2|statusValue3'


Do you happen to know, how can I adjust my procedure to have required output. Many thanks in forward.

Answer

Use following user defined function to return values from delimited string (say pipe):

CREATE FUNCTION [dbo].[stringlist_to_table]
    (@list      varchar(8000),
    @delimiter nchar(1) = N',') 
RETURNS @tbl TABLE (value varchar(8000)) AS
BEGIN
    DECLARE @pos      int,
        @tmpstr   varchar(8000),
        @tmpval   varchar(8000);

    SET @tmpstr = @list;
    SET @pos = charindex(@delimiter , @tmpstr);

    WHILE @pos > 0
    BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (value) VALUES(@tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
    END

    INSERT @tbl(value) VALUES (ltrim(rtrim(@tmpstr)));
    RETURN
END

Now use the following procedure to get the required output:

CREATE PROCEDURE [Report]
    @statusValue varchar(200) = '%'
AS
BEGIN

    DECLARE @iterator INT = 1;
    DECLARE @total INT = 1;
    DECLARE @keyword VARCHAR(100) = '';

    SELECT ROW_NUMBER() OVER (ORDER BY value) SNo, value keyword
    INTO #temp
    FROM dbo.stringlist_to_table(@statusValue, '|')

    SELECT * 
    INTO #output
    FROM SomeTable
    WHERE 1 = 0;

    SELECT @total = MAX(SNo), @iterator = MIN(Sno)
    FROM #temp

    WHILE (@iterator <= @total)
    BEGIN
        SELECT @keyword = '%' + keyword + '%'
        FROM #temp
        WHERE SNo = @iterator;

        INSERT INTO #output
        SELECT * 
        FROM SomeTable
        WHERE Something LIKE @keyword

        SET @iterator = @iterator + 1;
    END

    SELECT *
    FROM #output;

    DROP TABLE #output, #temp;
END