Editable GridView without ObjectDataSource Concepts

��

GO

/****** Object:  Table [dbo].[tPhysician]    Script Date: 06/08/2009 16:58:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tPhysician](

	[PhysicianID] [int] IDENTITY(1,1) NOT NULL,

	[HospitalID] [int] NOT NULL,

	[HospitalPhysicianID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

	[Suffix] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

	[IsActive] [bit] NOT NULL CONSTRAINT [DF_tPhysician_Active]  DEFAULT ((1)),

 CONSTRAINT [PK_tPhysician] PRIMARY KEY CLUSTERED 

(

	[PhysicianID] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



GO

SET ANSI_PADDING OFF

GO



CREATE PROCEDURE [dbo].[DeletePhysician]

	(@PhysicianID int)

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	UPDATE tPhysician

	SET IsActive = 0

	WHERE PhysicianID = @PhysicianID

	



END





GO



CREATE PROCEDURE [dbo].[getPhysicianList]

	(@TestVersion bit, @HospitalID int)

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	SELECT * FROM tPhysician

	WHERE HospitalID = @HospitalID

	AND IsActive = 1



END



GO



CREATE PROCEDURE [dbo].[InsertPhysician]

	(@HospitalID int, @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @HospitalPhysicianID varchar(50), @Suffix varchar(10))

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



	IF EXISTS (SELECT NULL 

		FROM tPhysician 

		WHERE HospitalID = @HospitalID

		AND IsActive = 1

		AND HospitalPhysicianID =  @HospitalPhysicianID)

	begin	

		DECLARE @ErrorMessage NVARCHAR(4000);



		select @ErrorMessage = 'Physician ID ' + @HospitalPhysicianID + ' already exists in the table.  Duplicates are not allowed.';

		RAISERROR (@ErrorMessage, -- Message text.

					   16, -- Severity.

					   1 -- State.

					   );

		

	end

	else

	begin

		-- Insert statements for procedure here

		INSERT INTO tPhysician (HospitalID, HospitalPhysicianID, FirstName, MiddleName, LastName, Suffix)

		VALUES (@HospitalID, @HospitalPhysicianID, @FirstName, @MiddleName, @LastName, @Suffix)

	end



END



GO





CREATE PROCEDURE [dbo].[UpdatePhysician]

	(@PhysicianID int, @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @HospitalPhysicianID varchar(50), @Suffix varchar(10))

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    -- Insert statements for procedure here

	UPDATE tPhysician 

		SET HospitalPhysicianID = @HospitalPhysicianID,

		FirstName = @FirstName, 

		MiddleName = @MiddleName, 

		LastName = @LastName,

		Suffix = @Suffix

	WHERE PhysicianID = @PhysicianID



END



GO



CREATE PROCEDURE [dbo].[IsHospitalPhysician]

	(@TestVersion bit, @HospitalPhysicianID varchar(50), @HospitalID int)

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



	IF EXISTS (SELECT NULL 

		FROM tPhysician 

		WHERE HospitalID = @HospitalID

		AND IsActive = 1

		AND HospitalPhysicianID =  @HospitalPhysicianID)

	begin	

		SELECT 1 as IsExisting

	end

	else	

	begin

		SELECT 0 as IsExisting

	end



END