Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.
How can I modify this so that I can pass in an additional parameter and have it group accordingly. I assume I'm going to have to modify the query so that it builds a dynamic SQL string and then execute that from within the SP. I know how I would do this in Access, using the Format( ) function with the docDate and a format string that either looks like "yyyy", "yy-q", or "yy-mm", but I'm not sure how to go about that in SQL Server.
ALTER PROCEDURE [dbo].[df_Analysis_Production_by_Well_Facility] @DSID integer, @FieldID integer, @EntityID integer, @StartDate DateTime, @EndDate DateTime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @DSID = ISNULL(@DSID, 0) SET @FieldID = IsNull(@FieldID, 0) SET @EntityID = ISNULL(@EntityID, 0) SELECT WF.DS_ID , DS.DS_Description AS Data_Source , WF.FieldID , WF.FieldName , P.Entity_ID , WF.Entity_Name AS Well_Fac_Name , WF.Bolo_ID , WF.Entity_Type_Desc AS Well_Fac_Type , P.docDate , P.Oil , P.Gas , P.Water FROM vw_Wells_and_Facilities_All as WF RIGHT JOIN tbl_sysProduction as P ON WF.Entity_ID = P.Entity_ID LEFT JOIN lst_Data_Sources as DS ON WF.DS_ID = DS.DS_ID WHERE ((@DSID = 0) OR (WF.DS_ID = @DSID)) AND ((@FieldID = 0) OR (WF.FieldID = @FieldID)) AND ((@EntityID = 0) OR (P.Entity_ID = @EntityID)) AND ((@StartDate IS NULL) OR (P.docDate >= @StartDate)) AND ((@EndDate IS NULL) OR (P.docDate < DATEADD(day, 1, @EndDate))) ORDER BY WF.FieldName, WF.Entity_Name, P.docDate END
|execute a MS SQL script as a schedule SQL job||72||97|
|Access recordset not updateable||8||38|
|Recurring Excel Timelime for Veeam||2||32|
|SQL: export into csv/sqlcmd method and field config||3||16|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!