Blog

Category Filtering: 'MSSQL'

Remove Filter


SQL splitting a varchar list argument to row of data

Posted by Luis Majano
May 14, 2010 10:35:48 UTC

I got into yet another issue with my SQL optimizations this week at work and one of them was that I needed to pass a list of IDs to a function that would be then used in a SQL statement via an “in” clause.  The problem is that the list argument is a varchar:

   1: @docTypeIds varchar(500) = '0',

 

Then I needed to use it in my SQL statement like this:

   1: SELECT *

   2: FROM table

   3: WHERE (

   4:   (@docTypeIds = '0' OR @docTypeIds = '')

   5:   OR

   6:   docType in (@docTypeIds)  

   7: )

The problem with this approach is that the datatype for the docType column was tinyint and then the argument is a varchar list, so it would fail as it cannot cast it. Bummer!  So what to do?  Well, after some searching and searching, the best idea so far (brought up by our local dev guru Sanjib Panda), was to break the list into a simple table with one column of type tinyint, but how?  Here is the simple logic which could easily be created as a UDF:

   1: declare @testString as varchar(20)

   2: set @testString = '1,2,3,4,5,6,7,8,9'

   3:  

   4: declare @tempTable table

   5: (

   6: docType tinyint

   7: )

   8:  

   9: while charindex(',',@testString) > 0

  10: begin

  11:   INSERT INTO @tempTable select substring(@testString,1,1)

  12:   SET @testString = substring(@testString,3,len(@testString))

  13: end

  14:  

  15: select * from @tempTable

So if you run the above, that will give you our temp table with one docType column with all the values we needed.  Man, sometimes I wish SQL could have some ColdFusion goodness and create a cool function like: listToTable() or something.  Anyways, after splitting the list into a table then I can change my SQL to something like this:

   1: AND(

   2:   (@docTypeIds = '0' OR @docTypeIds = '')

   3:   OR

   4:   d.docType in ( select docType from @tempTable )  

   5: )

There you go! The solution is working really fast and given us a nice little split to rows function that can be used on any variable.  Hope this helps.

 

UPDATE

After much trying out and pains, what I had did not work fully :(  So here is an updated function:

   1: -- Temp tables for lists

   2: DECLARE @docTypes TABLE( docType tinyint )

   3: DECLARE @docTypeIds varchar(300)

   4: DECLARE @delimIndex int

   5:  

   6: set @docTypeIds = '11,1,3,54'

   7:     

   8: -- If docTypes sent in, then convert to table rows

   9: IF LEN(@docTypeIds) > 0 AND @docTypeIds <> '0'

  10: BEGIN

  11:     -- Get Delimiter index

  12:     SET @delimIndex = CHARINDEX(',',@docTypeIds)

  13:     -- We have at least one delimiter, start on it

  14:     WHILE @delimIndex > 0

  15:     BEGIN

  16:         INSERT INTO @docTypes SELECT SUBSTRING(@docTypeIds,1,@delimIndex-1)

  17:         SET @docTypeIds = SUBSTRING(@docTypeIds,@delimIndex+1,LEN(@docTypeIds))

  18:         SET @delimIndex = CHARINDEX(',',@docTypeIds)

  19:     END

  20:     -- Insert the last entry or the first single no delim entry

  21:     INSERT INTO @docTypes SELECT @docTypeIds

  22: END

  23:  

  24: select * from @docTypes

SQL Conditional Where

Posted by Luis Majano
May 13, 2010 17:01:00 UTC

Today I needed to create a conditional where statement in MSSQL and I had no idea how?  It was an incoming variable to a stored procedure and I needed to ignore it in my SQL if the value was 0, if it was not 0 then I needed to do a where statement as a list.  The solution was so simple and elegant I was like WOW!! I like that.

My first thought was to do a if-else split on the SQL with the incoming variable like this:

   1: if @i = 1

   2:     select * from mytable where mycol = 1

   3: else

   4:     select * from mytable where mycol = 2

 

However, this does not hold true to DRY principles, so the solution I ended up using was the following with a simple OR statement!

   1: AND(

   2:   @docType = 0 

   3: OR

   4:   d.docType in (@docType)  

   5: )

A simple OR statement made my life much easier, so there you go, simple conditional statements when you at least expect certain values on incoming variables.

Creating a comma delimited list of column values

Posted by Luis Majano
Apr 02, 2010 00:00:00 UTC
I had a cool little SQL problem yesterday where I had a table with 2 columns: cn, email and for some reason there could be many entries for the same email address:

cn | email

1 | [email protected]

23 | [email protected]

So I needed to get a nice query where I could join all the values of the cn column into a single column for the same email. This was tricky but custom SQL functions to the rescue. This is for MSSQL but you can tweak it for MySQL very easily. So the first thing is to create a function:

CREATE FUNCTION [dbo].[getValueList]

(@email varchar(500))

RETURNS varchar(500)

AS

BEGIN

DECLARE @listStr VARCHAR(500)

SELECT @listStr = COALESCE(@listStr+',' , '') + cast(cn as varchar)

FROM tempTokenMailingList

WHERE emailAddress = @email

RETURN @listStr

END

Once I did this, it will return to me a list of column values based on an incoming email on my target table. You can make this more dynamic but it basically solves my use case. Then on my selection I do this:

select distinct emailAddress, name, token,

( dbo.getValueList(emailAddress) ) CN

from tempTokenMailingList

order by emailAddress

Run that and VOILA! You get a cool way to get your cn column concatenated as a list of values.

Paging in MSSQL 2005-Greater? Help

Posted by Luis Majano
Feb 19, 2009 00:00:00 UTC
I wanted to shoot out on this as I do not use MSSQL 2005 and greater too much and need help on what would be the best and great way to do record paging. I am so used to this in MySQL and you can see a sample of MySQL below:

SELECT SQL_CALC_FOUND_ROWS

Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive

FROM wiki_users as Users

WHERE Users.user_isActive =

LIMIT ,

SELECT found_rows() AS foundRows

First of all, do you see any problems with this approach?

Second, how would I reproduce this in MSSQL?

Site Updates

Archives

Entries Search