Conditional Dynamic SQL In Stored Procedure, Not Returning Any Result
Apr 17, 2007
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so: SELECT region_code, OperGroup_Code, country_name, MajorGroup_Name, RptgEntity_Name, shell_code, CRC, ' ' as right_type INTO #tmpTShell FROM TShell WHERE 1=0
But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.
Has anyone ever experienced anthying like this? Any help would be greatly appreciated!
Is there any way to use conditional SQL in the SELECT clause of a stored procedure, i.e., use an IF-THEN to SELECT either COUNT(*) or a column-list based on the value of a parameter passed to the stored procedure? (I'd like to avoid having two sets of queries with the same WHERE clause).
I need to create a stored procedure for a report Web page which allows the user to specify different criteria. Is there a way to conditionally add different WHERE clauses as needed to a SQL SELECT statement?
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.
I need to create a conditional if or case statement in SQL Server 2000 for a stored procedure. Basically if the value passed in is 1,2 or 3 then it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
I want to return a subset of data from my 'items' table using a stored procedure based on a number of parameters as follows :
customer code varchar(8) userid varchar(10) status tinyint
where the customer code can be any valid customer code OR blank, the userid can be any valid userid or blank and the status can be 1 (item is open), 2 (item is closed) or 3 (item is open or closed, i.e. no filter on status)
Is there a better way to achieve this than the following disaster:
IF LEN(RTRIM(LTRIM(@custcode))) = 0 BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE BEGIN IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END
END ELSE BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.
ie.
Value Date ----- ----- 12 1-1 14 1-2 100 1-8 35 1-10 50 1-12 5 1-15
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
Hi all, I created a stored procedure which perfoms a simple select like this: CREATE PROCEDURE Reports_Category ( @loc varchar(255), @pnum varchar(255) ) AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL RETURNGO It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied. Please help, Thanks, bullpit
I'm converting some direct SQL queries into stored procedures. The original queries have conditional parts appended depending on certain parameters like this:
Sql = "First part" If certain condition then Sql &= "Second part" Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0 Fist part Second part ELSE First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
I am trying to run an update stored procedure that will add 1 revision to the rev field and return the Value back to my Application. My number is incrementing by 2 and not 1. Here is my Stored Procedure CREATE PROCEDURE dbo.sp_Update_file @kbid big,@filename nvarchar(50),@rev big OUTPUT,@moddate datetime,@owner nvarchar(50),@author nvarchar(50)AsUPDATE KBFile SET rev = rev + 1,filename = @filename,moddate = @moddate,owner = @owner,author = @author,@rev = (Select rev from kbfile where kbid = @kbid)WHERE kbid = @kbIDGO
Ok, still a little bit novice on stored procedures, and can't see why this one doesn't return the value I need.In this case, lessonLocation.CREATE PROCEDURE dbo.getLocation @studentId VARCHAR(20), @lessonLocation VARCHAR(50) OUTPUTASBEGIN DECLARE @errCode INT SELECT lessonLocation FROM cmiDataModel WHERE studentId = @studentId SET @errCode = 0 RETURN @errCode RETURN @lessonLocation HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeHANDLE_DBERR: SET @errCode = -1 RETURN @errCodeENDGOIn the query analyzer, it returns the value in the db, but always blank when run in the .Net app.Thanks all,Zath
Hi everyone! I am new to sql server 2005 and visual studio 2005.
I have the following simple stored procedure that checks if a user exists: ------------------------------------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]
@UserName varchar(256) AS BEGIN SET NOCOUNT ON;
IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@UserName) = LoweredUserName )) RETURN(1) ELSE RETURN(0) END -------------------------------------------------------------------------------------------------------
I use the following code to execute the procedure on visual studio: ------------------------------------------------------------------------------------------------------- . . . cmdobj As SqlCommand cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj) cmdobj.CommandType = CommandType.StoredProcedure cmdobj.Parameters.AddWithValue("@UserName", "blablalala") cmdobj.ExecuteNonQuery() cmdobj.Dispose() connobj.Close() . . . -------------------------------------------------------------------------------------------------------
I expected that cmdobj.ExecuteNonQuery() would return 1 if the user blablab exists or 0 if the user doesnt, but it just return -1 (i think because no row was affected)
Does anyone knows how to retrieve the value that my stored procedure returns?
Trying to get a count on records that match search and all I'm getting is 0. I'm using the same basic sp and code elsewhere and it works fine. Anyone see anything wrong here? Stored Procedure:CREATE PROCEDURE GetResultsCount(@searchCatalog nVarchar(100))ASRETURN ( SELECT COUNT(*) FROM CatalogWHERE itemName LIKE @searchCatalog ORitemLongDesc LIKE @searchCatalog)GO Code: Dim connStr As SqlConnection Dim cmdResultsCount As SqlCommand Dim paramReturnCount As SqlParameter Dim intResultsCount As Integer connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdResultsCount = New SqlCommand("GetResultsCount", connStr) cmdResultsCount.CommandType = CommandType.StoredProcedure cmdResultsCount.Parameters.Add("@searchCatalog", Request.QueryString("search")) paramReturnCount = cmdResultsCount.Parameters.Add("ReturnValue", SqlDbType.Int) paramReturnCount.Direction = ParameterDirection.ReturnValue connStr.Open() cmdResultsCount.ExecuteNonQuery() intResultsCount = cmdResultsCount.Parameters("ReturnValue").Value connStr.Close() Me.lblResultsCount.Text = intResultsCount
Im creating an app in VB.NET that calls a stored procedure which inserts data into one of my tables. I need it to return the id it creates (autonumber) to the VB.NET Program
I keep raising an exception in VB.NET...I want to make sure my stored procedure is correct first...sadly this is my first procedure ive created.
If I pass in the value of 1, the stored procedure should return the value of (.5360268), else it should return 0 for any other value(null/blank/empty/etc)
The following stored procedure is returning the value 0 if I pass in the value of 1 which is wrong. And I would need to set to 0 for any other value. I am checking only for null or empty, but the if condition should check any value (except 1) and return 0 for it.
ALTER PROCEDURE [dbo].[Calculator] ( @ExtraCardiacArteriopathy bit ) AS BEGIN declare @zarterio decimal(14,10)
Hi Guys, I have a series of count statements in a Stored Procedure that return some values. What I want to do is take all of the values that have been returned by the Count statements, add them up and return the value to my .Net Code, but for some reason whenever I try I carry on getting a returned value of 0.
I have checked that the statements are in fact returning values other than 0, and I was wondering if there is something that I am doing wrong in the code below:
<code> CREATE PROCEDURE [dbo].[UCOutstanding] @userid int AS DECLARE @Num as Int DECLARE @Num2 as Int DECLARE @Num3 as Int DECLARE @Num4 as Int
SET @Num = (SELECT count(*) FROM images, albums, memorial WHERE (images.active=0 AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num2 = (SELECT count(*) FROM downloads, memorial WHERE (downloads.active=0 AND downloads.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num3 = (SELECT count(*) FROM images, comments, albums, memorial WHERE (comments.active=0 AND comments.imageid=images.id AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid)) SET @Num4 = (SELECT count(*) FROM memorial, story WHERE (story.memorialID=memorial.id and memorial.userid=@userid))
DECLARE @Total as Int SET @Total = @Num + @Num2 + @Num3 + @Num4
I have a Sproc shown below I want the procedure to return the New_ID value back to the calling program how can I do this. The current approach is not working. Please help. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ................................. ALTER PROCEDURE [dbo].[SP] ( @Id int=NULL , @site_id int = null, @Date datetime = null, ) AS BEGIN Declare @New_ID varchar(10) EXEC [dbo].[SP_GENERATEID] @New_ID output INSERT INTO A_Table( CODE,INT_ID,SITE_INT_ID,DATE, ) Values ( @New_ID,@Id,@site_id,@Date ) return @New_ID END Thanks
Hi All.Maybe someone in here could help on this too....Uusally I can return a value from a stored procedure without any problem.Today I ran into something I cannot figure out.Basically....what I am doing is a couple of inserts or updates depending onwhat is being passed.So in the storedproc tag, I am passing the necessary values along with 1output param. I was using an INOUT param, but I figured I would play itsafe since it wasn't working.So...in the stored procedure, I do some conditions inside transactionstatements...I don't have the code with me right now as I am home, but I figure if I cangive you the general idea, you may know what the problem is.So I have something like this...BEGIN TRANIF team_ID is nullBEGINIF target_ID > 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDIF target_ID < 0BEGIN--- INSERT processingSET @OutPutVariable = Scope_Identity()ENDENDIF team_ID is not nullBEGIN-- UPDATE ProcessingENDCOMMIT TRANSELECT @OutPutVariableIf I run this procedure through enterprise, i get what I need....the valueof the last inserted record. When I do it through CF, I always get 0 ORnothing at all.If I do a SELECT 100, I get a return value of 100 of course, so it seemslike it's out of scope.Any ideas?
I have a stored procedure below that returns a table of coaches. It worked before now it does not. It returns nothing, in vistual studio 2005 and on my asp.net webpage. But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows. There is no error just will not return what I need. I recreated the database and stored procedure still doing the same thing. Any ideas? Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Project]( [ProjID] [varchar](300) NOT NULL, [ProjType] [varchar](20) NULL, [ProjectTitle] [varchar](max) NULL, [ProjectDetails] [varchar](max) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](max) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED ( [ProjID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_Report]( [ReportName] [varchar](50) NOT NULL, [ProjID] [varchar](300) NULL, [DeptCode] [varchar](50) NULL, [ProjType] [varchar](50) NULL, [ProjectTitle] [varchar](500) NULL, [ProjectDetails] [varchar](3000) NULL, [ProjectManagerID] [int] NULL, [RequestedBy] [varchar](50) NULL, [DateRequested] [datetime] NULL, [DueDate] [datetime] NULL, [ProjectStatusID] [int] NULL, CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED ( [ReportName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID]) REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] GO ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID]) REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID]) GO ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectStatus]( [ProjectStatusID] [int] IDENTITY(1,1) NOT NULL, [ProjectStatus] [varchar](max) NULL, CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED ( [ProjectStatusID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_ProjectManager]( [ProjectManagerID] [int] IDENTITY(1,1) NOT NULL, [FName] [varchar](50) NULL, [LName] [varchar](50) NULL, [Inactive] [int] NULL, CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED ( [ProjectManagerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetReportQuery] ( @ReportName varchar(100) )
AS
BEGIN
SET NOCOUNT ON
IF @ReportName IS NULL BEGIN RETURN -1 END ELSE BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50) SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName)))) AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL) AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL) AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL) AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL) AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL) AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL) ) END
Hi, How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
SELECT * FROM v_reimbursement_report WHERE facility_key = @facility_key
/*IF @facility_key <> null PRINT "0" BEGIN SELECT * FROM v_reimbursement_report WHERE facility_key = @facility_key END*/
When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:
Private Sub Main_Click() On Error GoTo Err_Main
Dim lsFacility As String Dim lsReportName As String Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim gconn As ADODB.Connection Dim param_facility_key As ADODB.Parameter
Dim gConnString As String
gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''" Set gconn = New ADODB.Connection Set rs = New ADODB.Recordset Set cmd = New ADODB.Command gconn.Open gConnString
Dear all, I have a big problem in using SQL Server stored procedures: When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)
I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely.
I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table.
The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events.
The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select.
Thanks for any help in advance, TimC
SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message INTO #EVENT_FILTERED FROM EVENT WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate)
SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE, CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH, CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC, CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET, CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED, CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED, CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE, EVENT.TIME_STAMP AS IC_Set, EVENT_1.TIME_STAMP AS Cycle_Started, EVENT_2.TIME_STAMP AS Leak_Test_Done, EVENT_3.TIME_STAMP AS End_N2_PrePurge, EVENT_4.TIME_STAMP AS Heating_Complete, EVENT_5.TIME_STAMP AS Split_Temp_Met, EVENT_6.TIME_STAMP AS End_N2_Final_Purge, EVENT_7.TIME_STAMP AS Inner_Cover_Removed, EVENT_8.TIME_STAMP AS Cycle_Complete, EVENT_9.TIME_STAMP AS Post_Purge_Time_Met
FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE
WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND (EVENT_1.MESSAGE LIKE '%Cycle Started%') AND (EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND (EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND (EVENT_4.MESSAGE LIKE '%Heating Complete%') AND (EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND (EVENT_6.MESSAGE LIKE '%End N2 Final%') AND (EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND (EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND (EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND (CHARGE_INFO.TIME_STAMP >= @StartDate) AND (CHARGE_INFO.TIME_STAMP < @EndDate)
ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC