Tag: T-SQL

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

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

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)

Sometimes you just want to add a print statement in a stored procedure to output the contents of a table.

Then you can use the "FOR XML" statement in T-SQL:

declare @Data_as_xml xml
set @Data_as_xml = (select top 4 * from sys.tables FOR XML PATH(''))
print convert(varchar(8000), @Data_as_xml)

Result:

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/

select cast((substring(convert(varchar, getdate() , 20), 0, 12) + '10:00:00') as datetime)

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

To convert the current machine date and time to a varchar in the format YYYY-MM-DDThh:mm:ss[+|-]hh:mm in T-SQL, you can use the following code:

 
select convert(varchar(100), cast(sysdatetimeoffset() as datetimeoffset(0)), 126)

Result (in Amsterdam in Summer time)

2012-08-13T15:23:09+02:00