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. >>' +
Tag: T-SQL
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
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
Printing table contents for debugging purposes in T-SQL
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
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
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
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
Setting timepart of a datetime in T-SQL
select cast((substring(convert(varchar, getdate() , 20), 0, 12) + '10:00:00') as datetime)
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 =
How to convert / cast the current server date and time to a varchar in the format YYYY-MM-DDThh:mm:ss[+|-]hh:mm in T-SQL
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