none
Add a subquery to a dynamic query RRS feed

  • Question

  • Hi Gurus of SQL,

    I have the following query

    	@LastResult int NULL,
    	@Male int NULL,
    	@Female int NULL,
    	@StartDate Date
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	DECLARE @SQL NVARCHAR(2000), @WHERE NVARCHAR(2000)
    
    	BEGIN
    		SET @SQL = 
    			'INSERT INTO dbo.tblPtsPerCompHistoryTemp
    			SELECT T.*
    			FROM dbo.tblPtsPerCompHistory AS T
    			INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID 
    			'
    
    		SET @WHERE = 'WHERE C.Comp_Date >= @StartDate '
    		END
    
    	IF @Male Is Not NULL OR @Female Is Not NULL
    		BEGIN
    			SET @WHERE = @WHERE + 'And T.PtsMale = @Male And T.PtsFemale = @Female '
    		END
    
    	IF @LastResult Is Not NULL 
    		BEGIN
    			SET @WHERE = @WHERE + 'And T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 '
    		END
    	ELSE 
    		BEGIN
    			SET @WHERE = @WHERE + 'And T.PtsMale + T.PtsFemale > 0 '
    		END
    
    	BEGIN
    		SET @SQL = @SQL + @WHERE
    	END
    
    	EXEC sp_executesql @SQL,N'@LastResult int,@Male int, @Female int, @StartDate Date',@LastResult,@Male,@Female,@StartDate
    END
    However my WHERE clause for @LastResult is incorrect as it (quite rightly) returns only the rows whereT.PtsCompID = @LastResult
    What I actually need is where @LastResult is not Null to select rows based on the following as a sub query
    SELECT PtsMale, PtsFemale, PtsCplID
    FROM tblPtsPerCompHistory
    WHERE PtsCompID = @LastResult And PtsMale + PtsFemale > 0
    Sub Queries can still sometimes be problematic for me as I progress down the learning path & to now include it in this dynamic query is currently a stumbling block. Can anyone please help point me in the right direction with this. Any assistance in me achieving my objective much appreciated. I may also have my Begin & End in the wrong places although the query executes as is


    Sunday, April 21, 2019 9:12 PM

Answers

  • Hi Rachel,

    I managed to get the required results using Joins instead as follows

    	IF @LastResult is not NULL
    		BEGIN
    			SET @SQL = 'INSERT INTO dbo.tblPtsPerCompHistoryTemp
    			SELECT H.* 
    				FROM tblPtsPerCompHistory H
    				INNER JOIN 
    				(
    				SELECT *
    				FROM tblPtsPerCompHistory AS T
    				INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID And T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 And C.Comp_Date >= @StartDate
    				) As T ON T.PtsMale = H.PtsMale And T.PtsFemale = H.PtsFemale '
    		END
    
    	ELSE IF @LastResult is NULL
    		BEGIN
    			SET @SQL = 
    				'INSERT INTO dbo.tblPtsPerCompHistoryTemp
    				SELECT T.*
    				FROM dbo.tblPtsPerCompHistory AS T
    				INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID 
    				'
    			SET @WHERE = 'WHERE C.Comp_Date >= @StartDate '
    
    			IF @Male Is Not NULL OR @Female Is Not NULL
    					SET @WHERE = @WHERE + 'And T.PtsMale = @Male And T.PtsFemale = @Female '
    					SET @SQL = @SQL + @WHERE
    		END
    
    	EXEC sp_executesql @SQL,N'@LastResult int,@Male int, @Female int, @StartDate Date',@LastResult,@Male,@Female,@StartDate

    Monday, April 22, 2019 1:50 PM

All replies

  • Start with figuring out how you would do this in a static query. Once you have this going, getting the dynamic query right will be a lot easier. After all, building a dynamic query is just a matter of concatenting strings.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, April 21, 2019 9:42 PM
  • Hi Hugh Self Taught,

    Thank you for your posting .

     

    Per your description, I think your issue might be the above script . In above script, it will get following WHERE clause.

     
    ----(@Male Is Not NULL OR @Female Is Not NULL) and @LastResult Is NULL 
    WHERE C.Comp_Date >= @StartDate 
    And T.PtsMale = @Male And T.PtsFemale = @Female 
    And T.PtsMale + T.PtsFemale > 0
    
    ----(@Male Is Not NULL OR @Female Is Not NULL)  and @LastResult Is Not NULL 
    WHERE C.Comp_Date >= @StartDate 
    And T.PtsMale = @Male And T.PtsFemale = @Female 
    And T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 
    

    Could you please share your static query? So that we’ll get a right direction and make some test. 

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 22, 2019 2:49 AM
  • Hi Rachel, Thanks for responding. After a night sleep I've looked at this again & I think I need to use the following method

    IF @LastResult is not NULL BEGIN SET @SQL = 'SELECT H.* FROM tblPtsPerCompHistory H WHERE ( SELECT T.* FROM tblPtsPerCompHistory T INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID WHERE T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 And C.Comp_Date >= @StartDate) WHERE H.PtsMale = T.PtsMale And H.PtsFemale = T.PtsFemale ' END

        PRINT @SQL
        EXEC sp_executesql @SQL,N'@LastResult int,@Male int, @Female int, @StartDate Date',@LastResult,@Male,@Female,@StartDate

    There's something I'm not doing right with this query as I get the error
    SELECT H.* 
    			FROM tblPtsPerCompHistory H
    			WHERE
    			(
    			SELECT T.*
    			FROM tblPtsPerCompHistory T
    			INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID 
    			WHERE T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 And C.Comp_Date >= @StartDate) 
    
    			WHERE H.PtsMale = T.PtsMale And H.PtsFemale = T.PtsFemale 
    Msg 4145, Level 15, State 1, Line 10
    An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'.

    Can you spot what it is please. I think I'm confusing myself


    Monday, April 22, 2019 11:38 AM
  • Your first WHERE clause is wrong.

    Write your query static first and make it work then convert it to dynamic.

    Monday, April 22, 2019 1:49 PM
    Moderator
  • Hi Rachel,

    I managed to get the required results using Joins instead as follows

    	IF @LastResult is not NULL
    		BEGIN
    			SET @SQL = 'INSERT INTO dbo.tblPtsPerCompHistoryTemp
    			SELECT H.* 
    				FROM tblPtsPerCompHistory H
    				INNER JOIN 
    				(
    				SELECT *
    				FROM tblPtsPerCompHistory AS T
    				INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID And T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0 And C.Comp_Date >= @StartDate
    				) As T ON T.PtsMale = H.PtsMale And T.PtsFemale = H.PtsFemale '
    		END
    
    	ELSE IF @LastResult is NULL
    		BEGIN
    			SET @SQL = 
    				'INSERT INTO dbo.tblPtsPerCompHistoryTemp
    				SELECT T.*
    				FROM dbo.tblPtsPerCompHistory AS T
    				INNER JOIN tblCompetitions AS C ON C.Competition_Idx = T.PtsCompID 
    				'
    			SET @WHERE = 'WHERE C.Comp_Date >= @StartDate '
    
    			IF @Male Is Not NULL OR @Female Is Not NULL
    					SET @WHERE = @WHERE + 'And T.PtsMale = @Male And T.PtsFemale = @Female '
    					SET @SQL = @SQL + @WHERE
    		END
    
    	EXEC sp_executesql @SQL,N'@LastResult int,@Male int, @Female int, @StartDate Date',@LastResult,@Male,@Female,@StartDate

    Monday, April 22, 2019 1:50 PM
  • I think this could be refactored to not use dynamic SQL.  Does this work?

    INSERT INTO dbo.tblPtsPerCompHistoryTemp
    SELECT T.*
    FROM dbo.tblPtsPerCompHistory AS T
    WHERE EXISTS ( -- Don't need a join since nothing is returned from this table
    			SELECT 1 FROM tblCompetitions AS C WHERE C.Competition_Idx = T.PtsCompID AND C.Comp_Date >= @StartDate
    			)
    	AND (
    		(@Male IS NULL OR T.PtsMale = @Male)
    		AND
    		(@Female IS NULL OR T.PtsFemale = @Female)
    		AND
    			(
    			(@LastResult IS NOT NULL AND T.PtsCompID = @LastResult And T.PtsMale + T.PtsFemale > 0)
    			OR
    			(@LastResult IS NULL AND T.PtsMale + T.PtsFemale > 0)
    			)
    		)
    		

    Monday, April 22, 2019 2:39 PM
  • Hi Bill, This gives me all the records where the PtsCompID = @LastResult. The solution I managed to make gives me all the records for PtsMale & PtsFemale who have a record where PtsCompID = @LastResult
    Monday, April 22, 2019 4:11 PM
  • Tough to troubleshoot without test data, but I still think there's a way to do this with a static query. Maybe

    INSERT INTO dbo.tblPtsPerCompHistoryTemp
    SELECT T.*
    FROM dbo.tblPtsPerCompHistory AS T
    WHERE EXISTS ( -- Don't need a join since nothing is returned from this table
    			SELECT 1 FROM tblCompetitions AS C WHERE ISNULL(@LastResult,C.Competition_Idx) = T.PtsCompID AND C.Comp_Date >= @StartDate
    			)
    		AND (
    			(@LastResult IS NOT NULL AND T.PtsMale + T.PtsFemale > 0)
    			OR
    			(@LastResult IS NULL AND T.PtsMale = ISNULL(@Male, T.PtsMale) AND T.PtsFemale = ISNULL(@Female, T.PtsFemale) )
    			)

    Monday, April 22, 2019 8:09 PM
  • Hi Hugh Self Taught,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 7:21 AM