SQL transaction & error handling template

Here is a small template to execute a SQL statement in a transaction scope, with a rollback if an error occured.

BEGIN TRAN
/*
( SQL CODE TO EXECUTE HERE)
*/
GO
IF (@@ERROR = 0)
BEGIN
   COMMIT
   PRINT 'EXECUTION COMPLETED WITH SUCCESS'
END
ELSE
BEGIN
   ROLLBACK
   PRINT 'ERROR HAS OCCURED'
END

Checking if a column is a foreing key with system tables in SQL

With this request, we can see if a column of a given table is a foreign key. If it is the case, we get the parent table and the primary key associated with this column.

Replace MY_COLUMN_NAME and MY_TABLE_NAME with the real table and column values.

SELECT     dbo.sysobjects.name as CHILD_TABLE,
     dbo.syscolumns.name AS CHILD_COLUMN,
        sysobjects_1.name AS PARENT_TABLE,
     syscolumns_1.name AS PARENT_COLUMN
FROM         dbo.syscolumns syscolumns_1 INNER JOIN
           dbo.syscolumns
     INNER JOIN
           dbo.sysobjects
     INNER JOIN
           dbo.sysforeignkeys
     ON dbo.sysobjects.id = dbo.sysforeignkeys.fkeyid
     INNER JOIN
           dbo.sysobjects sysobjects_1
     ON dbo.sysforeignkeys.rkeyid = sysobjects_1.id
     ON dbo.syscolumns.id = dbo.sysforeignkeys.fkeyid AND
            dbo.syscolumns.colid = dbo.sysforeignkeys.fkey
     ON syscolumns_1.id = dbo.sysforeignkeys.rkeyid AND
           syscolumns_1.colid = dbo.sysforeignkeys.rkey
     where dbo.sysobjects.name = 'MY_COLUMN_NAME'
     and dbo.syscolumns.name = 'MY_TABLE_NAME

Works with SQL Server 2000

Checking if a culomn is a primary key with System Tables

This will return 1 if the column specified is a primary key. Works with SQL Server 2000.

You just have to replace MY_TABLE_NAME and CULOMN_NAME in whe query

select
count(*)
from sysindexes ind,
    sysindexkeys indkey,
    sysobjects obj,
    sysobjects obj2,
    syscolumns col
 where indkey.id + indkey.indid = ind.id + ind.indid and
       ind.name = obj.name and
       ind.id = obj.parent_obj and
       col.id = indkey.id and
       obj.parent_obj = col.id and
       obj.parent_obj = obj2.id and
       col.colid = indkey.colid and
       obj.xtype = 'PK' and
       obj2.name = 'MY_TABLE_NAME' and
       col.name = 'CULOMN_NAME'   

sql server french format date

Here is how we can get a YYYY-MM-DD date format, without the hours and minutes.

select CONVERT(VARCHAR(10), GetDate(), 120) as [myFrenchDate]

Sql Server add culumn if not exist

Generic script for adding a column if not already existing in database.

IF NOT EXISTS ( SELECT * 
  FROM [dbo].syscolumns 
   ID = OBJECT_ID(N'[dbo].tableName') 
    AND   NAME = N'culumnName') 
BEGIN
ALTER TABLE dbo.tableName
  ADD 
 culumnName decimal NULL
END

Cursor in SQL Server

Example of a simple cursor, SQL Server.

DECLARE @id integer
DECLARE @name  varchar(500)

    DECLARE myCursor CURSOR FOR
    SELECT   cursomerId, [name]
    FROM   CUSTOMER

    OPEN myCursor

    FETCH NEXT FROM myCursor
    INTO @id, @name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- do something (updates, insert into other tables, etc)
    print @name

        FETCH NEXT FROM myCursor
        INTO @id, @name

    END

CLOSE myCursor
DEALLOCATE myCursor