შევქმნათ სატესტო ცხრილი :
BB კოდი:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
DROP TABLE [dbo].[Persons]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Persons](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](150) NULL,
[LastName] [nvarchar](150) NULL,
[PersonalNumber] [nvarchar](50) NULL,
[Gender] [int] NULL,
[DOB] [datetime] NULL,
[CitizenshipID] [int] NULL,
[RegAddress] [nvarchar](250) NULL,
[Address] [nvarchar](250) NULL,
CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- დავაგენერიროთ ამ ცხრილისთვის პროცედურა
BB კოდი:EXEC [dbo].[GenerateIUDGscripts_tsp] @TBL = 'Persons'
-- რეზულტატი
-- =============================================
-- DROP PROCEDURES FOR tsp
-- OF TABLE Persons
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tsp_Persons]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[tsp_Persons]
GO
-- =============================================
GO
-- =============================================
-- STORED PROCEDURE TO INSERT(0),UPDATE(1),DELETE(2),GET(3x)
-- THE TABLE Persons
-- GENERATED BY : APEX GROUP
-- GENERATED ON :Aug 26 2011 2:31PM
-- =============================================
CREATE PROCEDURE tsp_Persons
(
@iud tinyint = 3, -- INSERT(0),UPDATE(1),DELETE(2),GET(3x)
@PersonID [integer] = null OUTPUT,
@FirstName [nvarchar](300) = null,
@LastName [nvarchar](300) = null,
@PersonalNumber [nvarchar](100) = null,
@Gender [integer] = null,
@DOB datetime = null,
@CitizenshipID [integer] = null,
@RegAddress [nvarchar](500) = null,
@Address [nvarchar](500) = null
)
AS
IF @iud between 0 and 1 -- INSERT OR UPDATE ACTION
BEGIN
DECLARE @RetVal TABLE
(
[PersonID] [integer]
);
IF @iud = 0 -- INSERT
INSERT INTO Persons
(
[FirstName],
[LastName],
[PersonalNumber],
[Gender],
[DOB],
[CitizenshipID],
[RegAddress],
[Address]
)
OUTPUT INSERTED.[PersonID] INTO @RetVal
VALUES
(
@FirstName,
@LastName,
@PersonalNumber,
@Gender,
@DOB,
@CitizenshipID,
@RegAddress,
@Address
)
IF @iud = 1 -- UPDATE
UPDATE Persons SET
[FirstName] = @FirstName,
[LastName] = @LastName,
[PersonalNumber] = @PersonalNumber,
[Gender] = @Gender,
[DOB] = @DOB,
[CitizenshipID] = @CitizenshipID,
[RegAddress] = @RegAddress,
[Address] = @Address
OUTPUT INSERTED.[PersonID] INTO @RetVal
WHERE
[PersonID] = @PersonID
SELECT TOP (1) @PersonID = [PersonID] FROM @RetVal
END
ELSE
BEGIN
IF @iud = 2 -- DELETE
DELETE FROM Persons
WHERE ([PersonID] = @PersonID)
END
IF @iud = 3 -- GET
SELECT
[PersonID],
[FirstName],
[LastName],
[PersonalNumber],
[Gender],
[DOB],
[CitizenshipID],
[RegAddress],
[Address]
FROM Persons
WHERE
[PersonID] = @PersonID OR @PersonID is null
GO
File Attachment(s):
GenerateIUDGscripts_tsp.txt
(20kb) downloaded 7 time(s).