A challenge I was tasked with recently was providing an SSRS report, where by a user could choose to exclude rows from a table if a particular quantity column contained a zero value.
Originally this coloured me perplexed, until I got my head around the logic required in my where clause which feeds into the data source of my SSRS report.
So for my own future reference and on the off chance any one else faces a similar problem, here is a snippet of the code I used:
WHERE ((@ExcludePur = 1 AND PurchasedYTD <> 0) OR (@ExcludePur = 0) )
AND ((@ExcludeSold = 1 AND SoldYTD <> 0) OR (@ExcludeSold = 0) )
AND ((@ExcludeUsed = 1 AND UsedYTD <> 0) OR (@ExcludeUsed = 0) )
ORDER BY ItemNumber
All three parameters have to passed a value, and are not optional, so in this case we can use an “AND” clause to compare all three filters, and then use an “OR sub clause to exclude zero value records if the relevant parameter =1. Otherwise if the parameter = 0 we’ll not limit the result set.
(Apologies for the dodgy formatting, I am still finding my feet with my new blog writing tool)
Such a simple task in hindsight, but if you’re scratching your head about how to remove non-numeric characters from a string, this function should help you.
The function uses the in built STUFF and PATINDEX functions to identify a characters which aren’t numeric (0-9) and replace each non-numeric character with an empty string (‘’).
1: CREATE Function [xFnRemoveNonNumericCharacters](@strText VARCHAR(1000))
2: RETURNS VARCHAR(1000)
5: WHILE PATINDEX('%[^0-9]%', @strText) > 0
7: SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
9: RETURN @strText
Something i’ve come across recently is the number of developers out there who don’t know about one of my favourite SQL Server management studio(SSMS) shortcuts. The ALT+Shift command to select an area of onscreen real estate.
This shortcut allows you to select a cross section of lines and rows in in SSMS and add/remove text. This is very good when you want to add something simple like a comma, or a table alias to the beginning of a number of columns. In the example below, we’ll simply add the typename function to a couple of ID’s, in reality this isn’t going to save much time but just imagine the scenarios were you’ve repetitively copied and pasted the same 2-3 letters, line after line.
My starting point:
From here I want to position my cursor between the comma and the ‘S’ on line three. Next I simply hold ALT+Shift and then press down.
After doing this I can simply start typing, and should see my typed letters appearing on both lines:
Once that is done, I simply need to go to the end of each line and add my closing bracket.
Finally, in the last screenshot I am simply showing how I can select a range of characters over multiple lines and either delete that which is constraint to my select box or replace it with a set of alternative values.
Working with application developers is a somewhat new experience for me, and recently two of us were tasked with documenting and understanding a new (to us) application database.
One of the first steps we looked to identify were which columns in a table were actually used. The qualifying criteria for this was simply any column which had more than one distinct value in it. Now, as we had numerous tables, some with 100+ columns, we decided we wanted to do this programmatically, and not spend hours/days sifting through the objects manually.
The below script is what I have written to complete such a task:
1: DECLARE @MyTable nvarchar(50) = 'SALES_ORDER_INVOICE_ITEM'
2: DECLARE @ObjectID nvarchar(30) = (SELECT Object_ID FROM sys.objects WHERE name = @MyTable AND Type_Desc ='USER_TABLE')
3: DECLARE @Statement nvarchar(max)
5: SET @Statement = (
7: ' WITH CTE AS
8: (SELECT '+STUFF(
9: (SELECT ', ' + 'COUNT(DISTINCT('+CASE WHEN SYSTEM_TYPE_ID =35 THEN 'CAST('+Name+' as varchar(4000))' ELSE Name END+')) AS '+Name FROM sys.columns t1 WHERE t1.object_id = t2.object_id FOR XML PATH('') ) ,1,1,'')+
10: ' FROM '+object_name(@ObjectID)+'
12: SELECT '''+@MyTable+'''as tbl
15: ,CASE WHEN type_name(C.system_type_ID) IN (''NVARCHAR'',''NCHAR'') THEN type_name(C.system_type_ID)+''(''+cast(max_length/2 as varchar)+'')''
16: WHEN type_name(C.system_type_ID) IN (''VARCHAR'',''CHAR'') THEN type_name(C.system_type_ID)+''(''+cast(max_length as varchar)+'')''
17: WHEN type_name(C.system_type_ID) = ''DECIMAL'' THEN type_name(C.system_type_ID)+''(''+cast(precision as varchar)+'',''+cast(scale as varchar)+'')''
18: ELSE type_name(C.system_type_ID)
19: END as Typ
20: FROM CTE
21: UNPIVOT (cnt for col in ('+STUFF((SELECT ', '+Name FROM sys.columns t1 WHERE t1.object_id = t2.object_id FOR XML PATH('') ) ,1,1,'')+')) as X
22: INNER JOIN sys.columns c
23: on x.col = c.name
24: and c.object_id = '+@ObjectID+'
25: WHERE Cnt >1' AS Txt
26: FROM sys.columns t2
27: WHERE object_id = @ObjectID
28: GROUP BY object_id
30: --PRINT @Statement
31: exec sp_executesql @Statement
The results for the example above are shown below, tbl being the table name, col being the column name, cnt being the number of distinct values that field contains and typ being the data type of the column.
As we look to develop this in the future, we will invariably look to add additional information such as primary/foreign keys and constraints. But for now, this has saved us a lot of time on our upfront documentation processes.
Another new one for me, so another one I decided to write a quick little blog about.
I created a fairly simple report the other day, which has a value column, which the report owner wanted formatting to two decimal places. However when the test user was exporting the file to excel they received the following error message:
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
Not only that, but on zero value cells in my value column, the zeros were coming through as “0.000000000000000000”
A quick Google search turned up the following link:
So in my case the problem occurred when a view, my source stored proc was referencing,, was handling a calculated column as decimal (38,16). The workaround for me was to simply cast the column as (35,13), as testing verified this would not affect my end results.
If you genuinely need to have more numerical columns with a precision higher than 14, then I can only recommend you upvote the bug reported in the link above.
Another day another “how have I never come across this problem before”. So todays challenge is to essentially execute a stored procedure which will allow us to alternate a BIT value. So if the value is 0 make it 1, if it is 1 make it 0.
After a little goggling it seems utilising the Bitwise XOR operator is the best method.
1: DECLARE @Bit BIT= 1
2: SELECT CAST(@Bit^1 AS bit)
While i’m not the best person to preach about the benefits of using this operator it got the job done in this instance, for more information start here:
Here’s a fun one I came across this week, we had what was supposed to be a relatively simple update statement taking forever to execute. The update was to an application database on another server, and the table had about 300k rows in it. We had two very similar update statements and only one of them was causing us any grief.
As mentioned, we were updating an application database, so used a cursor to lock the rows 1 at a time.
1: DECLARE @co_num NVARCHAR(10)
2: DECLARE @OrderOutcome NVARCHAR(50)
4: DECLARE CurOrders CURSOR FORWARD_ONLY READ_ONLY FOR
5: SELECT co_num, LEFT(OO.OrderOutcome,40)
6: FROM [SERVER01].DATABASE01.dbo.co co
7: INNER JOIN OrderOutcome OO ON OO.OrderNo = co.ordno
9: OPEN CurOrders
10: FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome
12: WHILE @@FETCH_STATUS = 0
15: UPDATE [SERVER01]. DATABASE01.dbo.co
16: SET Outcome = @OrderOutcome
17: WHERE co_num = @co_num
19: FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome
23: CLOSE CurOrders
24: DEALLOCATE CurOrders
The original execution plan looked as follows:
So you can see the query is doing a full table scan of the remote table (the table we want to update).
Why is it doing this? Well after much head scratching we finally identified the difference between our working query and this troublesome query. Our @OrderOutcome parameter is of length 50, where as the data being entered into it is limited to 40 characters. The fix, update the @OrderOutcome parameter to 40 characters…
The resulting execution plan, now looks like this:
I can’t explain why this happens, but it looks to have been a problem ever since SQL 2005, and when you have 2,000 updates to do, doing a table scan for each of those can be quite tedious, but now you know. Make your parameters the same length as the longest data going into it.