Docstoc

Transact SQL

Document Sample
Transact SQL Powered By Docstoc
					Transact SQL



     Stored Procedures
Procedure to Create
CREATE PROCEDURE TableName_CreateRecord
@value_1 type_1,
@value_2 type_2

AS

     INSERT INTO TableName(column_1, column_2)
     VALUES (@value_1, @value_2)

     IF @@Error =0
          SELECT SCOPE_IDENTITY()
     ELSE
          SELECT -1
GO
Procedure to Delete
CREATE PROCEDURE TableName_DeleteRecord
@columnID type_1,

AS

     DELETE from TableName
     WHERE columnID = @columnID

     IF @@Error =0
          SELECT 1
     ELSE
          SELECT -1
GO
Procedure to Update
CREATE PROCEDURE TableName_UpdateRecord
@column_ID type_ID,
@value_1 type_1,
@value_2 type_2

AS
     UPDATE TableName SET
     column_1 = @value_1,
     column_2 = @value_2
     WHERE column_ID = @column_ID

     IF @@Error =0
          SELECT 1
     ELSE
          SELECT -1
GO
Procedure to Retrieve

CREATE PROCEDURE TableName_RetrieveRecord
@column_ID type_ID

AS
     SELECT * FROM TableName
     WHERE column_ID = @column_ID

GO
Procedure to Retrieve (interval)

CREATE PROCEDURE TableName_RetrieveRecordsByDates
@start_date type_ID,
@end_date type_ID

AS
     SELECT * FROM TableName
     WHERE column_date BETWEEN @start_date AND @end_date

GO

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:10/3/2011
language:English
pages:6