stored procedures

[ayshonscripts]

hey guys,
i've jus started working with procedures.. I'm having a form with several controls say, date, name, adress,time.. etc.. etc.. once i eneter values in these fields n press save, all the values get saved in the database..a procedure has been written for doing this n everything is fine till now..

But ther's one more field called 'customer no', which shud auto-generate the value :CN1 for 1st customer, n CN2,CN3 so on for successive customers.. how shud i go abt this?? somebody plzz help :(

[jai80]

hey guys,
i've jus started working with procedures.. I'm having a form with several controls say, date, name, adress,time.. etc.. etc.. once i eneter values in these fields n press save, all the values get saved in the database..a procedure has been written for doing this n everything is fine till now..

But ther's one more field called 'customer no', which shud auto-generate the value :CN1 for 1st customer, n CN2,CN3 so on for successive customers.. how shud i go abt this?? somebody plzz help :(

hi there,

CREATE Procedure Sp_InserCustomerDetails
@Custname varchar (20)
as
declare
@Result int,
@count int,
@CustId varchar (20)

select @count=count(subrowid)+1 from TblCustomers
set @CustId='CN'+RIGHT(CONVERT(varchar(10),@count),1)

IF EXISTS ( SELECT CustId FROM TblCustomers where CustId=@CustId )
BEGIN

SET @Result = 1
RETURN @Result
END

Begin

insert into TblCustomers(CustId,Custname) values(@CustId,@Custname)
End
SET @Result = 0
RETURN @Result
Go

here 'subrowid' is the identity field of the table.

Try this StoredProcedure,it doesthe work of auto-generation of CustomerID's. The Right() function extracts the string from the starting position specified by its 2nd paramerter.
RIGHT ( character_expression , integer_expression ). Refer SQL Server Books Online. GudLuck!

Cheers,
jai

[iburyak]

To create automatically generated ID do following

1. Create table with identity column

[PHP]Create table CustomerInformation(
CusID int identity(1,1),
Name varchar(50) null,
Address varchar(100) null,
Date datetime default getdate())[/PHP]

2. Create stored procedure to accept parameters.
Don’t mention identity column in insert statement it will be generated automatically.


[PHP]Create proc InsertCustomerInformation
@Name varchar(50),
@Address varchar(100),
@Date datetime
AS

Insert into CustomerInformation ( Name, Address, Date)
values (@Name, @Address, @Date)
go[/PHP]

3. You can omit datetime as well it will be automatic as well the way I set it.

[PHP]Create proc InsertCustomerInformation
@Name varchar(50),
@Address varchar(100),
@Date datetime
AS

Insert into CustomerInformation ( Name, Address)
values (@Name, @Address)
go[/PHP]

3. Execute stored proc and see results

[PHP]Execute CustomerInformation 'ayshonscripts', '111 Some address', '02/11/07')[/PHP]

[iburyak]

hey guys,
i've jus started working with procedures.. I'm having a form with several controls say, date, name, adress,time.. etc.. etc.. once i eneter values in these fields n press save, all the values get saved in the database..a procedure has been written for doing this n everything is fine till now..

But ther's one more field called 'customer no', which shud auto-generate the value :CN1 for 1st customer, n CN2,CN3 so on for successive customers.. how shud i go abt this?? somebody plzz help :(

To create automatically generated ID do following

1. Create table with identity column

[PHP]Create table CustomerInformation(
CusID int identity(1,1),
Name varchar(50) null,
Address varchar(100) null,
Date datetime default getdate())[/PHP]

2. Create stored procedure to accept parameters.
Don’t mention identity column in insert statement it will be generated automatically.


[PHP]Create proc InsertCustomerInformation
@Name varchar(50),
@Address varchar(100),
@Date datetime
AS

Insert into CustomerInformation ( Name, Address, Date)
values (@Name, @Address, @Date)
go[/PHP]

3. You can omit datetime as well it will be automatic as well the way I set it.

[PHP]Create proc InsertCustomerInformation
@Name varchar(50),
@Address varchar(100),
@Date datetime
AS

Insert into CustomerInformation ( Name, Address)
values (@Name, @Address)
go[/PHP]

3. Execute stored proc and see results

[PHP]Execute CustomerInformation 'ayshonscripts', '111 Some address', '02/11/07')[/PHP]