function inside in the procedure

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 in the procedure

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-01-29 : 11:51:14

i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid
mmy question how to put function inside the procedure?
my function below,pls guide me its very urgent.
create function NextCustomerNumber()
returns char(5)
as
begin
declare @lastval char(5)
set @lastval = (select max(customerNumber) from Customers)
if @lastval is null set @lastval = 'C0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)
end

Desikankannan

Desikankannan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-29 : 14:13:18

quote:
mmy question how to put function inside the procedure?

First you have to know how to call your function.
Test this:
select dbo.NextCustomerNumber()

If that returns the expected value then you have to know how you will implement the call.

Perhaps like this:
insert MyTable (CustomerNumber, <OtherColumns>)
values (dbo.NextCustomer(), <OtherValues>)

Final thing is to create the stored procedure:
------------------------------------------
Create Procedure MyStoreProcedure
as

<Your insert Statement>

go
------------------------------------------

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources