no insertion happining.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

no insertion happining....

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-05-19 : 03:15:15

-- EXEC INSERT_EMPLOYEE_DETAIL 'CL03057','Mr.','MAU','CL','VAV','infotech','15','mb@megroup.com','4237' ,'4238','','',''

alter PROCEDURE INSERT_EMPLOYEE_DETAIL
(
@ECODE VARCHAR(10),
@PREF VARCHAR(5) ,
@ENAME VARCHAR(50),
@COMPANY_NAME varchar(20),
@LOCATION_NAME varchar(50),
@DEPARTMENT_NAME varchar(50),
@DEPT_CODE VARCHAR(20),
@EMAIL_ID VARCHAR(50)
@OFFICENO1 VARCHAR(20),
@OFFICENO2 VARCHAR(20),
@RESIDENCE VARCHAR(20),
@CELLNO1 VARCHAR(20),
@CELLNO2 VARCHAR(20)
--@Admin_Userid VARCHAR(50)
--@CATEGORY VARCHAR(20)
)

AS
BEGIN

DECLARE @CATEGORY VARCHAR(20),
@LOCCODE VARCHAR(50)

if @Ecode = '' or @Ecode = NULL
begin
SELECT @LOCCODE =

CASE
WHEN @LOCATION_NAME = 'VAV' THEN 'CL'
WHEN @LOCATION_NAME = 'bad' THEN 'HM'
WHEN @LOCATION_NAME = 'roda' THEN 'RD'
WHEN @LOCATION_NAME = 'gadh' THEN 'NG'
WHEN @LOCATION_NAME = 'Mosa' THEN 'CL'
WHEN @LOCATION_NAME = 'Mu' THEN 'UM'
WHEN @LOCATION_NAME = 'kot' THEN 'JT'
WHEN @LOCATION_NAME = 'rat' THEN 'RT'
WHEN @LOCATION_NAME = 'Bgar' THEN 'VN'
END

select @Ecode = (SELECT (MAX(SR_NO) + 1) FROM SCL_EMPLOYEE_MASTER)
SELECT @ECODE = (SELECT @LOCCODE+@ECODE)
SELECT @CATEGORY = 'UNEMPLOYED'

end
else
begin
SELECT @LOCCODE =
CASE
WHEN @LOCATION_NAME = 'VAV' THEN 'CL'
WHEN @LOCATION_NAME = 'bad' THEN 'HM'
WHEN @LOCATION_NAME = 'roda' THEN 'RD'
WHEN @LOCATION_NAME = 'gadh' THEN 'NG'
WHEN @LOCATION_NAME = 'Mosa' THEN 'CL'
WHEN @LOCATION_NAME = 'Mu' THEN 'UM'
WHEN @LOCATION_NAME = 'kot' THEN 'JT'
WHEN @LOCATION_NAME = 'rat' THEN 'RT'
WHEN @LOCATION_NAME = 'Bgar' THEN 'VN'
END
SELECT @CATEGORY = 'EMPLOYED'
end

select @LOCATION_NAME = ltrim(rtrim(@LOCATION_NAME))
select @DEPARTMENT_NAME = ltrim(rtrim(@DEPARTMENT_NAME))
select @COMPANY_NAME = ltrim(rtrim(@COMPANY_NAME))

set nocount on

INSERT INTO SCL_EMPLOYEE_MASTER
(
ECODE,PREF,ename,CCode,LOCN_CODE,Dept_Code,EmailID,CATEGORY
)--,Admin_Userid)
SELECT @ECODE,@PREF,@ENAME,A.CCODE,B.LOCN_CODE,C.DEPT_CODE,@EMAIL_ID,@CATEGORY
FROM SCL_COMPANY_MASTER A,SCL_LOCATION B, SCL_DEPARTMENT C --,,@Admin_Userid
WHERE C.DEPT_NAME = @DEPARTMENT_NAME
AND A.CNAME = @COMPANY_NAME
AND B.LOCN_NAME = @LOCATION_NAME

DECLARE @MAXSR VARCHAR(10)
SELECT @MAXSR = MAX(Sr_No)FROM SCL_EMPLOYEE_MASTER

INSERT INTO SCL_PHONENO(CCode,LOCN_CODE,DEPT_CODE,ECode,OfficeNo1,OfficeNo2,ResidanceNo,CellNo1,CellNo2)
SELECT A.CCODE,A.LOCN_CODE,A.DEPT_CODE,A.ECODE,@OFFICENO1,@OFFICENO2,@RESIDENCE,@CELLNO1,@CELLNO2

FROM SCL_EMPLOYEE_MASTER A
WHERE A.ENAME = @ENAME
AND A.Sr_No = @MAXSR

set nocount off
END

/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SCL_EMPLOYEE_MASTER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SCL_EMPLOYEE_MASTER]
GO

CREATE TABLE [dbo].[SCL_EMPLOYEE_MASTER] (
[Sr_No] [int] IDENTITY (1, 1) NOT NULL ,
[ECode] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[PREF] [varchar] (5) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[EName] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CCode] [int] NULL ,
[Locn_Code] [int] NULL ,
[Dept_Code] [int] NULL ,
[EmailID] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[Category] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[Admin_Userid] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO

*/

/*if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SCL_PhoneNo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SCL_PhoneNo]
GO

CREATE TABLE [dbo].[SCL_PhoneNo] (
[emp_code] [varchar] (6) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CCode] [int] NULL ,
[Locn_Code] [int] NULL ,
[Dept_Code] [int] NULL ,
[ECode] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
[OfficeNo1] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[OfficeNo2] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[ResidanceNo] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CellNo1] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CellNo2] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO

*/

--select * from SCL_EMPLOYEE_MASTER

/*
please help me to solve above problem......

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-19 : 05:52:57

Your description is too vague for any serious effort at assistance.

What error message if any are you getting?
Can you break down the procedure and identify what bits are definitely working?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources