Need solution for the query in Stored procedure

See more:

SQL-Server-2005

SQL-Server

I have a query in sql server 2005 to write a SP , pls help me to solve the same , Thanks



1. Select all the market codes from TBL_MARKET table
2. For each market code, select the Order Number and Date from Order Master table for all the Orders where date is same as parameter date
3. If there are no Order for a particular marketcode, select the next market from the market master table and start again from step 1. Otherwise, insert the Date and market code in the Tax Register Master table as per below mentioned logic

I want to create a procedure (SP) based on the follwoing parameter on the tables as , pls help me in this

There is OrderTable and Orderdetail and OrderRegsiter Tables and OrderCheck

OrderCheck table has follwowing column
Order_code | Order_name | region_code | VAT_NO | Order_active | userId

OrderTable is the master table having following coloumn

Order_number | Order_code | Order_number | Order_date | Order_status |


This procedure will populate the OrderRegsiter tables ( OrderRegsiter and Orderdetail) for all the Orders , which are generated on the particular date

parameter as

1. Order Code: Market for which the Order register is generated. The default value for the parameter will be -1, which means that the register will be generated for all the markets.
2. Date: Date for which the Order will be selected for the OrderRegister table


steps will be to ceate the stored proc as

1. Select all the Order codes(Order_code) from OrderCheck table
2. For each Order code, select the Order number and Date from Order Master table (Ordertable)for all the Order where date is same as parameter date
3. If there are no Order for a particular code , select the next code from the OrderCheck table and start again from step 1. Otherwise, insert the Date and order code in the OrderRegister Master table as per below mentioned logic



OrderRegsiter table column
a)- Order_id (PK) Surrogate key (running number)
b)- Order_code
c)- ORDER_DATE Date of generation of Tax Register. Available as parameter in the stored procedure
d)- Order_from Select last (max) REPORT_DATE for the market and add one day to it. If there is no last date available for the market then insert 1st Jan <current year=""> as the REPORT_PERIOD_FROM date
e)- Order_to This will be same as ORDER_DATE