Store Procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Store Procedure

Author

Topic

Indrajit
Starting Member

11 Posts

Posted - 2012-06-13 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 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

11 Posts

Posted - 2012-06-13 : 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

22864 Posts

Posted - 2012-06-20 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources