T-SQL tip: log error details in catch

If you want to log detailed information on an error in a catch block, you can use the following code:

 

BEGIN CATCH
        declare @Message varchar(2048) = 
        '<< Your custom error text here. >>' + 
        'Number: ' + cast(isnull(error_number(), '') as varchar(50)) + ' ' +
        'Severity: ' + cast(isnull(error_severity(), '') as varchar(50)) + ' ' +
        'State: ' + cast(isnull(error_state(), '') as varchar(100)) + ' ' +
        'Procedure: ' + cast(isnull(error_procedure(), '') as varchar(1024)) + ' ' +
        'Line: ' + cast(isnull(error_line(), '') as varchar(10)) + ' ' +
        'Message: ' + cast(isnull(error_message(), '') as varchar(1024)) + ' '

        ROLLBACK TRANSACTION    

        RAISERROR (@Message, 16, 1)
        

 

END CATCH

Execute a stored procedure for each row without using a cursor in T-SQL

If you want to execute a stored procedure for each row in T-SQL 2012, where the Id column is not a consecutive number, you van use the following T-SQL code

 

SQL Server >= 2012

declare @Person table
(
    Id          int                not null,
    Name        varchar(max)    not null
)

insert into @Person values
    (1, 'John'),
    (4, 'Mike')


-- Determine loop boundaries.
declare @Id int = 0
declare @counter int = 0
declare @total int = isnull((select count(1) from @Person), 0)
    
-- Iterate records.
while (@counter <> (@total))
begin  
        -- record to proces.
        set @Id = (select Id from @Person order by Id offset @counter rows fetch next 1 rows only)

        -- Exec sproc here:
        select @Id
                    
        -- Increase counter to break the loop after all records are processed.
        set @counter = @counter + 1
end

If however you are stuck on SQL Server before 2012 and your Id’s are consecutive you can use the following code:

 

SQL Server < 2012

 

declare @Person table
(
    Id            int                not null identity(1,1),
    Name        varchar(max)    not null
)

insert into @Person values 
    ('John'),
    ('Mike')

-- Determine first record to process.
declare @Id int = (select min(Id) from @Person)

-- Iterate records.
while (@Id is not null) 
begin  
    
    -- Exec sproc here:
    select    Name
    from    @Person
    where    Id = @Id
            
    -- Select next record, this will break the loop if next record is not found.
    set @Id = (select Id from @Person where Id = @Id + 1)
end

How to get the full stored procedure name (incl. schema name) within the current executing stored procedure.

 

Here is a function you can use to get the full stored procedure name (incl. schema name) within the current executing stored procedure:

 

if  object_id('dbo.GetFullSprocName') is not null
begin
    drop function dbo.GetFullSprocName
end
go

create function dbo.GetFullSprocName (@sprocId int)
returns varchar(255)
as
begin
    declare @FullSprocName as varchar(255)
    
    set @FullSprocName = (
        select        top 1 s.name + '.' + o.name
        from        sys.objects o with(nolock)
        inner join    sys.schemas s with (nolock) on o.schema_id = s.schema_id
        where        o.object_id = @sprocId
    )

    return @FullSprocName
end
go

To call this function use:

 

declare @sprocName varchar(128) = dbo.GetFullSprocName(@@PROCID)

Drop all stored procedures not belonging to the dbo schema from a SQL Server database

if you want to drop all stored procedures not belonging to the dbo schema from a SQL Server database, you van use the following select statement to generate a list that can be executed to drop the sprocs.

PS: It’s your foot.

 

    select        'drop procedure ' + s.name + '.' + o.name
    from          sys.objects o with (nolock)
    inner join    sys.schemas s with (nolock) on o.schema_id = s.schema_id
    where         o.type = 'P'
    and           s.name <> 'dbo'

How to left pad a string in T-SQL (SQL Server 2012)

If you want to left pad a string in T-SQL in Microsoft SQL Server 2012, you can use the format function:

To get the current hour padded with, zeros use:

declare @CurrentDateTime datetime = '2013-04-03 05:06:17'
select format(datepart(hh, @CurrentDateTime), '00')

-- Result: 05

For more information, including (SQL 2005, and 2008 options):

http://www.tech-recipes.com/rx/30469/sql-server-how-to-left-pad-a-number-with-zeros/

Fix: Arithmetic overflow error converting IDENTITY to data type int

I was getting the error: "Arithmetic overflow error converting IDENTITY to data type int", when inserting a record in a table with a identity column. The max id of this table was 45448, this is certainly not the max of int in SQL Server, so I expected the next Id to be 45449, but DBCC CHECKIDENT returned 2147483647 (the max of an int). 

DBCC CHECKIDENT ('Reporting.MyTable');

Reseeding the table with DBCC CHECKIDENT fixed the problem:

DBCC CHECKIDENT ('Reporting.MyTable', reseed, 45449)

How to drop a foreign key based on a table name and column name with T-SQL.

If you want to delete a foreign key based on a given table name and column name in T-SQL, you can use the following code snippet:

Full table name = [MySchema1].[MyTable1]

Full column name = [MyColumn1]

declare @TableName varchar(255)
set @TableName = '[MySchema1].[MyTable1]'

declare @ColumnName varchar(255)
set @ColumnName = 'MyColumn1'

declare @TabelObjectId int
set @tabelObjectId = Object_ID(@TableName)

declare @ForeignKeyObjectId int
select        @ForeignKeyObjectId = fc.constraint_object_id
from        sys.foreign_key_columns fc
inner join    sys.columns c  on  c.object_id = parent_object_id and c.column_id = fc.parent_column_id
where        fc.parent_object_id = @tabelObjectId and c.name = @ColumnName

declare @ForeignKeyName int
set @ForeignKeyName = (select name from sys.objects where object_id = @ForeignKeyObjectId)

if object_id(@ForeignKeyObjectId) is not null
begin
    exec('alter table ' + @TableName + ' drop constraint ' + @ForeignKeyName)
end