Create A Comma Delimited List From a Column

Create A Comma Delimited List From a Column

Create Sample Data for Both Solutions

-- Suppress data loading messages SET NOCOUNT ON   -- Create Sample Data  CREATE TABLE TeamInfo    (  MemberID    int IDENTITY,       TeamID      int,        FirstName   varchar(50)    )   -- Load Sample Data INSERT INTO TeamInfo VALUES ( 1, 'Jim' ) INSERT INTO TeamInfo VALUES ( 1, 'Mary' ) INSERT INTO TeamInfo VALUES ( 1, 'Bob' ) INSERT INTO TeamInfo VALUES ( 2, 'Sue' ) INSERT INTO TeamInfo VALUES ( 2, 'Ralph' ) INSERT INTO TeamInfo VALUES ( 2, 'Ellen' ) INSERT INTO TeamInfo VALUES ( 3, 'Bill' ) INSERT INTO TeamInfo VALUES ( 3, 'Linda' )

create function -- SQL 2000, Retrieve desired data

-- With SQL 2000, we will create a User Defined Function to do the concatenation. -- While this solution can also be used with SQL Server 2005/SQL Server 2008,  -- the previous suggestion is more efficient.   CREATE FUNCTION dbo.fnMakeTeamList    (  @TeamID int  )    RETURNS varchar(1000) AS    BEGIN       DECLARE @TempTeam table          (  Firstname varchar(20)  )       DECLARE @TeamList varchar(1000)       SET @TeamList = ''       INSERT INTO @TempTeam          SELECT FirstName          FROM TeamInfo          WHERE TeamID = @TeamID       IF @@ROWCOUNT > 0          UPDATE @TempTeam             SET @TeamList = ( @TeamList + FirstName + ', ' )       RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))    END   -- Usage SELECT     TeamID,     MemberList = dbo.fnMakeTeamList( TeamId )  FROM TeamInfo  GROUP BY TeamID   -- Results TeamID     MemberList 1 Jim, Mary, Bob 2 Sue, Ralph, Ellen 3 Bill, LindaS

QL Server 2005 / SQL Server 2008 Solution

--Retrieve desired data SELECT    t1.TeamID,    MemberList = substring((SELECT ( ', ' + FirstName )                            FROM TeamInfo t2                            WHERE t1.TeamID = t2.TeamID                            ORDER BY                                TeamID,                               FirstName                            FOR XML PATH( '' )                           ), 3, 1000 )FROM TeamInfo t1 GROUP BY TeamID   -- Results TeamID     MemberList 1 Bob, Jim, Mary 2 Ellen, Ralph, Sue 3 Bill, Linda

SQL2008: Lock a Stored Procedure for Single Use Only

SQL2008: Lock a Stored Procedure for Single Use Only

Create a Custom Error Message, Designating an User assigned Error Number, starting at 50000, or higher, and a user designated Error Message.

USE Northwind GO   EXECUTE sp_addmessage     @msgnum   = 51001,     @severity = 16,     @msgtext  = N'Resource NOT Available',     @lang     = 'us_english',    @replace  = REPLACE

Create a Stored Procedure that Can be 'locked'

CREATE PROCEDURE dbo.Employees_U_LastName    (  @EmployeeID   int,       @LastName     varchar(20)    ) AS    BEGIN         SET TRANSACTION ISOLATION LEVEL REPEATABLE READ       BEGIN TRANSACTION         DECLARE @LockResult int         EXECUTE @LockResult = sp_getapplock           @Resource    = 'RepeatableRead_TRANSACTION',           @LockMode    = 'Exclusive',          @LockTimeout = 0              IF @LockResult <> 0             BEGIN                ROLLBACK TRANSACTION                RAISERROR ( 51001, 16, 1 )                RETURN             END            -- All code between the use of sp_getapplock above,          -- and sp_releaseapplock below will be restricted to           -- only one user at a time.              -- Ten Second delay for Demonstration Purposes          WAITFOR DELAY '00:00:10'          -- Remove these three lines for 'Normal' use            UPDATE Employees             SET LastName = @LastName             WHERE EmployeeID = @EmployeeID            EXECUTE sp_releaseapplock              @Resource = 'RepeatableRead_TRANSACTION'         COMMIT TRANSACTION      END  Demonstration

Open two separate Query Windows, and place the following code in one of the windows.
DO NOT Execute the code until both Query Windows are in place and connected to the database.

Query Window 1

   EXECUTE dbo.Employees_U_LastName       @EmployeeID = 1,       @LastName   = 'Davolio-Jones'         SELECT           EmployeeID,          LastName,          FirstName       FROM Employees       WHERE EmployeeID = 1 

Place the following code in Query window 2

Query Window 2

   EXECUTE dbo.Employees_U_LastName       @EmployeeID = 1,       @LastName   = 'Davolio'         SELECT           EmployeeID,          LastName,          FirstName       FROM Employees       WHERE EmployeeID = 1 

Execute the code in Query window 1, and then immediately execute the code in Query window 2.

Results in Query Window 2

Msg 51001, Level 16, State 1, Procedure Employees_U_LastName, Line 21 Resource NOT Available 

As you can see, it becomes possible to control code to single use only. 

Clean up the test environment

DROP PROCEDURE dbo.Employees_U_LastName 

Concurrency and Performance Considerations

Applications should capture the error, and handle appropriately, perhaps attempting to EXECUTE the query after a brief delay.