Small Thing Learned Today

Let's say I have some data regarding the buildings and people who built them - one building obviously can have multiple builders.

BuildingID  BuilderName
----------- ----------------
1           Paul
2           John
3           Bob
1           George
2           Sam
3           Fred
1           Joe
2           Phil

What I need here, is a report in the following format:

BuildingID  builder_list
----------- --------------------------
1           George, Joe, Paul
2           John, Phil, Sam
3           Bob, Fred

I found a very good guide on the problem:

Concatenating row values in Transact-SQL

This is the solution I chose from the suggested ones:

WITH CTE (BuildingID, builder_list, builder_name, length)
 AS(SELECT BuildingID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
    FROM tblBuildings
    GROUP BY BuildingID
       UNION ALL
    SELECT p.BuildingID, CAST(builder_list +
    CASE WHEN length = 0 THEN '' ELSE ', ' END + BuilderName AS VARCHAR(8000) ), 
    CAST(BuilderName AS VARCHAR(8000)), length + 1
    FROM CTE c
    INNER JOIN tblBuilders p
    ON c.BuildingID = p.BuildingID
    WHERE p.BuilderName > c.builder_name)
  
SELECT BuildingID, builder_list
FROM (SELECT BuildingID, builder_list,
 RANK() OVER (PARTITION BY BuildingID ORDER BY length DESC)
FROM CTE) D (BuildingID, builder_list, rank)
WHERE rank = 1;

It looks a bit complex to understand, but does exactly what I need!

by Evgeny. Also posted on my website

Small Thing Learned Today

You can actually assign default values to stored procedure parameters. I suspect I knew that sometime, but totally forgotter. All I need to do is declare them like this

CREATE PROCEDURE [dbo].[prSP_MyStoredProcWithDefaultParameters]
(@someID int,@someParam1 int = -1,@someParam2 int = -1,@someParam3 int = -1,
@someParam4 int = -1,//...@someParam999 int = -1)

Now if I have less than 999 'someParameters', I can still call the stored procedure. I don't really care how many parameters are in the list, as long as there is no more than 999 of them.

List myParams = new List();
List paramList = new List();
// add some values to the list
foreach (int myInt in paramList)
{ 
 if (paramList.IndexOf(myInt) > 998) 
 {
  break;
 }
 else
 {
  myParams.Add(DbManager.CreateInParameter("someParam" +
 (paramList.IndexOf(myInt) + 1).ToString(), DbType.Int32, myInt)); 
 }
}

Well, I guess I have to take care to assign the really important parameters of course.

I can have default values in SQL Server functions too, but, unfortunately I have to specify the keyword 'default' while calling them.

So, if a function is defined as

CREATE FUNCTION [dbo].[fn_MyFunctionWithDefaultParameters](@someID int,
@someParam1 int = -1,@someParam2 int = -1,
@someParam3 int = -1,@someParam4 int = -1,
//...
@someParam999 int = -1)

I need to call it this way:

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3, default, default, 
/* snip a few hundred more*/ 
default)

Still quite useful, but I wish I could call it just like this

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3)

or this

dbo.fn_MyFunctionWithDefaultParameters(1, 2, 3, default, 5, 6)

by Evgeny. Also posted on my website