dbo.IsValidSQL

Here’s a handy stored procedure I found that validates dynamic SQL for SQL Server.

IF OBJECT_ID (‘dbo.IsValidSQL’, ‘P’) IS NOT NULL
DROP PROCEDURE dbo.IsValidSQL;
GO
CREATE PROC [dbo].[IsValidSQL]
( @sql VARCHAR(MAX) )
AS
BEGIN
  BEGIN TRY
    SET @sql = 'set parseonly on;' + @sql;
    EXEC (@sql);
  END TRY
  BEGIN CATCH
    RETURN(1);
  END CATCH;
  RETURN(0);
END;
Advertisements

dbo.People

Modify to suit – I just saved a few minutes of your time. You’re welcome!

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.People')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.People (
     ID BIGINT PRIMARY KEY IDENTITY(1,1),
     Salutation NVARCHAR(30) , -- Mr, Mrs, Dr, Miss, etc.
     Title NVARCHAR(100) , --
     FirstName1 NVARCHAR(50) ,
     FirstName2 NVARCHAR(50) ,
     MiddleName1 NVARCHAR(50) ,
     MiddleName2 NVARCHAR(50) ,
     MiddleInitial CHAR(1) ,
     LastName1 NVARCHAR(100) ,
     LastName2 NVARCHAR(100) ,
     Suffix NVARCHAR(30) );
END
GO

dbo.Addresses

I just saved you a few minutes of time – you’re welcome!

IF NOT EXISTS ( SELECT *
                FROM   sys.Objects
                WHERE  object_id = OBJECT_ID(N'dbo.Addresses')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Addresses (
     ID BIGINT PRIMARY KEY IDENTITY(1,1),
     AddressType INT , -- Foreign Key to AddressTypes table
     ContactID BIGINT , -- Foreign Key to a People or Contacts table
     PreDirectional NVARCHAR(20) , -- South, SW, NE, East
     StreetNo NVARCHAR(20) , -- location on street
     StreetName NVARCHAR(50) , -- name of the street, if applicable
     CrossStreetName NVARCHAR(50) , -- for purposes of wayfinding
     PostDirectional NVARCHAR(20) , -- South, SW, NE, East
     StreetType NVARCHAR(30) , -- Street, Road, Boulevard, Annex, Canyon
     UnitNo NVARCHAR(20) , -- Apt, Suite, etc.
     PostOfficeBox NVARCHAR(20) ,
     FloorNo NVARCHAR(20) ,
     BuildingNo NVARCHAR(20) ,
     BuildingName NVARCHAR(60) ,
     CampusName NVARCHAR(50) ,
     LotNo NVARCHAR(20) ,
     PlatNo NVARCHAR(20) ,
     PlatName NVARCHAR(20) ,
     CityNo BIGINT , -- FK to Cities.CityID
     CityName NVARCHAR(20) ,
     ParishName NVARCHAR(50) ,
     PostalCode NVARCHAR(10) , -- USPS, Canada and other foreign postal services
     MailStop NVARCHAR(20) ,
     USPS_StopCode BIGINT , -- USPS postal stop code
     Latitude DECIMAL (18,4) ,
     Longitude DECIMAL (18,4) ,
     CountyID BIGINT ,
     CountyName NVARCHAR(30) ,
     StateID INT ,
     StateAbbr NVARCHAR(20) ,
     CountryID INT ,
     RegionID INT ,
     MSACode NVARCHAR(20)
);
END
GO

Cities, Counties, States

IF NOT EXISTS ( SELECT *
                FROM   sys.Objects
                WHERE  object_id = OBJECT_ID(N'dbo.Cities')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Cities (
     CityID    BIGINT IDENTITY(1,1),
     CityName  NVARCHAR(50) ,
     StateID   INT ,
     MsaID     INT
  );
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.Counties')
                       AND type in (N'U')
)
BEGIN
  CREATE TABLE dbo.Counties (
     CountyID    BIGINT IDENTITY(1,1),
     CountyName  NVARCHAR(50) ,
     StateID     BIGINT
  );
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.States')
                AND    type in (N'U')
)
BEGIN
  CREATE TABLE dbo.States(
     ID            INT IDENTITY(1,1) NOT NULL,
     Name          NVARCHAR(40)      NOT NULL,
     Abbreviation  NVARCHAR(2)       NOT NULL,
     CountryID     INT               NOT NULL,
     CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED ([Id] ASC),
     CONSTRAINT [uc_States_Abbreviation]
     UNIQUE NONCLUSTERED ([Abbreviation] ASC)
);
SET IDENTITY_INSERT States ON;

DECLARE @US_CountryId INT = 226;
DECLARE @CAD_CountryId INT = 38;

INSERT INTO dbo.States ( Id, Name, Abbreviation, CountryId )
VALUES
(1, 'Alaska', 'AK', @US_CountryId),
(2, 'Alabama', 'AL', @US_CountryId),
(3, 'American Samoa', 'AS', @US_CountryId),
(4, 'Arizona', 'AZ', @US_CountryId),
(5, 'Arkansas', 'AR', @US_CountryId),
(6, 'California', 'CA', @US_CountryId),
(7, 'Colorado', 'CO', @US_CountryId),
(8, 'Connecticut', 'CT', @US_CountryId),
(9, 'Delaware', 'DE', @US_CountryId),
(10, 'District of Columbia', 'DC', @US_CountryId),
(11, 'Florida', 'FL', @US_CountryId),
(12, 'Georgia', 'GA', @US_CountryId),
(13, 'Guam', 'GU', @US_CountryId),
(14, 'Hawaii', 'HI', @US_CountryId),
(15, 'Idaho', 'ID', @US_CountryId),
(16, 'Illinois', 'IL', @US_CountryId),
(17, 'Indiana', 'IN', @US_CountryId),
(18, 'Iowa', 'IA', @US_CountryId),
(19, 'Kansas', 'KS', @US_CountryId),
(20, 'Kentucky', 'KY', @US_CountryId),
(21, 'Louisiana', 'LA', @US_CountryId),
(22, 'Maine', 'ME', @US_CountryId),
(23, 'Maryland', 'MD', @US_CountryId),
(24, 'Massachusetts', 'MA', @US_CountryId),
(25, 'Michigan', 'MI', @US_CountryId),
(26, 'Minnesota', 'MN', @US_CountryId),
(27, 'Mississippi', 'MS', @US_CountryId),
(28, 'Missouri', 'MO', @US_CountryId),
(29, 'Montana', 'MT', @US_CountryId),
(30, 'Nebraska', 'NE', @US_CountryId),
(31, 'Nevada', 'NV', @US_CountryId),
(32, 'New Hampshire', 'NH', @US_CountryId),
(33, 'New Jersey', 'NJ', @US_CountryId),
(34, 'New Mexico', 'NM', @US_CountryId),
(35, 'New York', 'NY', @US_CountryId),
(36, 'North Carolina', 'NC', @US_CountryId),
(37, 'North Dakota', 'ND', @US_CountryId),
(38, 'Northern Mariana Islands', 'MP', @US_CountryId),
(39, 'Ohio', 'OH', @US_CountryId),
(40, 'Oklahoma', 'OK', @US_CountryId),
(41, 'Oregon', 'OR', @US_CountryId),
(42, 'Palau', 'PW', @US_CountryId),
(43, 'Pennsylvania', 'PA', @US_CountryId),
(44, 'Puerto Rico', 'PR', @US_CountryId),
(45, 'Rhode Island', 'RI', @US_CountryId),
(46, 'South Carolina', 'SC', @US_CountryId),
(47, 'South Dakota', 'SD', @US_CountryId),
(48, 'Tennessee', 'TN', @US_CountryId),
(49, 'Texas', 'TX', @US_CountryId),
(50, 'Utah', 'UT', @US_CountryId),
(51, 'Vermont', 'VT', @US_CountryId),
(52, 'Virgin Islands', 'VI', @US_CountryId),
(53, 'Virginia', 'VA', @US_CountryId),
(54, 'Washington', 'WA', @US_CountryId),
(55, 'West Virginia', 'WV', @US_CountryId),
(56, 'Wisconsin', 'WI', @US_CountryId),
(57, 'Wyoming', 'WY', @US_CountryId),
-- included here for lookup convenience
(58, 'Alberta', 'AB', @CAD_CountryId),
(59, 'British Columbia', 'BC', @CAD_CountryId),
(60, 'Manitoba', 'MB', @CAD_CountryId),
(61, 'New Brunswick', 'NB', @CAD_CountryId),
(62, 'Newfoundland and Labrador', 'NL', @CAD_CountryId),
(63, 'Northwest Territories', 'NT', @CAD_CountryId),
(64, 'Nova Scotia', 'NS', @CAD_CountryId),
(65, 'Nunavut', 'NU', @CAD_CountryId),
(66, 'Ontario', 'ON', @CAD_CountryId),
(67, 'Prince Edward Island', 'PE', @CAD_CountryId),
(68, 'Québec', 'QC', @CAD_CountryId),
(69, 'Saskatchewan', 'SK', @CAD_CountryId),
(70, 'Yukon Territory', 'YT', @CAD_CountryId);

SET IDENTITY_INSERT dbo.States OFF;
END
GO

IF NOT EXISTS ( SELECT *
                FROM   sys.objects
                WHERE  object_id = OBJECT_ID(N'dbo.CanadianProvinces')
                       AND type in (N'U')
)
BEGIN
CREATE TABLE dbo.CanadianProvinces
(
   ProvinceID    BIGINT PRIMARY KEY IDENTITY(1,1) ,
   ProvinceName  NVARCHAR(50) ,
   Abbreviation  CHAR(2)
);
INSERT INTO dbo.CanadianProvinces
VALUES
('British Columbia','BC'),
('Manitoba','MB'),
('Alberta','AB'),
('Ontario','ON'),
('Saskatchewan','SK'),
('Quebec','QE'),
('Nunavut','NU'),
('Yukon','YK'),
('Northwest Territories','NT'),
('Nova Scotia','NS'),
('New Brunswick','NB'),
('Newfoundland & Labrador','NL'),
('Prince Edward Island','PE');
END
GO

T-SQL Try-Catch Template

–TRY/CATCH TEMPLATE
BEGIN TRY
–check to see if .Net initiated a SqlTransaction before calling this proc
IF @@TRANCOUNT = 0
BEGIN
–.NET did not initiate an explicit SqlTransaction outside of the procedure
— as a result, we initiate an explicit T-SQL transaction here.
BEGIN TRAN;
END

/* PUT SCRIPT CODE RIGHT HERE */

— PRINT ‘>> COMMITING TRANSACTION’ — optional
— no error yet, so commit the transaction
— if query is select only; this statement is meaningless.
IF @@TRANCOUNT = 1 COMMIT TRAN;
END TRY

BEGIN CATCH
/*
There will be either a .Net SqlTransaction or T-SQL transaction here to rollback;
we need to roll back that transaction before handling the exception.

Test XACT_STATE for 0, 1, or -1.
1 = the transaction is COMMITTABLE (we are going to roll back anyways).
-1 = the transaction is UNCOMMITTABLE and should be rolled back.
0 = there is no transaction and a commit or rollback operation would generate an error.

Test whether the transaction is uncommittable.
*/

IF (XACT_STATE() 0)
BEGIN
PRINT ‘>> ROLLING BACK TRANSACTION’
ROLLBACK TRANSACTION;
END

—- OPTIONAL EXCEPTION PROPERTIES TO CATCH AS NEEDED.
–@ErrorNo INT = ERROR_NUMBER(),
–@ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE(),
–@ErrorLine AS INT = ERROR_LINE()

DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorSeverity INT ,
@ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();

— Use RAISERROR inside the CATCH block to return error information
— about the original error that caused execution to jump to the CATCH block.
PRINT ‘>> RAISING ERROR’
https://technet.microsoft.com/en-us/library/ms177497(v=sql.105).aspx
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState );
END CATCH

SQL Server *Worst* Practices

Here’s a link to a great article I read recently that summarized in a concise package things not to do when using SQL Server as a data-persistence solution, which is almost more important than what to do.

Note: SQL cursors especially – please never use cursors of any flavor in your stored procedures. There is almost always a better set-based method to accomplish your end goal.

As it’s not mentioned in the following article, I will add that not making use of SQL Server metadata is a mistake I see very frequently. What is metadata?  It’s data that describes other data or data structures. It’s particularly easy to implement with SQL Server once you know what to do and how to use it. There is probably no easier way to document a database for all users or stakeholders involved than to implement/update SQL Server metadata, and there are no good reasons not to utilize it.  It’s easy and freely available – just waiting to be used.  I have a set of stored procedures developed that make the process absolutely painless for keeping SQL Server database metadata updated at the table, column, view, and function/param levels (all the places that really count!), and across all environments (production, pilot, test).

https://www.mssqltips.com/sqlservertip/1707/sql-server-worst-practices/