Sample: TestDB Test Stored Procedures

TestCreate

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestCreate]
	@code AS NVARCHAR(10),
	@name AS NVARCHAR(50),
	@description AS NVARCHAR(100),
	@createdBy AS NVARCHAR(10),
	@createdDate AS DATETIME
AS
	INSERT INTO Test (
		Code, 
		Name, 
		Description, 
		CreatedBy, 
		CreatedDate
        ) VALUES (
		@code, 
		@name, 
		@description, 
		@createdBy, 
		@createdDate
        );
	SELECT CAST(SCOPE_IDENTITY() AS INT);
RETURN 0
GO

TestRead

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestRead]
	@testID AS INT
AS
	SELECT *
	FROM dbo.[Test]
        WHERE TestID = @testID;
RETURN 0
GO

TestUpdate

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestUpdate]
	@code AS NVARCHAR(10),
	@name AS NVARCHAR(50),
	@description AS NVARCHAR(100),
	@modifiedBy AS NVARCHAR(10),
	@modifiedDate AS DATETIME,
	@testID AS INT
AS
	UPDATE Test 
		SET Code = @code, 
		Name = @name, 
		Description = @description,
		ModifiedBy = @modifiedBy,
		ModifiedDate = @modifiedDate
	WHERE TestID = @testID;
RETURN 0
GO

TestDelete

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestDelete]
	@testID AS INT
AS
	DELETE
	FROM dbo.[Test]
        WHERE TestID = @testID;
RETURN 0
GO

TestSearch

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestSearch]
AS
	SELECT *
	FROM dbo.[Test];
RETURN 0
GO