How can execute this procedure..

Question

1.00/5 (1 vote)

See more:

CREATE Procedure Proc_PutEmp_Mst_type
@strParam varchar(2000),
@strMsgCode varchar(10) output
AS
BEGIN
declare @modifier varchar(25)
DECLARE @ECODE VARCHAR(10)
DECLARE @NAME VARCHAR(50)
DECLARE @GRADE VARCHAR(10)
DECLARE @QUALI VARCHAR(30)
DECLARE @ADD1 VARCHAR(50)
DECLARE @ADD2 VARCHAR(50)
DECLARE @ADD3 VARCHAR(50)
DECLARE @ADD4 VARCHAR(50)
DECLARE @PIN VARCHAR(10)
DECLARE @PHONE VARCHAR(20)
DECLARE @FAX VARCHAR(20)
DECLARE @DESC VARCHAR(50)
DECLARE @DOB varchar(20)
DECLARE @DOJ varchar(20)
DECLARE @DOW varchar(20)
DECLARE @EMAIL VARCHAR(50)
DECLARE @DESIG VARCHAR(10)

DECLARE @BNKCODE VARCHAR(50)
DECLARE @ACCNO VARCHAR(50)
DECLARE @Mode int
DECLARE @CNT INT
DECLARE @INTPOS INT
DECLARE @REMAIN VARCHAR(2000)
SET @REMAIN=@STRPARAM
DECLARE @DOBd datetime
DECLARE @DOJd datetime
DECLARE @DOWd datetime
declare @doc datetime
declare @dor datetime
declare @docd datetime
declare @dord datetime
declare @title varchar(6)
declare @sex char(3)
declare @deptment varchar(10)
declare @location varchar(10)
declare @offph varchar(20)
declare @offExten varchar(20)
declare @emailpers varchar(50)
declare @role varchar(10)
DECLARE @PARENT VARCHAR(10)
DECLARE @JObDesc VARCHAR(50)
DECLARE @HQAddress VARCHAR(200)
Declare @Padd1 VARCHAR(200)
Declare @Padd2 VARCHAR(200)
Declare @Padd3 VARCHAR(200)
Declare @Padd4 VARCHAR(200)
Declare @madd1 VARCHAR(200)
Declare @madd2 VARCHAR(200)
Declare @madd3 VARCHAR(200)
Declare @madd4 VARCHAR(200)
Declare @branchname VARCHAR(100)
Declare @rtgs VARCHAR(20)
Declare @hpin VARCHAR(20)
Declare @ppin VARCHAR(20)
Declare @mpin VARCHAR(20)
Declare @emptype INT
declare @pan varchar(10)
Declare @autoemail varchar(100)
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @title=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @sex=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @deptment=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @location=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @offph=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @offExten=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @doc=convert(datetime,SUBSTRING(@REMAIN,1,@INTPOS-1),103)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @dor=convert(datetime,SUBSTRING(@REMAIN,1,@INTPOS-1),103)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @emailpers=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @role=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @modifier=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ECODE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @NAME=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @GRADE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @QUALI=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ADD4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PIN=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PHONE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @FAX=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DESC=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOB=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOJ=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @DOW=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @email=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @desig=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))

SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @BNKCODE=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))

SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ACCNO=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Mode=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @PARENT=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @JObDesc=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @HQAddress=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @Padd4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd1=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd2=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd3=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @madd4=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @branchname =SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @rtgs=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @hpin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @ppin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
-- SET @INTPOS=CHARINDEX('»',@REMAIN)
-- SET @emptype=SUBSTRING(@REMAIN,1,@INTPOS-1)
-- SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
--
-- SET @INTPOS=CHARINDEX('»',@REMAIN)
-- SET @mpin=@REMAIN
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @mpin=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @emptype=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @pan=SUBSTRING(@REMAIN,1,@INTPOS-1)
SET @REMAIN=SUBSTRING(@REMAIN,@INTPOS+1,LEN(@REMAIN))
SET @INTPOS=CHARINDEX('»',@REMAIN)
SET @autoemail=@REMAIN
if @doc=''
begin
set @docd=NULL
end
else
begin
set @docd=convert(datetime,@doc,103)
end
if @dor=''
begin
set @dord=NULL
end
else
begin
set @dord=convert(datetime,@dor,103)
end
IF @DOj=''
BEGIN
set @DOjd=NULL
END
else
begin
set @DOjd=convert(datetime,@DOj,103)
end
IF @DOb=''
BEGIN
set @DObd=NULL
END
else
begin
set @DObd=convert(datetime,@DOb,103)
end
IF @DOw=''
BEGIN
set @DOwd=NULL
END
else
begin
set @DOwd=convert(datetime,@DOw,103)
end
select @Mode
if @Mode = 0
BEGIN
set @cnt=(SELECT COUNT(*) FROM TBL_Emp_MST WHERE c_Empcode = @Ecode)
IF @cnt=0
BEGIN
INSERT INTO
Tbl_Emp_Mst
(
c_Empcode,
C_name,
c_Grade,
c_Qualification,
c_Add_1,
c_Add_2,
c_Add_3,
c_Add_4,
c_pin,
c_mobile_no,
c_Fax,
c_desc,
D_DOB,
D_DOj,
D_DOW,
c_email_office,
c_desig,
c_bankcode,
C_Accno,
n_deleted,
d_created,
c_modifier,
d_modified,
c_nameprefix,c_sex,c_dept_code,c_loc_code,c_office_phone_no,c_office_phone_extnno,c_email_personal,c_role,
d_conf_date,d_relieve_date,c_jobdesc,c_hq_address ,c_padd_1, c_padd_2,c_padd_3,c_padd_4 ,C_MAdd_1,C_MAdd_2,C_MAdd_3,C_MAdd_4
,c_branch_name,c_rtgs_neft,c_hq_pin,c_padd_pin ,c_madd_pin,c_emp_type,c_pan,c_auto_report_email
)
VALUES
(
@ECODE,
@NAME,
@GRADE,
@QUALI,
@ADD1,
@ADD2,
@ADD3,
@ADD4,
@PIN,
@PHONE,
@FAX,
@DESC,
@DOBd,
@DOJd,
@DOWd,
@EMAIL,
@desig,

@BNKCODE,
@ACCNO,
0,
getdate(),
@modifier,
getdate(),
@title,@sex,@deptment,@location,@offph,@offExten,@emailpers,@role,
@docd,@dord,@JObDesc,@HQAddress , @Padd1,@Padd2,@Padd3,@Padd4,@madd1,@madd2,@madd3,@madd4
,@branchname, @rtgs, @hpin,@ppin,@mpin ,@emptype,@pan,@autoemail
)
INSERT INTO
Tbl_Emp_parent_fs
(
c_Empcode,
C_parentCode
)
values
(
@ECODE,@PARENT
)
END
ELSE
BEGIN
SET @strMsgCode='3001'
RETURN
END
END
if @Mode = 1
BEGIN IF (SELECT COUNT(*) FROM Tbl_Emp_Mst WHERE c_empcode = @ecode ) = 1
BEGIN
UPDATE
Tbl_Emp_Mst
SET
C_NAME=@NAME,
C_Grade=@GRADE,
C_Qualification=@QUALI,
C_Add_1=@ADD1,
C_Add_2=@ADD2,
C_Add_3=@ADD3,
C_Add_4=@ADD4,
C_Pin=@PIN,
C_mobile_no=@PHONE,
C_Fax=@FAX,
C_Desc=@DESC,
D_DOB=@DOBd,
D_DOJ=@DOJd,
D_DOW=@DOWd,
C_Email_office=@EMAIL,
c_desig=@desig,

c_bankcode=@BNKCODE,
C_Accno=@ACCNO,
c_modifier=@modifier,
c_padd_1=@Padd1,
c_padd_2=@Padd2,
c_padd_3=@Padd3,
c_padd_4=@Padd4,
c_madd_1=@madd1,
c_madd_2=@madd2,
c_madd_3=@madd3,
c_madd_4=@madd4,
c_branch_name=@branchname,
c_rtgs_neft=@rtgs,
c_hq_pin=@hpin,
c_padd_pin=@ppin,
c_madd_pin=@mpin ,
c_emp_type=@emptype,
d_modified=getdate(),c_nameprefix=@title,c_sex=@sex,c_dept_code=@deptment,c_loc_code=@location,
c_office_phone_no=@offph,c_office_phone_extnno=@offExten,c_email_personal=@emailpers,c_role=@role,
d_conf_date=@docd,d_relieve_date=@dord,c_jobdesc=@JObDesc,c_hq_address= @HQAddress ,
c_pan=@pan ,c_auto_report_email=@autoemail
WHERE
c_EMpcode = @ecode
IF (SELECT COUNT(*) FROM Tbl_Emp_parent_fs WHERE c_empcode = @ecode ) = 1
BEGIN
update Tbl_Emp_parent_fs set C_parentCode = @PARENT where c_Empcode = @ECODE
END
Else
Begin
INSERT INTO
Tbl_Emp_parent_fs
(
c_Empcode,
C_parentCode
)
values
(
@ECODE,@PARENT
)
End
SET @strMsgCode='3002'
RETURN
END
ELSE
BEGIN
SET @strMsgCode='3001'
RETURN
END
END
IF @@ERROR<>0
BEGIN SET @strMsgCode='9000'
END
ELSE
BEGIN
SET @strMsgCode='3000'
END
end

Posted 29-Dec-14 19:54pm

Member 11337367

Add a Solution

Comments

Member 11337367 30-Dec-14 6:09am

No..it is one procedure i want to execute in sql 2008..

[no name] 30-Dec-14 6:46am

There is a command "EXEC ProcedureName Param1,Param2".
You can use this command to execute your procedure. What problem you are facing with this?

Member 11337367 30-Dec-14 7:06am

I was getting error while executing
Invalid length parameter passed to the LEFT or SUBSTRING function.

[no name] 30-Dec-14 7:49am

I think there is an issue with the SUBSTRING() function. You can able to check the line number and findout the values that you are passing to the SUBSTRING(). In that way you can only be able to findout the issue. For displaying the values you can use the Print statement. Just like response.write you need to put the print statement there in the stored procedure and check whewre is the issue.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BechirBenltaief · Answer 1 · 2014-12-29T23:58:00

Solution 1

hi,

In what language will you ask for the result ?
I mean; will you use it in C# ?

Bechir.

Posted 29-Dec-14 23:58pm

BechirBenltaief