Using result of a SQL statement as parameter for another SQL statement

Question

I have this SQL query:

select distinct Project_Id, keyword, SE_Id 
from [RL].[SearchMetrics_ProjectKeyword]

The result of this query should be used in the following complex query (where I put @ProjectID, @Keyword and @SEID):

with DateWithValue as 
(
    SELECT  
        dt.Date_ID,
        LEAD(Load_Date,1,0)OVER (ORDER BY Load_Date) as  Date_ID_AFTER,
        [keyword]
        ,[DWH_ServerExecutionID]
        ,[DWH_LoadTime]
        ,[Load_Date]
        ,TopKey100=
        case 
            when [Tags] like '%prio%' and [pos_position]!=''   then [pos_pos ition]
            when [Tags] like '%prio%' and [pos_position]=''   then NULL
            else NULL
         end
        ,TopKey400=
        case
           when [pos_position]='' then NULL
           else pos_position
        end
        ,[pos_position]=
        case
           when [pos_position]='' then NULL
          else  pos_position
        end
        ,[pos_traffic]=
        case
           when [pos_traffic]='' then NULL
          else [pos_traffic]
        end
        ,[tags]
        ,[trend_date]
        ,[SE_Id]
        ,[Domain]
        ,[dbo].[CleanseURL]([Pos_url]) as Pos_url
        ,pr.Company_BK
        ,pr.Project_URL
        ,PK.Project_ID
    FROM 
        DimDate AS dt 
    LEFT JOIN 
        [RL].[SearchMetrics_ProjectKeyword] AS PK ON dt.Date_ID = PK.Load_Date 
                                                  AND PK.Project_Id = @ProjectID 
                                                  AND Pk.keyword = @Keyword 
                                                  AND SE_Id = @SEID
    LEFT JOIN 
        MDM.SearchMetrics_Project AS pr ON PK.Project_ID = pr.Project_ID
    WHERE
        PK.Domain IS NOT NULL
        AND dt.Date_ID >= (SELECT MIN(Load_Date) 
                           FROM [RL].[SearchMetrics_ProjectKeyword] 
                           WHERE Project_Id = @ProjectID 
                             AND keyword = @Keyword 
                             AND SE_Id = @SEID)
        AND dt.Date_ID <= (SELECT MAX(Load_Date) 
                           FROM [RL].[SearchMetrics_ProjectKeyword] 
                           WHERE Project_Id = @ProjectID 
                             AND keyword = @Keyword 
                             AND SE_Id = @SEID)
    --order by Date_ID
)
SELECT 
    dt.Date_ID, DateWithValue.Domain,
    DateWithValue.keyword, DateWithValue.pos_position,
    DateWithValue.Load_Date, DateWithValue.pos_traffic, 
    DateWithValue.Company_BK, DateWithValue.Pos_url,
    DateWithValue.Project_Id, DateWithValue.Project_URL,
    DateWithValue.SE_Id, DateWithValue.tags,
    DateWithValue.TopKey100, DateWithValue.TopKey400,
    DateWithValue.trend_date 
FROM
    DimDate AS dt 
LEFT JOIN
    DateWithValue ON dt.Date_ID >= DateWithValue.Date_ID 
                  AND dt.Date_ID < DateWithValue.Date_ID_AFTER
WHERE
    DateWithValue.Date_ID IS NOT NULL
ORDER BY
    dt.Date_ID

Do you have any idea or suggestion, how can I reach this aim in an optimal way?

I don't think you need to use those variables since your first query deos not have a WHERE clause, what you really need is to pass a table-valued instead of those parameters and join it with [RL].[SearchMetrics_ProjectKeyword] table. — Ilyes
– Ilyes, Commented Nov 17, 2018 at 11:30
I'm not sure if the first results should be passed as a parameter since your code is not an SP nor a function, mybe you are looking for a CTE or even a SubQuery, Table-Valued also can be used there. Also this line is confuse me a bit FROM DimDate as dt LEFT JOIN [RL].[SearchMetrics_ProjectKeyword] as PK on dt.Date_ID=PK.Load_Date and PK.Project_Id=@ProjectID and Pk.keyword=@Keyword and SE_Id=@SEID Are you looking to pass on value? or many values? — Ilyes
– Ilyes, Commented Nov 17, 2018 at 11:37
Possible duplicate of Using result of a query in input parameters for a common table expression — SMor
– SMor, Commented Nov 17, 2018 at 14:29
The technique is still exactly the same. Put the first query into another CTE or derived table - or dump the result into a temp table. And all those left joins are mostly unnecessary (perhaps all of them are). It is also highly suspicious that you reference DimDate twice. And while we at it, BE CONSISTENT! Add the appropriate alias to every column you use - not just some of them. — SMor
– SMor, Commented Nov 17, 2018 at 18:23

Paul Maxwell · Accepted Answer · 2023-04-18 06:01:12Z

Without access to any data it's impossible to verify that this suggestion is functionally equivalent to your existing query. But, it seems you want a full set of dates (from a dimension table) with metrics when the data falls between a date range per project. Instead of attempting to do this in a row-by-row fashion (which is not the best way to use SQL) join the dimension and the metrics, but do this via a "derived table" that includes the needed min/max date ranges. e.g.

SELECT
      dt.Date_ID
    , PK.Domain
    , PK.keyword
    , pos_position = CASE WHEN PK.pos_position = '' THEN NULL ELSE PK.pos_position END
    , PK.Load_Date
    , pos_traffic = CASE WHEN PK.pos_traffic = '' THEN NULL ELSE PK.pos_traffic END
    , pr.Company_BK
    , dbo.CleanseURL(Pos_url) AS Pos_url /* user function */
    , PK.Project_ID
    , pr.Project_URL
    , PK.SE_Id
    , PK.tags
    , TopKey100 = CASE WHEN PK.Tags LIKE '%prio%'
            AND PK.pos_position != '' THEN PK.pos_pos ition WHEN Tags LIKE '%prio%'
            AND PK.pos_position = '' THEN NULL 
           ELSE NULL END
    , TopKey400 = CASE WHEN PK.pos_position = '' THEN NULL
            ELSE PK.pos_position END
    , trend_date
FROM DimDate AS dt
INNER JOIN (
    SELECT
          *
        , MIN(Load_Date) OVER (PARTITION BY Project_Id, keyword, SE_Id) AS max_Load_Date
        , MAX(Load_Date) OVER (PARTITION BY Project_Id, keyword, SE_Id) AS min_Load_Date
    FROM RL.SearchMetrics_ProjectKeyword
    ) AS PK ON dt.Date_ID = PK.Load_Date
LEFT JOIN MDM.SearchMetrics_Project AS pr ON PK.Project_ID = pr.Project_ID
WHERE PK.Domain IS NOT NULL
    AND dt.Date_ID >= pk.min_Load_Date
    AND dt.Date_ID <= pk.max_Load_Date
ORDER BY dt.Date_ID

Notes

your where clause PK.Domain IS NOT NULL forces each row to have a match between the tables, hence it is useless using a left join. You would need to relax this condition if you actually need the left join.
you really should include the relevant table alias to all column references, I have assumed this is PK. if it was absent.

Collectives™ on Stack Overflow

Using result of a SQL statement as parameter for another SQL statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related