Category Archives: T-SQL

Don’t drop that Stored Procedure, Alter it!

Database Administrator, dba, SQL Server, T-SQL, , , , , , , , , , , , , , ,

So when writing scripts to create tables you might often include some SQL like below to first assess whether or not the table exists before dropping it.

IF OBJECT_ID('Schema.TableName', 'U') IS NOT NULL
      DROP TABLE Schema.TableName
      GO

 

This might be because you are still testing data and want a table with a different structure to be created or to exist and be populated with different data while using the same table name. So it’s the right thing to do in that circumstance but you may be wrongly carrying that thinking forward into creating stored procedures and user defined functions.

By dropping an SP or UDF you are also breaking any securities or permissions associated with that SP or UDF meaning these permissions etc. will have to be created again.

But for SPs and UDFs you don’t actually need to drop the SP or UDF you just want to change it if it exists. In which case you use Alter rather than Create. However what if you are unaware as to whether the SP or UDF actually exists?

I propose creating dummy SP’s or UDF’s in their place which will simply be over written as demonstrated below.

-- ALTER STORED PROCEDURE
-- THIS DUMMY SP JUST SELECTS 1
IF OBJECT_ID('[Schema].[NameOfStoreProcedure]') IS NULL
	EXEC ('CREATE PROCEDURE [Schema].[NameOfStoreProcedure] AS SELECT 1')
GO

ALTER PROCEDURE [Schema].[NameOfStoreProcedure] @DateParameter DATE
	,@IntParameter INT
	,@CharParameter VARCHAR(30)
AS
BEGIN
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	--E.G.
	SELECT 1
	------------------------------------------------------
	------------------------------------------------------
	
END;
GO

-- ALTER FUNCTION
-- THIS DUMMY UDF SIMPLY SETS THE PARAMETER @INT TO 1
IF OBJECT_ID('[Schema].[NameOfFunction]') IS NULL
	EXEC ('
CREATE FUNCTION [Schema].[NameOfFunction] (@INT AS INT)
RETURNS INT
AS
BEGIN
	SELECT @INT = 1
	RETURN @INT
END
')
GO

ALTER FUNCTION [Schema].[NameOfFunction] (@INT AS INT)
RETURNS INT
AS
BEGIN
	SET @INT = @INT + 1
	RETURN @INT
END
GO

-- RUN FUNCTION TO SEE RESULT
SELECT [Schema].[NameOfFunction](10) AS ReturnedValue

 

Universal Error Logging for Stored Procedures in SQL Server

Reporting, SQL Server, T-SQL, , , , , , , ,

Probably the best way to log whether your stored procedure ran correctly or threw an error during its run is to create another stored procedure to capture the events of the run.

This example although basic captures the primary data you would want to keep a record of regarding a procedures run.

  • The name of the stored procedure
  • The start time of the stored procedure
  • The time the stored procedure finished/errored
  • A user comment/error message
  • An assigned Log ID

Below is a pre-baked example requiring just a change to the DatabaseName, and if you wish SP_Name, text fields, as instructed in the comments of the SQL below, to test run the script.

This will create a SP_Log table and the stored procedure InsertRunIntoSpLog which can be reused over and over again when making stored procedures for other jobs to log the success/failure of these jobs.

Step 3 is an example stored procedure, which can be renamed as something other than SP_Name, which simply displays the date time.  Once created whenever the following is executed, EXEC dbo.SP_Name, the current date time will be returned and a record of the procedures run will be added to a row in the SP_Log table.

Although as stated above this is a basic example it is a very powerful foundation which can be built upon for very comprehensive event and transaction logging.

 


— CHANGE [DatabaseName] TO THE NAME OF THE DB REQUIRED
— CHANGE SP_NAME TO THE NAME OF THE SP REQUIRED
—————————————————
— STEP 1
— CREATE TABLE TO STORE ERRORS
—————————————————
USE [DatabaseName]
GO
— DROP LOG TABLE IF ALREADY EXISTS
IF OBJECT_ID('dbo.SP_Log', 'U') IS NOT NULL
DROP TABLE dbo.SP_Log
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
— CREATE LOG TABLE
CREATE TABLE [dbo].[SP_Log] (
[LogID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL
,[NameOfSp] [nvarchar](100) NOT NULL
,[StepCode] [varchar](1) NOT NULL
,[RunDate] [datetime] NOT NULL
,[Comment] [nvarchar](200) NULL
,PRIMARY KEY CLUSTERED ([LogID] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
—————————————————
— STEP 2
— CREATE STORED PROCEDURE TO WRITE ERRORS TO ERROR TABLE
—————————————————
USE [DatabaseName]
GO
IF OBJECT_ID('dbo.InsertRunIntoSpLog', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[InsertRunIntoSpLog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— CREATE STORED PROCEDURE
CREATE PROCEDURE [dbo].[InsertRunIntoSpLog] @SpVariableName NVARCHAR(100)
,@StepCode VARCHAR(1)
,@Comment NVARCHAR(400)
AS
BEGIN
INSERT INTO SP_Log (
NameOfSp
,StepCode
,RunDate
,Comment
)
VALUES (
@SpVariableName
,@StepCode
,getutcdate()
,@Comment
)
END
GO
—————————————————
— STEP 3
— STORED PROCEDURE RUN / ERROR CATCH EXAMPLE
—————————————————
IF OBJECT_ID('dbo.SP_Name', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[SP_Name]
GO
CREATE PROCEDURE [dbo].[SP_Name]
AS
BEGIN
BEGIN TRY
— INSERT SP RUN LOG FOR SP START
EXEC InsertRunIntoSpLog 'SP_NAME' — SP_NAME
,'S' — S, F OR E (START, FINNISH OR ERROR)
,'' — USER COMMENT
— QUERY HERE
— FOR EXAMPLE
SELECT GETDATE() AS CurrentDateTime
— INSERT SP RUN LOG FOR SP END
EXEC InsertRunIntoSpLog 'SP_NAME'
,'F'
,''
END TRY
BEGIN CATCH
PRINT 'error!'
— INSERT SP RUN LOG FOR SP ERROR
DECLARE @error_number AS INTEGER
DECLARE @error_message AS VARCHAR(400)
SET @error_number = error_number()
SET @error_message = left(error_message(), 400)
PRINT 'error_message: ' + @error_message
EXEC InsertRunIntoSpLog 'SP_NAME'
,'E'
,@error_message
END CATCH
END

view raw

Universal Error Logging for Stored Procedures in SQL Server

hosted with ❤ by GitHub

Report Requirements Template

Reporting, SQL Server, T-SQL, , , , , , , , ,

When a developer is asked to create a business report it always involves a meeting of two disciplines.

The developer has the technical knowledge of pulling raw data, manipulating it and presenting interpreted data as information to be consumed by an end user. They may also know the location and source of the data to be used and how regularly this source is refreshed or updated with new data.

The Requester of the report will usually be the person with business domain knowledge. They understand the business rules which need to be applied to the data to transform it into information.

An obvious example for this is the relationship between a SQL developer working in an accounting environment. The developer is not an account and the account is not a developer but the two need to be able to collaborate and communicate to ensure that a report eventually outputs the correct information at the require period reliably.

Failure in achieving this goal can have disastrous consequences. For example projecting further revenue based on faulty data.

A good start for creating a foundation for effective communication and collaboration is for the developer and requester to step through a requirements template. This gives the report a structure and focus while also serving as a means of documenting the creation of the report from a business perspective and a future resource from a development perspective, i.e. the requirement of a future developer new to the report updating and modifying the report.

Attached is an example Template.

The Requirements sheet deals with questions like:

Who is the requester?

Why is the report needed?

What information will the report return?

This sheet will primarily be filled in by the requester.

The Report Fields sheet tries to plug the gap in knowledge between the requester and the developer by exploring what is the information required and what fields, calculations correspond to this information. The developer and requester may need to step through this sheet together to ensure their intended outcomes align.

Distinct Row Count Table

T-SQL, , , , , ,

If you come from a programming background you might want to do a loop using TSQL rather than using a cursor.

The syntax for this is pretty recognisable, exempli gratia,


DECLARE @thisThing INT
DECLARE @thatThing INT
SET @thisThing = 1
SET @thatThing = 10
WHILE @thisThing < @thatThing
BEGIN
–Do stuff
SELECT GETDATE()
SET @thisThing = @thisThing + 1
END

view raw

gistfile1.sql

hosted with ❤ by GitHub

However if you want to feed in dynamic variables captured from a table you can create a distinctive row count table.

This deduplicates the occurrence of a field value and assigns it a row number.

Where this can be used will be included in a more complicated example coming in the future.


IF OBJECT_ID('tempdb..#rowCountTB') IS NOT NULL
DROP TABLE #rowCountTB;
WITH DistinctTable
AS (
SELECT DISTINCT [fieldValue]
FROM [tableName]
)
,RowNumber
AS (
SELECT ROW_NUMBER() OVER (
ORDER BY [fieldValue]
) AS RowNum
,[fieldValue]
FROM DistinctTable
)
SELECT *
INTO #rowCountTB
FROM RowNumber
ORDER BY [fieldValue]

view raw

gistfile1.sql

hosted with ❤ by GitHub

How to find columns from all Tables of a Database

Excel, GDPR, Reporting, sql, SQL Server, SSRS, SSRS Reporting, T-SQL, , , , , , , , , , , , ,

If your job is to create reports using SQL chances are you have or will encounter this situation:

You’ve been asked to prepare a report, but the person who has asked for the report simply has a list of fields they want and they have no idea where those fields come from. They may have received previous reports in the past, so they know the fields exist, but they cannot provide any of the SQL queries used to create these reports as an example.

You, the developer, may not be familiar with that particular area of the business or associated data sources. Possibly because you typically prepared financial reports and this request has come from the operations or marketing departments.

So the first step is to locate these columns within the database.

The following query will return the Table Name, Schema Name and Column Name from the database.

In the example below all instances where the column name equals CustomerID, OrderID, OrderDate will be returned. Also Column names that contain the word Status or Promotion will also be return. Simply change or add additional columns names as needed. 

USE [YourDatabaseName];
GO

SELECT T.NAME AS TableName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,C.NAME AS ColumnName
FROM SYS.TABLES AS t
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME = 'CustomerID'
	OR C.NAME = 'OrderID'
	OR C.NAME = 'OrderDate'
	OR C.NAME LIKE '%Status%'
	OR C.NAME LIKE '%Promotion%'
ORDER BY SchemaName
	,TableName;

 

That should help get you started in preparing the report.

How to dynamically pull all text file names from a folder into a SQL Server table

T-SQL, , , , , , , , , , , , ,

So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.

To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .

-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
-- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED

Sayōnara


— THIS QUERY USES CMD TO CAPTURE ALL THE FILES IN A FOLDER
— AND WRITE THE FILE NAMES TO A TABLE
— THE QUERY REQUIRES THAT xp_cmdShell BE ENABLED TO WORK
— THE FOLLOWING ENABLES xp_cmdShell:
— TO ALLOW ADVANCED OPTIONS TO BE CHANGED
EXEC sp_configure 'show advanced options'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR ADVANCED OPTIONS
RECONFIGURE
GO
— TO ENABLE THE FEATURE
EXEC sp_configure 'xp_cmdshell'
,1
GO
— TO UPDATE THE CURRENTLY CONFIGURED VALUE FOR THIS FEATURE
RECONFIGURE
GO
— THE FOLLOWING CREATES A TABLE AND POPULATES IT WITH THE FILES LOCATED IN THE SPECIFIED DIRECTORY
IF OBJECT_ID('ALLFILENAMES', 'U') IS NOT NULL
DROP TABLE ALLFILENAMES
CREATE TABLE ALLFILENAMES (
WHICHPATH VARCHAR(255)
,WHICHFILE VARCHAR(255)
)
DECLARE @filename VARCHAR(255)
,@path VARCHAR(255)
,@cmd VARCHAR(1000)
— GET THE LIST OF FILES TO PROCESS
— NOTES: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING
— *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED
SET @path = 'C:\Users\Data\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES (WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES
SET WHICHPATH = @path
WHERE WHICHPATH IS NULL
SELECT *
FROM ALLFILENAMES

view raw

DynamicallyPullTextFileNames

hosted with ❤ by GitHub

How to get the hour from a datetime string (T-SQL)

How To, Reporting, sql, SQL Server, T-SQL, , , , , , , , , , , , ,

Often data is entered into the database as a string (varchar), when a more appropriate data type should have been applied.

Here’s a snippet of T-SQL for dealing with datetime data, stored as varchar, when what you really need is the hour. 

DECLARE @DateTime AS VARCHAR(25)

-- GET THE CURRENT DATETIME AND CONVERT INTO A STRING
SET @DateTime = (
		SELECT CONVERT(VARCHAR(25), GETDATE(), 120)
		)

--PRINT @DateTime
-- GET HOUR FROM DATETIME AS A STRING
SELECT DATEPART(HOUR, CONVERT(DATETIME, CONVERT(VARCHAR(13), @DateTime, 120) + ':00')) AS ConvertedHour

 

Why would data be stored as a varchar instead of using the appropriate data type in the first place?

The most common reason might be that XML files have been loaded without any data type conversions being applied, i.e. dates have been entered as strings along with everything else.

Why would you need this code?

Should you be confronted with data in this raw string form you would be unable to apply a clause regarding a specific hour. Ideally there should be some sort of staging table to convert the fields into the appropriate data types but if you simply wanted to apply some clauses to select specific data, which will be outputted to an excel file for example, that additional effort may be unnecessary.