Handling multi value parameters from SSRS in a stored procedure

Actually quite easy, using this function :

CREATE FUNCTION [dbo].[fn_SplitIN]
/* This function is used to split up multi-value parameters */
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
/* ========================================================================
fn_SplitIN

Function to return a table for joining for a multi valued parameter from SSRS

How to use :

SELECT cols
FROM mytab
WHERE cols IN (SELECT Item FROM fn_SplitIN(@multiValueParam,','))

===========================================================================
Version Date Author Comment
------- ---- ------ -------
v0.00 23/03/2009 Brian Jones Initial Version
=========================================================================== */

RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)

SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ',@delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END

RETURN
END

Conditional Where Clauses

Excellect article by Erland Sommarskog about the various ways of doing SQL conditional where clauses. What sets this apart is that Erland has comparative performance figures, showing what way is best. Also kept up to date.

Change server collation

Changing SQL 2005 collation does not require a total re-install. Instead the following can be done :

1. Backup all user databases
2. Drop user databases
3. Make sure security is SQL Server
4. Rebuild the master database as follows :

cd "\Program Files\Microsoft SQL Server\90\Setup Bootstrap"

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AS

More details are here

Lock Down SQL 2005

Nice website detailing whats needed to lock down a SQL Server 2005 implementation

TFS Sharing workspace between multiple users

This requirement is for a build machine where several developers (who develop with TFS on their own PC) want to build and test on the final hardware. IT would not allow a single AD login, and TFS will not allow a workspace to be used by different users on the same PC.

However, this can be done by fooling TFS into thinking that the physical workspace is a different path per user. By using the DOS command SUBST to assign a different drive letter for each user to the same path, TFS will allow the files to be shared.

This is OK for this scenario where only get latest was every used - I'm not sure if it would be any good if files are checked out.

SQL Server Versions

A rather useful site with all the SQL Server versions and hotfixes. For a more detailed (though less readable) list look here.

Dynamic PIVOT table

SQL 2005 Pivot command is great if you have a fixed column result set. However, if the columns are data derived then it is unfortunately not possible to use a SELECT in the PIVOT IN clause. The following overcomes this shortfall :

--==============================
-- Create pivot "columns"
--==============================

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.Name
FROM ColumnNamesTable AS t2
ORDER BY '],[' + t2.Name
FOR XML PATH('')
), 1, 2, '') + ']'

--==============================
-- Create SQL for Pivot table
--==============================
SET @sqlStmt = N'SELECT MainColumn, ' + @Cols +
' FROM ResultsTable' +
' PIVOT'+
' (SUM(Value) FOR ColumnName IN (' + @cols + ')) AS PivotedResults' +
' ORDER BY MainColumn'

EXEC sp_executeSql @sqlStmt