Building dynamic T-SQL query from a string argument in a sproc

Question

Let's say I have a table which contains a varchar field:

CREATE TABLE [MyTable] (
    [MyId]    varchar(3)    NOT NULL,
    .....
)

The [MyId] column contains sequential alphanum values like A1, A2... A99, B1, B2..B99, C1 and so on (up to Z99).

What I'd like to do is to extract rows from the table whose MyId field matches some specific prefixes... e.g. I'd like to fetch rows from the series A, C, P and X.

And I'd like to this with a sproc which will dynamically construct the query based on the prefix alphabets supplied in the argument.

I'm thinking about something like this...

CREATE PROCEDURE [dbo].[uspFilterMyTable]
  @prefixArray varchar(max)
AS
  ... -- split individual characters from @prefixArray into an array

  SELECT * FROM [MyTable] 
  WHERE 
    [MyId] LIKE ....
    OR
    [MyId] LIKE ....  -- iterate all characters from @prefixArray

I think the main bulk of the stored procedure will resemble the following pseudo-code:

DECLARE @sql nvarchar(max)
-- iterate through all the characters
SET @sql = 'SELECT * FROM [MyTable] WHERE [MyId] LIKE ' + @charInTheArray + '%'
SET @sql = @sql + ' OR [MyId] LIKE ' + @nextCharInArray + '%'


EXEC (@sql)

The above proecedure will be called like this:

EXEC uspFilterMyTable("A,C,P,X")

... or perhaps like this (if it makes splitting the alphabets easier):

EXEC uspFilterMyTable("ACPX")

Any ideas? Pointers?

Update: OK, this is what I've come up with ([Split] function borrowed from Chhatrapati Sharma):

-- [MyTable] contains these rows: 'A7', 'A87', 'B16', 'C51', 'H99', 'X12'

-- the "input" parameter
DECLARE @prefixArray NVARCHAR(100)= 'H,A,C'

-- split the string into SQL wild-card patterns
DECLARE charCursor CURSOR FOR
    select items + N'%' from dbo.Split(@prefixArray, ',')


OPEN charCursor;
DECLARE @pattern CHAR(2)

-- create temp table if necessary
IF NOT EXISTS(SELECT * FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '#tmpTable%')
    CREATE TABLE #tmpTable ([Id] VARCHAR(3) NOT NULL)

-- purge old data
DELETE FROM #tmpTable

FETCH NEXT FROM charCursor into @pattern
WHILE @@FETCH_STATUS = 0
BEGIN
      --SELECT * INTO #tmpTable FROM [MyTable] WHERE [MyId] LIKE @pattern   
      Insert Into #tmpTable Select * FROM [MyTable] WHERE [MyId] LIKE @pattern  
    FETCH NEXT FROM charCursor into @pattern
END

CLOSE charCursor;
DEALLOCATE charCursor;

-- return the values
SELECT * FROM #tmpTable

It's ugly I know, but it works... any tips to improvise the code?

Wich RDBMS are you using, MS SQL Server, MySQL, Oracle, Postgre? — Yaroslav, Sep 20 '12 at 06:47
I am actually using EF - but I'd like to keep the business logic on the server-side...this procedure will return read-only rows and will be called a lot — invarbrass, Sep 20 '12 at 06:48
`tsql` and `sql-server`have different tags for a reason. I guess you have heard about [Sybase](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/title.htm) — Yaroslav, Sep 20 '12 at 06:54
I'd suggest you normalize your data scheme and introduce a separate table for MyId and give it a FK to MyTable. This way things become much more easy to solve. Additionally you get data integrity and performance benefits. Some reason to not go the usual route ? You say that this will be called a lot. Note that when you make your SQL dynamically SQL execution plans cannot be used. You are crippling SQL server built in performance features. — pero, Sep 20 '12 at 07:01
@PetarRepac: Thanks for the inputs! The original tables do have proper indices and are normalized (the fictitious example table I showed here doesn't have any of those) I agree, using stored procedure here may not have much performance benefit - as each dynamically constructed query will override the execution planner... I'm basically fishing for some fresh/new ideas off the learned crowd... ;) — invarbrass, Sep 20 '12 at 07:09

Chhatrapati Sharma · Answer 1 · 2012-09-20T06:59:31.530

first you should create below function and then use this in query like this

SELECT * FROM [MyTable] WHERE  [MyId] in (select items from dbo.split(@prefixArray,','))



CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))         
returns @temptable TABLE (items varchar(8000))         
as         
begin         
    declare @idx int         
    declare @slice varchar(8000)         

    select @idx = 1         
    if len(@String)<1 or @String is null  return         

    while @idx!= 0         
     begin         
      set @idx = charindex(@Delimiter,@String)         
      if @idx!=0         
      set @slice = left(@String,@idx - 1)         
     else         
      set @slice = @String         

      if(len(@slice)>0)    
       insert into @temptable(Items) values(@slice)         

       set @String = right(@String,len(@String) - @idx)         
       if len(@String) = 0 break         
       end     
    return         
    end

score 2 · Answer 2 · answered Sep 20 '12 at 07:03

Here you have a nice and fast split method based on XML:

DECLARE @str NVARCHAR(100)= 'A1,B3,C4,B12,K19', @separator VARCHAR(1)= ','
DECLARE @SplitedList  TABLE (code NVARCHAR(30))

DECLARE @XMLList XML
SET @XMLList=CAST('<i>'+REPLACE(@str, @separator,'</i><i>')+'</i>' AS XML)

INSERT INTO @SplitedList
SELECT x.i.value('(./text())[1]','varchar(100)')
FROM @XMLList.nodes('i') x(i)

SELECT * FROM @SplitedList

Result will be a table with the splitted values:

code
A1
B3
C4
B12
K19

From here you can continue and use this table on your procedure and join with you original table using LIKE as you propossed.

score 1 · Answer 3 · edited May 23 '17 at 12:03

I would have suggested you to use table valued parameters to call your stored procedure. I guess you call it from .net. But EF I think will not be able to handle it, though you might check it. If not, I think the best way is to first parse the string into a temporary table, or a table value and after that join with it.

With TVP:

CREATE PROCEDURE [dbo].[uspFilterMyTable]
  @prefixArray tvp_idlist readonly
as
  select 
   t.* 
  from MyTable t
  join @prefixArray pa on pa.id = t.myid

With a split function (of your choosing, you find many examples on the net)

CREATE PROCEDURE [dbo].[uspFilterMyTable]
  @prefixArray varchar(max)
as

  create @prefixArray tvp_idlist
  insert into @prefixArray (id)
  select id from dbo.myCustomSplit(@prefixArray,',')


  select 
   t.* 
  from MyTable t
  join @prefixArray pa on pa.id = t.myid

Where for both cases @prefixArray is a table variable is Id = varchar(3)

As an edit, after a little digging, it seems that with a little work EF works fine with TVPs. Check this : Entity Framework Stored Procedure Table Value Parameter. So The best thing is to send directly a table to your stored procedure, then to send a string to parse.

Building dynamic T-SQL query from a string argument in a sproc

3 Answers3