USE [byrndb]
GO

IF OBJECT_ID('spDeleteCounty') IS NOT NULL DROP PROCEDURE spDeleteCounty
GO

CREATE PROC [dbo].[spDeleteCounty]
  @ID int
AS

IF NOT EXISTS (SELECT * FROM surveys WHERE CountyID = @ID)
BEGIN
  DELETE FROM counties WHERE CountyID = @ID
END
ELSE
BEGIN
  DECLARE @CountyName varchar(50) = (SELECT counties.CountyName
                                     FROM surveys
                                     JOIN counties ON counties.CountyID = surveys.CountyID
                                     WHERE surveys.CountyID = @ID)
  DECLARE @Count int = (SELECT counties.CountyName
			FROM surveys
			JOIN counties ON counties.CountyID = surveys.CountyID
			WHERE surveys.CountyID = @ID)
  PRINT 'The selected county of "' & @CountyName & '" is associated with ' & @Count & ' survey(s).  Disassociate the county with those surveys prior to attempting to delete it.'
END