| Author |
 Topic  |
|
|
Indrajit
Starting Member
India
11 Posts |
 Posted - 06/13/2012 : 05:33:53
|
Hi All,
I have to create a Store Procedure where I am taking user input as Customer Model No, Customer Order No, Order model and Order Qty.
Now I have to check in my database this Customer Model No, Customer Order No, Order model existed if it is not then I have to create like below
Foe New Customer, Input value is-
Customer Model No - A001
Customer Order No - Ord200
Order model - 311A
Order Qty -10
so here my Store Procedure should generate like 311A-01,311A-02,311A-03,...........,
311A-09,311A-10 and store in Data Base
<pre lang="sql">CREATE PROCEDURE OrderNo @CusModlNo nchar, @CusOrdNo nchar, @OrderModelNo nchar, @OrderQty int
AS
BEGIN
Declare @OrderNo int
END
GO
</pre>
Thanks
IndrajitDasgupta |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/13/2012 : 05:37:30
|
CREATE PROCEDURE OrderNo
@CusModlNo nchar, @CusOrdNo nchar, @OrderModelNo nchar, @OrderQty int
as
insert tbl
select ...
where not exists (select * from tbl where CusModlNo = @CusModlNo and CusOrdNo = @CusOrdNo and OrderModelNo = @OrderModelNo)
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
Indrajit
Starting Member
India
11 Posts |
Posted - 06/13/2012 : 05:42:20
|
Thanks for reply This is ok but how can I generate OrderNo? Can U please give details
IndrajitDasgupta |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 06/20/2012 : 08:45:32
|
1 Always specify the length for your variables - Refer this for reason http://beyondrelational.com/modules/2/blogs/70/posts/10824/column-length-and-data-length.aspx
2 Use this logic
declare @OrderModelNo varchar(10), @OrderQty int
select @OrderModelNo ='311A',@OrderQty =10
select @OrderModelNo+'-'+replace(str(number,2),' ','0') from master..spt_values
where type='p' and number between 1 and @OrderQty
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
Store Procedure
