iVend Database Database : ValidateUserTable Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTableName

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

-- =============================================

-- Author:  Abinesh

-- Create date: 01/14/2008

-- Description: Check for table existance and the validations

-- =============================================

--exec ValidateUserTable 'mytable'

CREATE PROCEDURE [dbo].[ValidateUserTable]

-- Add the parameters for the stored procedure here

@pTableName NVARCHAR(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @ErrorDesc     VARCHAR(255),

  @Error     INT,

  @KeyCount     INT,

  @lObjectId     INT

SELECT @lObjectId = OBJECT_ID(@pTableName)

  -- Insert statements for procedure here

IF NOT EXISTS

 (

  SELECT *

  FROM sys.objects Where Type = 'U'

  And Name = @pTableName

 )

BEGIN

  SET @ErrorDesc = 'Table does not exists in database.'

        GOTO ERRORHANDLER

END

IF NOT EXISTS

 (

  SELECT *

  FROM sys.columns A, sys.objects B

  Where A.object_id = B.object_id

  And B.Type = 'U'

  And B.Name = @pTableName

  And A.Name = 'id'

 )

BEGIN

  SET @ErrorDesc = 'Id Column does not exists in table.'

        GOTO ERRORHANDLER

END

IF NOT EXISTS

 (

  SELECT *

  FROM sys.columns A, sys.objects B

  Where A.object_id = B.object_id

  And B.Type = 'U'

  And B.Name = @pTableName

  And A.Name = 'description'

 )

BEGIN

  SET @ErrorDesc = 'Description Column does not exists in table.'

        GOTO ERRORHANDLER

END

--  --check for the existance of the key column

--  IF NOT EXISTS

--  (

--   SELECT *

--   FROM sys.columns A, sys.objects B

--   Where A.object_id = B.object_id

--   And B.Type = 'U'

--   And B.Name = @pTableName

--   And A.Name = 'key'

--  )

--  BEGIN

--   SET @ErrorDesc = 'key Column does not exists in table.'

--         GOTO ERRORHANDLER

--  END

--  --check for the data type of the key column

--  IF NOT EXISTS

--  (

--   SELECT *

--   FROM sys.columns A, sys.objects B

--   Where A.object_id = B.object_id

--   And B.Type = 'U'

--   And B.Name = @pTableName

--   And A.Name = 'id'

--   And A.system_type_id = 127

--  )

--  BEGIN

--   SET @ErrorDesc = 'Id Column Should have a BIGINT data type.'

--         GOTO ERRORHANDLER

--  END

--check for the primary key on the key column

Select

  COLUMN_NAME = convert(sysname,c.name),

  KEY_SEQ = convert (smallint,

          case

              when c.name = index_col(@pTableName, i.index_id, 1) then 1

              when c.name = index_col(@pTableName, i.index_id, 2) then 2

              when c.name = index_col(@pTableName, i.index_id, 3) then 3

              when c.name = index_col(@pTableName, i.index_id, 4) then 4

              when c.name = index_col(@pTableName, i.index_id, 5) then 5

              when c.name = index_col(@pTableName, i.index_id, 6) then 6

              when c.name = index_col(@pTableName, i.index_id, 7) then 7

              when c.name = index_col(@pTableName, i.index_id, 8) then 8

              when c.name = index_col(@pTableName, i.index_id, 9) then 9

              when c.name = index_col(@pTableName, i.index_id, 10) then 10

              when c.name = index_col(@pTableName, i.index_id, 11) then 11

              when c.name = index_col(@pTableName, i.index_id, 12) then 12

              when c.name = index_col(@pTableName, i.index_id, 13) then 13

              when c.name = index_col(@pTableName, i.index_id, 14) then 14

              when c.name = index_col(@pTableName, i.index_id, 15) then 15

              when c.name = index_col(@pTableName, i.index_id, 16) then 16

          end),

  PK_NAME = convert(sysname,i.name)

  INTO #TempTable

from

  sys.indexes i,

  sys.all_columns c,

  sys.all_objects o

where

  o.object_id = @lObjectId and

  o.object_id = c.object_id and

  o.object_id = i.object_id and

  i.is_primary_key = 1 and

  (c.name = index_col (@pTableName, i.index_id, 1) or

   c.name = index_col (@pTableName, i.index_id, 2) or

   c.name = index_col (@pTableName, i.index_id, 3) or

   c.name = index_col (@pTableName, i.index_id, 4) or

   c.name = index_col (@pTableName, i.index_id, 5) or

   c.name = index_col (@pTableName, i.index_id, 6) or

   c.name = index_col (@pTableName, i.index_id, 7) or

   c.name = index_col (@pTableName, i.index_id, 8) or

   c.name = index_col (@pTableName, i.index_id, 9) or

   c.name = index_col (@pTableName, i.index_id, 10) or

   c.name = index_col (@pTableName, i.index_id, 11) or

   c.name = index_col (@pTableName, i.index_id, 12) or

   c.name = index_col (@pTableName, i.index_id, 13) or

   c.name = index_col (@pTableName, i.index_id, 14) or

   c.name = index_col (@pTableName, i.index_id, 15) or

   c.name = index_col (@pTableName, i.index_id, 16))

order by 2, 1

IF NOT EXISTS

 (

  Select * From #TempTable

  Where COLUMN_NAME = 'id'

 )

BEGIN

  SET @ErrorDesc = 'Primary Key should be defined on the the Id Column.'

        GOTO ERRORHANDLER

END

SET @KeyCount = 0

Select @KeyCount = COUNT(*)

From #TempTable

if @KeyCount > 1

BEGIN

  SET @ErrorDesc = 'Primary Key should not be defined as the composite key.'

        GOTO ERRORHANDLER

END

SELECT *

FROM sys.objects Where Type = 'U'

And Name = @pTableName

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN

END