Databases
StackOverflow2013
Postsdbo
POBuilding dynamic T-SQL query from a string argument in a sproc
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBuilding dynamic T-SQL query from a string argument in a sproc
    primarykey
    data
    text
    <p>Let's say I have a table which contains a varchar field:</p> <pre><code>CREATE TABLE [MyTable] ( [MyId] varchar(3) NOT NULL, ..... ) </code></pre> <p>The <strong>[MyId]</strong> column contains sequential alphanum values like A1, A2... A99, B1, B2..B99, C1 and so on (up to Z99).</p> <p>What I'd like to do is to extract rows from the table whose <strong>MyId</strong> field matches some specific prefixes... e.g. I'd like to fetch rows from the series A, C, P and X. </p> <p>And I'd like to this with a sproc which will dynamically construct the query based on the prefix alphabets supplied in the argument.</p> <p>I'm thinking about something like this...</p> <pre><code>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 </code></pre> <p>I think the main bulk of the stored procedure will resemble the following pseudo-code:</p> <pre><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) </code></pre> <p>The above proecedure will be called like this:</p> <pre><code>EXEC uspFilterMyTable("A,C,P,X") </code></pre> <p>... or perhaps like this (if it makes splitting the alphabets easier):</p> <pre><code>EXEC uspFilterMyTable("ACPX") </code></pre> <p>Any ideas? Pointers?</p> <hr> <p><strong>Update:</strong> OK, this is what I've come up with ([Split] function borrowed from Chhatrapati Sharma):</p> <pre><code>-- [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 </code></pre> <p>It's ugly I know, but it works... any tips to improvise the code?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USinvarbrass
    1. USinvarbrass
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBuilding dynamic T-SQL query from a string argument in a sproc
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow do you use it ? Where are you calling it from ?
      singulars
      1. POBuilding dynamic T-SQL query from a string argument in a sproc
      1. USDumitrescu Bogdan
    2. COWich RDBMS are you using, MS SQL Server, MySQL, Oracle, Postgre?
      singulars
      1. POBuilding dynamic T-SQL query from a string argument in a sproc
      1. USYaroslav
    3. COI 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
      singulars
      1. POBuilding dynamic T-SQL query from a string argument in a sproc
      1. USinvarbrass
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload