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 = '')
6: docType in (@docTypeIds)
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'
4: declare @tempTable table
6: docType tinyint
9: while charindex(',',@testString) > 0
11: INSERT INTO @tempTable select substring(@testString,1,1)
12: SET @testString = substring(@testString,3,len(@testString))
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:
2: (@docTypeIds = '0' OR @docTypeIds = '')
4: d.docType in ( select docType from @tempTable )
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.
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
6: set @docTypeIds = '11,1,3,54'
8: -- If docTypes sent in, then convert to table rows
9: IF LEN(@docTypeIds) > 0 AND @docTypeIds <> '0'
11: -- Get Delimiter index
12: SET @delimIndex = CHARINDEX(',',@docTypeIds)
13: -- We have at least one delimiter, start on it
14: WHILE @delimIndex > 0
16: INSERT INTO @docTypes SELECT SUBSTRING(@docTypeIds,1,@delimIndex-1)
17: SET @docTypeIds = SUBSTRING(@docTypeIds,@delimIndex+1,LEN(@docTypeIds))
18: SET @delimIndex = CHARINDEX(',',@docTypeIds)
20: -- Insert the last entry or the first single no delim entry
21: INSERT INTO @docTypes SELECT @docTypeIds
24: select * from @docTypes
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
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!
2: @docType = 0
4: d.docType in (@docType)
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.
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 | firstname.lastname@example.org
23 | email@example.com
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]
DECLARE @listStr VARCHAR(500)
SELECT @listStr = COALESCE(@listStr+',' , '') + cast(cn as varchar)
WHERE emailAddress = @email
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
order by emailAddress
Run that and VOILA! You get a cool way to get your cn column concatenated as a list of values.
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:
Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive
FROM wiki_users as Users
WHERE Users.user_isActive =
SELECT found_rows() AS foundRows
First of all, do you see any problems with this approach?
Second, how would I reproduce this in MSSQL?