WynneIT Posted October 30, 2019 Share Posted October 30, 2019 I have a stored procedure that looks for orders within a date period. How do I set the date within the stored procedure? Here is my code. Thank you!! USE [TMWSUITE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Proc [dbo].[DawgAlert_Completed_Order_Status] AS SELECT orderheader.ord_number ,orderheader.ord_billto ,orderheader.ord_status ,orderheader.ord_origin_earliestdate ,orderheader.ord_origin_latestdate ,orderheader.ord_dest_earliestdate ,orderheader.ord_dest_latestdate ,orderheader.ord_trailer ,orderheader.ord_totalmiles ,stops.stp_city ,stops.stp_state ,stops.stp_schdtearliest ,stops.stp_schdtlatest ,stops.stp_status ,stops.stp_number ,stops.stp_sequence ,stops.stp_arrivaldate ,stops.stp_departuredate ,stops.cmd_code ,stops.cmp_name ,orderheader.ord_refnum ,orderheader.ord_description ,trailerprofile.trl_number ,trailerprofile.trl_type3 ,city.cty_nmstct ,orderheader.ord_completiondate FROM orderheader INNER JOIN stops ON orderheader.ord_hdrnumber = stops.ord_hdrnumber LEFT OUTER JOIN company ON orderheader.ord_company = company.cmp_id INNER JOIN trailerprofile ON orderheader.ord_trailer = trailerprofile.trl_number INNER JOIN city ON stops.stp_city = city.cty_code WHERE stops.stp_departure_status = 'DNE' AND orderheader.ord_status='CMP' AND (orderheader.ord_completiondate >= @LoadDate) AND (orderheader.ord_completiondate <= @EndDate) AND (orderheader.ord_billto = @billto) Link to comment Share on other sites More sharing options...
RecursiveCTE Posted November 6, 2019 Share Posted November 6, 2019 Pass the dates as parameters to the procedure: CREATE Proc [dbo].[DawgAlert_Completed_Order_Status] (@LoadDate datetime, @EndDate datetime, @billto varchar(100)) You may call the procedure with parameters: exec [dbo].[DawgAlert_Completed_Order_Status] '2019/01/01','2019/01/02','bill to address' For more info: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15 Link to comment Share on other sites More sharing options...
Makwana Prahlad Posted February 11, 2020 Share Posted February 11, 2020 Hello, @WynneIT Please try this code,To set date in stored procedure CREATE PROCEDURE TRGetMsgTksByBIdDIdPIdAMsreDte @REGISTRATION_DATE DATETIME = NULL AS SET NOCOUNT ON SELECT * FROM STUDENT WHERE REGISTRATION_DATE = @REGISTRATION_DATE RETURN 0 GO I hope above code will be useful for you. Thank you. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now