same table for two applications

Question

Hi,

 im using two tables  for two diferent   applications because fields in both applications are common. except in one application has one column extra ClientID

below are two tables

CREATE TABLE [dbo].[EmployeeTSDates](
	[TSID] [int] IDENTITY(1,1) NOT NULL,
	[ClientID] [int] NULL,
	[EmployeeName] [varchar](50) NULL,
	[TSStartDate] [datetime] NULL,
	[TSEndDate] [datetime] NULL,
 CONSTRAINT [PK_EmployeesTable]





CREATE TABLE [dbo].[EmpTimesheet](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientID] [int] NULL,
	[EmpID] [int] NOT NULL,
	[EmpName] [varchar](50) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[day] [varchar](50) NULL,
	[date] [datetime] NULL,
	[Service] [nvarchar](max) NULL,
	[Venue] [varchar](50) NULL,
	[Project] [varchar](50) NULL,
	[StartTime] [varchar](50) NULL,

In one applicaion i dont need ClientID so CLientID isalways empty  but in another application it has ClientID =32

below storeprocedure for  without clientid

ALTER PROCEDURE [dbo].[Sp_EmployeeTSDates_InsertGet]@empName varchar(50),
	@tsStartDate datetime,
	@tsEnddate datetime  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
if exists
	(SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate)
begin 
SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate
end
else
begin 
insert into EmployeeTSDates (Employeename, TSStartDate, TSEndDate) values (@empName, @tsStartDate, @tsEnddate)
SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate

end



END

below procedure wih client ID

ALTER PROCEDURE [dbo].[Sp_AEmployeeTSDates_InsertGet]
	-- Add the parameters for the stored procedure here
	@empName varchar(50),
@clientId int,
	@tsStartDate datetime,
	@tsEnddate datetime  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
if exists
	(SELECT TSID ,ClientID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate)
begin 
SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate
end
else
begin 
insert into EmployeeTSDates (Employeename,ClientID, TSStartDate, TSEndDate) values (@empName,@clientId, @tsStartDate, @tsEnddate)
SELECT TSID,ClientID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate

end



END

my issue is  do i  need to create another  two more tables(emptimesheet1,emtsdates2) for without clientID application

Answer 1

What is the problem you are facing the in the current approach?

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 2

To specific to you procedure, I could not find any issues. First of all you are not using ClientID in your both of the procedures as predicate or search argument.

Now, there would be some other functionality using the Client ID in your application whcih is not a scope of your question, but thats very important.

How many records would be there with ClientID as NULL and Not NULL?

Do you have any index on the Client ID?

Answer 3

Hi,

In my application employess dont have id numbers so i created timesheetid(TSID) is unique id  .when user enters Name and startdate then tsid is created autocrement  for timesheet1 application.timesheet2 application is also following same procedure but they have clientid.but there is problem any user enters name is common two times dates are not possible.In my application some employees work for  more than one project . both timesheets are for two different oranizations.so same employee works for oranization1 and oranization2 .

Ex:angie works for organization1  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000//it creates one id

angie works for organization2  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000    with clientid =32 // i want to  create another id with same dates

TSID   ClientID name
21	NULL	angie	2014-02-16 00:00:00.000	2014-02-22 00:00:00.000
22	NULL	Angie	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
23	NULL	Alex	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
24	32	test2	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
25	32	test3	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000

Answer 4

Ex:angie works for organization1  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000//it creates one id

angie works for organization2  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000    with clientid =32 // i want to  create another id with same dates

TSID   ClientID name
21	NULL	angie	2014-02-16 00:00:00.000	2014-02-22 00:00:00.000
22	NULL	Angie	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
23	NULL	Alex	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
24	32	test2	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000
25	32	test3	2014-02-02 00:00:00.000	2014-02-08 00:00:00.000

Hi hemaak,

According to your description, you want to use two store procedures  to insert the records about employee. When a user enters name , startdate, enddata  then tsid is created auto-increase  for timesheet1 application;   When a user enters name, clientid, startdate, enddata  then tsid is created auto-increase  for timesheet2 application. If it need two more tables for different application. Personally, I recommend you can use one table about timesheet. Just take the dbo.EmployeeTSDates as an example.

1. you can use the store procedure of [dbo].[Sp_EmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is null.

2. Then use the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is not null.

You should to note that there is some modification about the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet], you can refer to the following statement.

if exists              
  (SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate and clientId =@clientId)
Begin..

3. You can return the sub-table for different application via the following statement.

With application1 as
(select * from dbo.EmployeeTSDates where ClientID is not null);
With application2 as
(select * from dbo.EmployeeTSDates where ClientID is null);

Regards,
Sofiya Li

---

Sofiya Li
TechNet Community Support