none
How to execute a Proc in SQL with date parameters

Answers

  • Date parameters are treated the same way as any other sproc parameter.  I'm assuming that you mean datetime (SQL 2005), but it would be the same for Date (SQL 2008):

     

    Code Snippet

    CREATE PROC DateProc (@SomeParam datetime)

    AS

    BEGIN

    PRINT @SomeParam

    END

     

    --Test it

    DECLARE @DateInput datetime

    SET @DateInput = GetDate()

    EXEC DateProc @DateInput

     

     

    Does this help?
    Monday, November 10, 2008 3:32 PM
    Moderator
  • If the date is the only parameter you could execute it like this.

     

    EXEC storedprocedurename '11/10/2008'

     

    You can also specify the parameter name like this.

     

    EXEC storedprocedurename @nameofdateparmater='11/10/2008'

     

    Monday, November 10, 2008 3:32 PM
  • Just join inv_loc to inv_mast:

     

    Code Snippet

    CREATE PROC  DXP_PROC_Goulds_Items_Received_Southeast_Region
    (@sdate as datetime, @edate as datetime)

    AS

     

    select 'Purchase Order' as Type, l.location_id,l.location_name ,pol.po_no as po_or_transfer_number,pol.qty_received,pol.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_po_line pol
    join p21_view_po_hdr poh on pol.po_no=poh.po_no
    join p21_view_inv_mast m on m.inv_mast_uid = pol.inv_mast_uid
    join p21_view_location l on l.location_id = poh.location_id

    join inv_loc il ON m.inv_mast_uid = il.inv_mast_uid
    where poh.supplier_id IN ('36486','82798','65054') and poh.location_id
    IN('10038','10047')
    and pol.received_date >= @sdate and pol.received_date < @edate + 1
     

    Union ALL

     

    Select 'Transfer' as Type,l.location_id,l.location_name, trl.transfer_no as  po_or_transfer_number,trl.qty_transferred,thr.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_transfer_line trl
    join p21_view_transfer_hdr thr on trl.transfer_no=thr.transfer_no
    join p21_view_inv_mast m on m.inv_mast_uid = trl.inv_mast_uid
    join p21_view_inv_loc il on il.inv_mast_uid=trl.inv_mast_uid and from_location_id = il.location_id
    join p21_view_location l on l.location_id = thr.to_location_id

    join inv_loc il ON m.inv_mast_uid = il.inv_mast_uid
    where thr.to_location_id IN ('10047','10038')
    and il.primary_supplier_id IN ('36486','82798','65054')
    and thr.received_date >= @sdate and thr.received_date < @edate + 1

     

     

     

    Does this solve it?
    Monday, November 10, 2008 4:23 PM
    Moderator

All replies

  • Date parameters are treated the same way as any other sproc parameter.  I'm assuming that you mean datetime (SQL 2005), but it would be the same for Date (SQL 2008):

     

    Code Snippet

    CREATE PROC DateProc (@SomeParam datetime)

    AS

    BEGIN

    PRINT @SomeParam

    END

     

    --Test it

    DECLARE @DateInput datetime

    SET @DateInput = GetDate()

    EXEC DateProc @DateInput

     

     

    Does this help?
    Monday, November 10, 2008 3:32 PM
    Moderator
  • If the date is the only parameter you could execute it like this.

     

    EXEC storedprocedurename '11/10/2008'

     

    You can also specify the parameter name like this.

     

    EXEC storedprocedurename @nameofdateparmater='11/10/2008'

     

    Monday, November 10, 2008 3:32 PM
  • Hi Ken

     

    when i use your way as

     

    EXEC DXP_PROC_Machine_Repair_Sales '11/10/2008'

     

    i get this error

     

    Procedure PROC_Machine_Repair_Sales' expects parameter '@edate', which was not supplied.

     

    Monday, November 10, 2008 3:40 PM
  • CREATE PROC  PROC_Machine_Repair_Sales
    (@sdate as datetime, @edate as datetime)

    as

    select  ih.invoice_no,ih.order_no,ih.order_date,a.name as 'Customer Name',
    ih.customer_id,
    inl.item_id,inl.extended_price,inl.cogs_amount
    from p21_view_invoice_hdr ih
    join p21_view_invoice_line inl on ih.invoice_no=inl.invoice_no 
    join p21_view_address a on ih.customer_id=a.id
    join p21_view_inv_mast m on m.inv_mast_uid=inl.inv_mast_uid
    join p21_view_oe_line ol on ol.order_no =inl.order_no
    and ol.line_no=inl.oe_line_number
    where
    inl.item_id in ('SERVICE LABOR KIT','GCTR MACHINE REPAIR')
    and ol.source_loc_id='129041' and
    ih.order_date >= @sdate and ih.order_date < @edate + 1
    order by inl.item_id

     

    This is how i created the proc and now i am just trying to execute it ..
    Monday, November 10, 2008 3:42 PM
  • You have two datetime params, so the syntax would be:

     

    Code Snippet

    EXEC PROC_Machine_Repair_Sales @Date1, @Date2

     

     

     

    Like this:

     

    Code Snippet

    EXEC PROC_Machine_Repair_Sales '2008-01-01', '2008-12-31'

     

     

    Does this help?
    Monday, November 10, 2008 3:44 PM
    Moderator
  • You must have multiple input parmeters.  If edate is the only parmater that is required you can do it like this.  If not, you will have to specify a value for every parameter that does not have a defualt value assigned.

     

     

    EXEC DXP_PROC_Machine_Repair_Sales @edate='11/10/2008'

     

    An example would be...

     

    CREATE PROC DateProc2 (@SomeParam datetime= '11/1/2008', @edate datetime)

    AS

    BEGIN

    PRINT @SomeParam

    END

    --Test it

     

    EXEC DateProc2 @edate='1/1/2008'

     

    --Since the first value has a default it is not required, but since edate is not the first parameter you must use the name.

    Monday, November 10, 2008 3:47 PM
  • Hi Aaron,

     

    Thanks .. That works .. thank you very much.

     

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    CREATE PROC  DXP_PROC_Goulds_Items_Received_Southeast_Region
    (@sdate as datetime, @edate as datetime)

    AS

     

    select 'Purchase Order' as Type, l.location_id,l.location_name ,pol.po_no as po_or_transfer_number,pol.qty_received,pol.received_date,
    m.item_id,m.item_desc
    from p21_view_po_line pol
    join p21_view_po_hdr poh on pol.po_no=poh.po_no
    join p21_view_inv_mast m on m.inv_mast_uid = pol.inv_mast_uid
    join p21_view_location l on l.location_id = poh.location_id
    where poh.supplier_id IN ('36486','82798','65054') and poh.location_id
    IN('10038','10047')
    and pol.received_date >= @sdate and pol.received_date < @edate + 1
     

    Union ALL

     

    Select 'Transfer' as Type,l.location_id,l.location_name, trl.transfer_no as  po_or_transfer_number,trl.qty_transferred,thr.received_date,
    m.item_id,m.item_desc
    from p21_view_transfer_line trl
    join p21_view_transfer_hdr thr on trl.transfer_no=thr.transfer_no
    join p21_view_inv_mast m on m.inv_mast_uid = trl.inv_mast_uid
    join p21_view_inv_loc il on il.inv_mast_uid=trl.inv_mast_uid and from_location_id = il.location_id
    join p21_view_location l on l.location_id = thr.to_location_id
    where thr.to_location_id IN ('10047','10038')
    and il.primary_supplier_id IN ('36486','82798','65054')
    and thr.received_date >= @sdate and thr.received_date < @edate + 1

     

     

    I have this Proc which is live now. I am wanting to add another column to both the queries named cost.

     

    inv_loc is the table that has the standard cost for all the item id`s. Inv_loc also has a col " inv_mast_uid" can we join standard_cost col from inv_loc to inv_mast uid and gett the results ?? or  do a join standrd_cost=item_id ?

     

    Item_id col is in inv_mast

     

    How can i add the cost col to both the queries ???

     

    Can you help?

    Monday, November 10, 2008 3:53 PM
  • Just join inv_loc to inv_mast:

     

    Code Snippet

    CREATE PROC  DXP_PROC_Goulds_Items_Received_Southeast_Region
    (@sdate as datetime, @edate as datetime)

    AS

     

    select 'Purchase Order' as Type, l.location_id,l.location_name ,pol.po_no as po_or_transfer_number,pol.qty_received,pol.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_po_line pol
    join p21_view_po_hdr poh on pol.po_no=poh.po_no
    join p21_view_inv_mast m on m.inv_mast_uid = pol.inv_mast_uid
    join p21_view_location l on l.location_id = poh.location_id

    join inv_loc il ON m.inv_mast_uid = il.inv_mast_uid
    where poh.supplier_id IN ('36486','82798','65054') and poh.location_id
    IN('10038','10047')
    and pol.received_date >= @sdate and pol.received_date < @edate + 1
     

    Union ALL

     

    Select 'Transfer' as Type,l.location_id,l.location_name, trl.transfer_no as  po_or_transfer_number,trl.qty_transferred,thr.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_transfer_line trl
    join p21_view_transfer_hdr thr on trl.transfer_no=thr.transfer_no
    join p21_view_inv_mast m on m.inv_mast_uid = trl.inv_mast_uid
    join p21_view_inv_loc il on il.inv_mast_uid=trl.inv_mast_uid and from_location_id = il.location_id
    join p21_view_location l on l.location_id = thr.to_location_id

    join inv_loc il ON m.inv_mast_uid = il.inv_mast_uid
    where thr.to_location_id IN ('10047','10038')
    and il.primary_supplier_id IN ('36486','82798','65054')
    and thr.received_date >= @sdate and thr.received_date < @edate + 1

     

     

     

    Does this solve it?
    Monday, November 10, 2008 4:23 PM
    Moderator
  •  

    Hi Aaron,

    I get this message

     

    The correlation name 'il' is specified multiple times in a FROM clause.

     

    The first query with the join works but

    In the second query there`s an existing join for inv_loc 

     

    so i made some changes to your join see below and it seems to be working..

     

    join p21_view_inv_loc il on il.inv_mast_uid=trl.inv_mast_uid and from_location_id = il.location_id and m.inv_mast_uid = il.inv_mast_uid ????

     

    Since this is an existing PROC can i just use Alter command ?? or can you give how to modify the existing PROC??

     

    Thank you

     

    FM



    Monday, November 10, 2008 4:33 PM
  • Yeah, I didn't see that.  You can just remove the JOIN in the second query, if standard_cost is in p21_view_inv_loc.  Otherwise, leave it in there, but change the alias.  Yes, just use ALTER PROC to make the change:

     

    Code Snippet

    ALTER PROC  DXP_PROC_Goulds_Items_Received_Southeast_Region
    (@sdate as datetime, @edate as datetime)

    AS

     

    select 'Purchase Order' as Type, l.location_id,l.location_name ,pol.po_no as po_or_transfer_number,pol.qty_received,pol.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_po_line pol
    join p21_view_po_hdr poh on pol.po_no=poh.po_no
    join p21_view_inv_mast m on m.inv_mast_uid = pol.inv_mast_uid
    join p21_view_location l on l.location_id = poh.location_id

    join inv_loc il ON m.inv_mast_uid = il.inv_mast_uid
    where poh.supplier_id IN ('36486','82798','65054') and poh.location_id
    IN('10038','10047')
    and pol.received_date >= @sdate and pol.received_date < @edate + 1
     

    Union ALL

     

    Select 'Transfer' as Type,l.location_id,l.location_name, trl.transfer_no as  po_or_transfer_number,trl.qty_transferred,thr.received_date,
    m.item_id,m.item_desc, il.standard_cost
    from p21_view_transfer_line trl
    join p21_view_transfer_hdr thr on trl.transfer_no=thr.transfer_no
    join p21_view_inv_mast m on m.inv_mast_uid = trl.inv_mast_uid
    join p21_view_inv_loc il on il.inv_mast_uid=trl.inv_mast_uid and from_location_id = il.location_id
    join p21_view_location l on l.location_id = thr.to_location_id

    where thr.to_location_id IN ('10047','10038')
    and il.primary_supplier_id IN ('36486','82798','65054')
    and thr.received_date >= @sdate and thr.received_date < @edate + 1

     

     

    HTH.
    Monday, November 10, 2008 4:49 PM
    Moderator
  • Hi Aaron,

     

    Thanks a lot.. that works for me...

     

    FM

     

    Monday, November 10, 2008 5:01 PM
  • No problem, Farhan.

     

    Monday, November 10, 2008 5:13 PM
    Moderator