SQL "How To's"

1)How to display Year as YYYY instead of YY

Enterprise Manager adopts the User's regional settings for the date format. By default, the regional setting for the short date format is m/d/yy. To display it in m/d/yyyy format, follow the following steps:

On the taskbar, click the Start button, point to Settings and then select Control Panel. In the Control Panel dialog box double-click Regional Settings. In the Regional Settings Properties dialog box, select the Date tab. Next, change the value for the Short Date Style from M/d/yy to M/d/yyyy.

2) How to Build a Comma Delimited String from Table values

Sometimes you might want to return your records in a single comma delimited string. For example, if you have records like :-

Mango
Banana
Peach
Grapes
Strawberry

Instead of returning it as a recordset containing these 5 records, you might want to return them as 'Mango, Banana, Peach, Grapes, Strawberry'. Here's how you can accomplish this.

-- Create a temporary table & insert dummy records

Create Table #Fruits (Fruit Varchar(25)) Insert #Fruits (Fruit) values('Mango') Insert #Fruits (Fruit) values('Banana') Insert #Fruits (Fruit) values('Peach') Insert #Fruits (Fruit) values('Grapes') Insert #Fruits (Fruit) values('Strawberry') 

-- Build comma delimited string

Declare @Fruits Varchar(200) Set @Fruits = '' Update #Fruits Set @Fruits = @Fruits + Fruit + ',' Set @Fruits = Substring(@Fruits,1,len(@Fruits)-1)               -- to remove extra comma at the end print @Fruits

3) How to add a User Defined Error Message
Use Master Go  EXEC sp_addmessage @msgnum = 50001, @severity = 16,     @msgtext = N'Failed to insert Customer Transaction into %s table',     @lang = 'us_english'  Go 
You can raise this message from your code like this :-
INSERT Customers (CustomerName) VALUES ('Rick Nelson')  IF @@Error <> 0             RAISERROR (50001,16,1, 'Customers')

4) How to get the Rowcount as well as Error at the same time
Normally when we run a Query, we check if the query was executed successfully or not before commiting it.
DECLARE @CustomerId            Int SET @CustomerId = 1  Begin Transaction Update Orders Set OrderDate = GetDate() Where CustomerId = @CustomerId  IF @@Error <> 0 Begin             Raiserror('Failed to update Orders',16,1)                Rollback Transaction End  Commit Transaction 
But what if you also want to get the number of records which were effected by this query? If you do a @@Rowcount after your Error checkingstatement then you are going to get 0 as the value of @@Recordcount would have been reset. And if you place @@Recordcount before the error-checking statement then your @@Error would get reset. So what's the solution to get both of them at the same time ? Save both the values in a local variable and then check that local variable. Here's how it can be done.
DECLARE @Rcount                      Int DECLARE @ErrNum                    Int  DECLARE @CustomerId            Int  SET @Rcount = 0 SET @ErrNum = 0 SET @CustomerId = 1   Begin Transaction Update Orders Set OrderDate = GetDate() Where CustomerId = @CustomerId  SELECT @Rcount = @@RowCount, @ErrNum = @@Error  -- check if there was an error in Updating the records IF @ErrNum <> 0 Begin             Raiserror('Failed to update Orders',16,1)                Rollback Transaction End  -- check if some records were updated or not IF @Rcount = 0 Begin             Raiserror('No records for CustomerId %d',16,1, @CustomerId)                          Rollback Transaction End  Commit Transaction