Monday, 19 November 2012

Script to create SqlServer Table, Stored Procedure and View

PRINT 'CREATE TABLE'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [Table1]
GO
 
CREATE TABLE [dbo].[Table1](
  [Id] [uniqueidentifier] NOT NULL,
  [Name] [varchar](50) NULL,
  [DateUpdated] [datetime] NULL,
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
  (
    [Id]
  )
)
GO
 
GRANT SELECT, INSERT, UPDATE, DELETE ON [Table1] TO [SampleUser]
GO
 
 
PRINT 'CREATE STORED PROCEDURE'
GO 
 
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[StoredProcedure1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[StoredProcedure1]  
GO
 
CREATE PROCEDURE [dbo].[StoredProcedure1]  
AS
 
SET NOCOUNT ON
 
UPDATE dbo.Table1 
SET [DateUpdated]=GETDATE()
 
GO
 
GRANT EXECUTE ON [StoredProcedure1] TO [SampleUser]
GO
 
 
PRINT 'CREATE VIEW'
GO  
 
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[View1]') and OBJECTPROPERTY(id, N'IsView') = 1)
  DROP VIEW [View1]
GO
 
CREATE VIEW [dbo].[View1] AS 
  SELECT * from Table1
GO
GRANT SELECT ON [View1] TO [SampleUser]
GO
 
  
-- SIMPLE TEST -- 
 
DELETE FROM Table1
 
INSERT INTO Table1
SELECT NEWID(),'Test',GETDATE()
 
SELECT 'BEFORE',* FROM View1
 
--wait for 1s
WAITFOR DELAY '00:00:01'
 
EXEC StoredProcedure1
 
SELECT 'AFTER',* FROM View1

No comments:

Post a Comment