?
Solved

Dynamic SQL and 'WHERE Field IS NULL' Condition

Posted on 2009-04-20
4
Medium Priority
?
484 Views
Last Modified: 2012-05-06
I'm not really good with dynamic SQL and I try to use sp_executesql to generate an SQL Statement. Everything works fine exept in the case where I add a 'Where field is null' condition on the query. Then it hangs forever. I don't understand it because I can use any other condition (Where field is not null works for exemple). Can anybody help me on this?


Static statement thats runs good and fast: 
SELECT  [LABOR_TICKET_ID],[PROJECT_ID],[EMPLOYEE_ID],[FIRST_NAME],[LAST_NAME],[DEPARTMENT_ID],[HOURLY_COST],[WORK_HOURS],[TRANSACTION_DATE],[ACT_LABOR_COST],[COST_CATEGORY_ID] 
FROM 
     (SELECT [ONDT_MSP_CHECK_DETAIL].[laborTicketId] AS [LABOR_TICKET_ID],                        [ONDT_MSP_CHECK_DETAIL].[projectId] AS [PROJECT_ID], [ONDT_MSP_CHECK].[employeeId] AS [EMPLOYEE_ID], COALESCE([EMPLOYEE].FIRST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),0,CharIndex(' ',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [FIRST_NAME],COALESCE ([EMPLOYEE].LAST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),CharIndex(' ',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId])),len([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[LABOR_TICKET].[HOURLY_COST],SUM([ONDT_MSP_WORK_HOURS].[workHours]) AS [WORK_HOURS],[LABOR_TICKET].[TRANSACTION_DATE],[LABOR_TICKET].[INDIRECT_ID],[LABOR_TICKET].[ACT_LABOR_COST],
[LABOR_TICKET].[COST_CATEGORY_ID]
 
FROM	[ONDT_MSP_CHECK_DETAIL] INNER JOIN 
[SRV-QC-SQL05].[VMFGPROD].[dbo].[LABOR_TICKET] [LABOR_TICKET] ON [ONDT_MSP_CHECK_DETAIL].[laborTicketId] = [LABOR_TICKET].[TRANSACTION_ID] INNER JOIN
[ONDT_MSP_CHECK] ON [ONDT_MSP_CHECK_DETAIL].[checkId] = [ONDT_MSP_CHECK].[ID] INNER JOIN
[ONDT_MSP_WEEK] ON [ONDT_MSP_CHECK].[weekId] = [ONDT_MSP_WEEK].[ID] INNER JOIN
[SRV-QC-SQL05].[VMFGPROD].[dbo].[EMPLOYEE] [EMPLOYEE] ON[ONDT_MSP_CHECK].employeeId = [EMPLOYEE].ID INNER JOIN
[ONDT_MSP_WORK_HOURS] ON [ONDT_MSP_WORK_HOURS].[checkDetailId] = [ONDT_MSP_CHECK_DETAIL].[ID]
						
WHERE   [ONDT_MSP_WEEK].[startWeek] = '2009-02-01'  AND [EMPLOYEE].[ID] = '000815' 
GROUP BY [LABORTICKETID],[projectId],[employeeId],[FIRST_NAME],[LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[HOURLY_COST],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[LABOR_TICKET].[COST_CATEGORY_ID]) as TIMESHEET
 
WHERE TIMESHEET.[INDIRECT_ID] IS NULL 
ORDER BY LAST_NAME,FIRST_NAME
 
 
Dynamic SP:
ALTER PROCEDURE [dbo].[ONDT_MSP_PORTAL_GET_TIMESHEET_TRANSFER_DIA]
	@Startdate	DATETIME,
	@Filter smallint,			--ALL = -1 Direct = 0 Indirect = 1
	@Department_ID int = NULL,
	@Employee_ID nvarchar(15) = NULL,
	@Debug bit = 0
AS
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
 
	DECLARE @sqlCommand as nvarchar(4000)
	DECLARE @Condition as nvarchar(1000)
 
	SET @sqlCommand = '	SELECT [ONDT_MSP_CHECK_DETAIL].[laborTicketId] AS [LABOR_TICKET_ID],
							[ONDT_MSP_CHECK_DETAIL].[projectId] AS [PROJECT_ID], 
							[ONDT_MSP_CHECK].[employeeId] AS [EMPLOYEE_ID],
							COALESCE([EMPLOYEE].FIRST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),0,CharIndex('' '',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [FIRST_NAME],
							COALESCE ([EMPLOYEE].LAST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),CharIndex('' '',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId])),len([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [LAST_NAME],
							[EMPLOYEE].[DEPARTMENT_ID],
							[LABOR_TICKET].[HOURLY_COST],
							SUM([ONDT_MSP_WORK_HOURS].[workHours]) AS [WORK_HOURS],
							[LABOR_TICKET].[TRANSACTION_DATE],
							[LABOR_TICKET].[INDIRECT_ID],
							[LABOR_TICKET].[ACT_LABOR_COST],
							[LABOR_TICKET].[COST_CATEGORY_ID]
 
							FROM	[ONDT_MSP_CHECK_DETAIL] INNER JOIN 
									[SRV-QC-SQL05].[VMFGPROD].[dbo].[LABOR_TICKET] [LABOR_TICKET] ON [ONDT_MSP_CHECK_DETAIL].[laborTicketId] = [LABOR_TICKET].[TRANSACTION_ID] INNER JOIN
									[ONDT_MSP_CHECK] ON [ONDT_MSP_CHECK_DETAIL].[checkId] = [ONDT_MSP_CHECK].[ID] INNER JOIN
									[ONDT_MSP_WEEK] ON [ONDT_MSP_CHECK].[weekId] = [ONDT_MSP_WEEK].[ID] INNER JOIN
									[SRV-QC-SQL05].[VMFGPROD].[dbo].[EMPLOYEE] [EMPLOYEE] ON [ONDT_MSP_CHECK].employeeId = [EMPLOYEE].ID INNER JOIN
									[ONDT_MSP_WORK_HOURS] ON [ONDT_MSP_WORK_HOURS].[checkDetailId] = [ONDT_MSP_CHECK_DETAIL].[ID]
						
							WHERE   [ONDT_MSP_WEEK].[startWeek] = @Startdate '
	
-- Check wich params are passed to filter the query
IF @Department_ID is not NULL
BEGIN
	SET @sqlCommand = @sqlCommand + ' AND [EMPLOYEE].[Department_ID] = @Department_ID'
END
 
IF @Employee_ID is not NULL
BEGIN
 
	
		SET @sqlCommand = @sqlCommand + ' AND [EMPLOYEE].[ID] = @Employee_ID'
END
 
SET @sqlCommand = @sqlCommand + ' GROUP BY [LABORTICKETID],[projectId],[employeeId],[FIRST_NAME],[LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[HOURLY_COST],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[LABOR_TICKET].[COST_CATEGORY_ID]'
 
-- ALL
IF @Filter = -1 
BEGIN	
	SET @sqlCommand = 'SELECT  [LABOR_TICKET_ID],[PROJECT_ID],[EMPLOYEE_ID],[FIRST_NAME],[LAST_NAME],[DEPARTMENT_ID],[HOURLY_COST],[WORK_HOURS],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[COST_CATEGORY_ID] FROM(' 
						+ @sqlCommand + ') as TIMESHEET '
						
END
 
-- DIRECT ONLY
ELSE IF @Filter = 0
BEGIN
	SET @sqlCommand = 'SELECT  [LABOR_TICKET_ID],[PROJECT_ID],[EMPLOYEE_ID],[FIRST_NAME],[LAST_NAME],[DEPARTMENT_ID],[HOURLY_COST],[WORK_HOURS],[TRANSACTION_DATE],[ACT_LABOR_COST],[COST_CATEGORY_ID] FROM(' 
						+ @sqlCommand + ') as TIMESHEET
									WHERE TIMESHEET.[INDIRECT_ID] IS NULL'
END
 
-- INDIRECT ONLY
ELSE IF @Filter = 1
BEGIN
	SET @sqlCommand = 'SELECT  [LABOR_TICKET_ID],[EMPLOYEE_ID],[FIRST_NAME],[LAST_NAME],[DEPARTMENT_ID],[HOURLY_COST],[WORK_HOURS],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[COST_CATEGORY_ID] FROM('  
						+ @sqlCommand  + ') as TIMESHEET
									WHERE TIMESHEET.[INDIRECT_ID] IS NOT NULL'
END
 
--INVALID FILTER
ELSE
BEGIN
	DECLARE @ErrorMessage as nvarchar(200)
	SET @ErrorMessage = convert(nvarchar(20),@Filter) + ' IS NOT A VALID FILTER'
	RAISERROR(@ErrorMessage, 16, -1, 300)
END
 
-- Group by project 
SET @sqlCommand = @sqlCommand + ' ORDER BY LAST_NAME,FIRST_NAME'
 
	-- DEBUG THE CMD
	IF @Debug = 1 PRINT @sqlCommand
 
	IF @Filter = -1 OR @Filter = 0 OR @Filter = 1
	BEGIN
		EXECUTE sp_executesql @sqlCommand, N'@Filter int,@Startdate datetime, @Department_ID int, @Employee_ID nvarchar(15)', @Filter = @Filter, @Startdate = @Startdate ,  @Department_ID = @Department_ID , @Employee_ID = @Employee_ID
	END
END
 
SP CALL 
--Parameters:  Transfer Date, Filter, Department,EmployeeID,Debug
ONDT_MSP_PORTAL_GET_TIMESHEET_TRANSFER_DIA '2009-02-01',0,DEFAULT,'000815',1

Open in new window

0
Comment
Question by:lali_murray
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24187728
I don't know why you're having that problem.

But I've got a better idea:  Pull it out of dynamic SQL ... like this:

SELECT  [LABOR_TICKET_ID],[PROJECT_ID],[EMPLOYEE_ID],[FIRST_NAME],[LAST_NAME],[DEPARTMENT_ID],[HOURLY_COST],[WORK_HOURS],[TRANSACTION_DATE],[ACT_LABOR_COST],[COST_CATEGORY_ID] 
FROM 
     (SELECT [ONDT_MSP_CHECK_DETAIL].[laborTicketId] AS [LABOR_TICKET_ID],                        [ONDT_MSP_CHECK_DETAIL].[projectId] AS [PROJECT_ID], [ONDT_MSP_CHECK].[employeeId] AS [EMPLOYEE_ID], COALESCE([EMPLOYEE].FIRST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),0,CharIndex(' ',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [FIRST_NAME],COALESCE ([EMPLOYEE].LAST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),CharIndex(' ',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId])),len([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[LABOR_TICKET].[HOURLY_COST],SUM([ONDT_MSP_WORK_HOURS].[workHours]) AS [WORK_HOURS],[LABOR_TICKET].[TRANSACTION_DATE],[LABOR_TICKET].[INDIRECT_ID],[LABOR_TICKET].[ACT_LABOR_COST],
[LABOR_TICKET].[COST_CATEGORY_ID]
 
FROM    [ONDT_MSP_CHECK_DETAIL] INNER JOIN 
[SRV-QC-SQL05].[VMFGPROD].[dbo].[LABOR_TICKET] [LABOR_TICKET] ON [ONDT_MSP_CHECK_DETAIL].[laborTicketId] = [LABOR_TICKET].[TRANSACTION_ID] INNER JOIN
[ONDT_MSP_CHECK] ON [ONDT_MSP_CHECK_DETAIL].[checkId] = [ONDT_MSP_CHECK].[ID] INNER JOIN
[ONDT_MSP_WEEK] ON [ONDT_MSP_CHECK].[weekId] = [ONDT_MSP_WEEK].[ID] INNER JOIN
[SRV-QC-SQL05].[VMFGPROD].[dbo].[EMPLOYEE] [EMPLOYEE] ON[ONDT_MSP_CHECK].employeeId = [EMPLOYEE].ID INNER JOIN
[ONDT_MSP_WORK_HOURS] ON [ONDT_MSP_WORK_HOURS].[checkDetailId] = [ONDT_MSP_CHECK_DETAIL].[ID]
                                                
WHERE   [ONDT_MSP_WEEK].[startWeek] = '2009-02-01'  AND [EMPLOYEE].[ID] = '000815' 
 
GROUP BY [LABORTICKETID],[projectId],[employeeId],[FIRST_NAME],[LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[HOURLY_COST],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[LABOR_TICKET].[COST_CATEGORY_ID]) as TIMESHEET
	AND IsNull([EMPLOYEE].[Department_ID],0) = IsNull(@Department_ID,IsNull([EMPLOYEE].[Department_ID],0))
WHERE 
	(@Filter = -1) OR
	(@Filter = 0 and TIMESHEET.[INDIRECT_ID] IS NULL )OR
	(@Filter = 1 and TIMESHEET.[INDIRECT_ID] IS NOT NULL )
ORDER BY LAST_NAME,FIRST_NAME
 

Open in new window

0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24187751
Not sure as to why using sp_executesql would hang as such..

It would be advisable to check for  IS NULL/IS NOT NULL in the main query instead of doing it in the outer query as this may utilize any possible indexes on this INDIRECT_ID field..

Try and see if the slightly reworked  (doesn't use a grouped suquery)  procedure below works -


ALTER PROCEDURE [dbo].[ONDT_MSP_PORTAL_GET_TIMESHEET_TRANSFER_DIA]
        @Startdate      DATETIME,
        @Filter smallint,                       --ALL = -1 Direct = 0 Indirect = 1
        @Department_ID int = NULL,
        @Employee_ID nvarchar(15) = NULL,
        @Debug bit = 0
AS
BEGIN
 
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON
 
        DECLARE @sqlCommand as nvarchar(4000)
        DECLARE @Condition as nvarchar(1000)
 
        SET @sqlCommand = '     SELECT [ONDT_MSP_CHECK_DETAIL].[laborTicketId] AS [LABOR_TICKET_ID],
                                                        [ONDT_MSP_CHECK_DETAIL].[projectId] AS [PROJECT_ID], 
                                                        [ONDT_MSP_CHECK].[employeeId] AS [EMPLOYEE_ID],
                                                        COALESCE([EMPLOYEE].FIRST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),0,CharIndex('' '',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [FIRST_NAME],
                                                        COALESCE ([EMPLOYEE].LAST_NAME,UPPER(SUBSTRING([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]),CharIndex('' '',[dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId])),len([dbo].[ONDT_WEB_FN_Get_Employee_Name_By_Id]([ONDT_MSP_CHECK].[employeeId]))))) AS [LAST_NAME],
                                                        [EMPLOYEE].[DEPARTMENT_ID],
                                                        [LABOR_TICKET].[HOURLY_COST],
                                                        SUM([ONDT_MSP_WORK_HOURS].[workHours]) AS [WORK_HOURS],
                                                        [LABOR_TICKET].[TRANSACTION_DATE],
                                                        [LABOR_TICKET].[INDIRECT_ID],
                                                        [LABOR_TICKET].[ACT_LABOR_COST],
                                                        [LABOR_TICKET].[COST_CATEGORY_ID]
 
                                                        FROM    [ONDT_MSP_CHECK_DETAIL] INNER JOIN 
                                                                        [SRV-QC-SQL05].[VMFGPROD].[dbo].[LABOR_TICKET] [LABOR_TICKET] ON [ONDT_MSP_CHECK_DETAIL].[laborTicketId] = [LABOR_TICKET].[TRANSACTION_ID] INNER JOIN
                                                                        [ONDT_MSP_CHECK] ON [ONDT_MSP_CHECK_DETAIL].[checkId] = [ONDT_MSP_CHECK].[ID] INNER JOIN
                                                                        [ONDT_MSP_WEEK] ON [ONDT_MSP_CHECK].[weekId] = [ONDT_MSP_WEEK].[ID] INNER JOIN
                                                                        [SRV-QC-SQL05].[VMFGPROD].[dbo].[EMPLOYEE] [EMPLOYEE] ON [ONDT_MSP_CHECK].employeeId = [EMPLOYEE].ID INNER JOIN
                                                                        [ONDT_MSP_WORK_HOURS] ON [ONDT_MSP_WORK_HOURS].[checkDetailId] = [ONDT_MSP_CHECK_DETAIL].[ID]
                                                
                                                        WHERE   [ONDT_MSP_WEEK].[startWeek] = @Startdate '
        
	-- Check wich params are passed to filter the query
	IF @Department_ID is not NULL
	BEGIN
	        SET @sqlCommand = @sqlCommand + ' AND [EMPLOYEE].[Department_ID] = @Department_ID'
	END
	 
	IF @Employee_ID is not NULL
	BEGIN
	 
	        
	                SET @sqlCommand = @sqlCommand + ' AND [EMPLOYEE].[ID] = @Employee_ID'
	END
	 
	-- DIRECT ONLY
	IF @Filter = 0 
	BEGIN   
	        SET @sqlCommand = ' AND [LABOR_TICKET].[INDIRECT_ID] IS NULL '
	END
	 
	-- INDIRECT ONLY
	ELSE IF @Filter = 1
	BEGIN
	        SET @sqlCommand = ' AND [LABOR_TICKET].[INDIRECT_ID] IS NOT NULL '
	END
	ELSE IF @Filter <> -1  -- <> ALL
	BEGIN
	        DECLARE @ErrorMessage as nvarchar(200)
	        SET @ErrorMessage = convert(nvarchar(20),@Filter) + ' IS NOT A VALID FILTER'
	        RAISERROR(@ErrorMessage, 16, -1, 300)
	END
	
	SET @sqlCommand = @sqlCommand + ' GROUP BY [LABORTICKETID],[projectId],[employeeId],[FIRST_NAME],[LAST_NAME],[EMPLOYEE].[DEPARTMENT_ID],[HOURLY_COST],[TRANSACTION_DATE],[INDIRECT_ID],[ACT_LABOR_COST],[LABOR_TICKET].[COST_CATEGORY_ID]'
	 
	-- Group by project 
	SET @sqlCommand = @sqlCommand + ' ORDER BY LAST_NAME,FIRST_NAME'
 
       -- DEBUG THE CMD
        IF @Debug = 1 PRINT @sqlCommand
 
	EXECUTE sp_executesql @sqlCommand, N'@Filter int,@Startdate datetime, @Department_ID int, @Employee_ID nvarchar(15)', @Filter = @Filter, @Startdate = @Startdate ,  @Department_ID = @Department_ID , @Employee_ID = @Employee_ID
END
GO

Open in new window

0
 

Author Comment

by:lali_murray
ID: 24187877
As Suggested, I have removed the grouped subquery and It runs good. I'll try to figure out a way to get the same results without the group by and award you the points. Thanks!
0
 

Author Closing Comment

by:lali_murray
ID: 31572415
It's efficient now, Thanks a lot!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question