function inside the procedure error

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

SQL Server 2000 Forums

Transact-SQL (2000)

function inside the procedure error

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-01-30 : 10:44:44

Hi,
i try to write procedure for insert with auto generation number of
employee id ,below i have attach the code and also the error message which i got
error1 'NextCustomerNumber' is not a recognized function name.
error2 Incorrect syntax near ')'.
CREATE Procedure addcustomer
(
@EMPName nvarchar(255),
@PASSWORD nvarchar(25),
@EMAILID nvarchar(50),
@DESIGNATION nvarchar(50),
)
AS

INSERT INTO EMPLOYEE
(
empid,
empname,
passwords,
emailid,
designation
)

VALUES
(
NextCustomerNumber(),
@EMPName,
@PASSWORD,
@EMAILID,
@DESIGNATION
)

create function NextCustomerNumber()
returns varchar(6)
as
begin
declare @lastval varchar(6)
set @lastval = (select max(empid) from employee)
if @lastval is null set @lastval = 'EM0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'EM' + right('000' + convert(varchar(10),@i),4)
end

Desikankannan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 10:54:47

Create the function first, to store the function in the database.

create function NextCustomerNumber() 
returns varchar(6) 
as 
begin 
declare @lastval varchar(6) 
set @lastval = (select max(empid) from employee) 
if @lastval is null set @lastval = 'EM0001' 
declare @i int 
set @i = right(@lastval,4) + 1 
return 'EM' + right('000' + convert(varchar(10),@i),4) 
end

AND NOW YOU create the stored procedure.

CREATE Procedure addcustomer
(
@EMPName nvarchar(255),
@PASSWORD nvarchar(25),
@EMAILID nvarchar(50),
@DESIGNATION nvarchar(50), 
)
AS

INSERT INTO EMPLOYEE
(
empid,
empname,
passwords,
emailid, 
designation
)
select 
NextCustomerNumber(), 
@EMPName,
@PASSWORD,
@EMAILID,
@DESIGNATION

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-30 : 10:54:59

What does the below code results into:

Select dbo.NextCustomerNumber()
Select NextCustomerNumber()

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-30 : 11:00:34

Even though that is correct order Peter, I doubt that's the problem with OP's case.

desikankannan,

Just try prefixing your function name with dbo and see whether you still face error:

INSERT INTO EMPLOYEE
(
empid,
empname,
passwords,
emailid,
designation
)

VALUES
(
dbo.NextCustomerNumber(),
@EMPName,
@PASSWORD,
@EMAILID,
@DESIGNATION
)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources