CODEWITHSUNDEEP
sqlstored-proceduressubqueryazure-sql-database
Mike S
Mike S

Reputation: 157

SQL Azure Stored Procedure Multiple result sets

I am creating a stored procedure for initial page load and thus have two different queries and result sets.

The first query is entirely repeated as a nested SELECT inside the second query.

My question then is whether to put the results of the first query into a table var or to just essentially duplicate the code/query in the second query?

Thanks in advance.

--Get Most Popular Sports for each country
    SELECT
        dbo.Countries2Sports.rank,
        dbo.Countries2Sports.SportName,
        dbo.Countries2Sports.imageURL,
        dbo.Countries2Sports.imageALTtext
    FROM
        dbo.Countries2Sports
    WHERE
        dbo.Countries2Sports.CountryCode = @CountryCode
    ORDER BY
        dbo.Countries2Sports.rank ASC

    --Get Organizations related to each country's sports
    SELECT
        dbo.Countries2Organizations.ID,
        dbo.Countries2Organizations.OrganizationCode,
        dbo.Countries2Organizations.OrganizationName,
        dbo.Countries2Organizations.LogoURL,
        dbo.Countries2Organizations.Sport
    FROM
        dbo.Countries2Organizations
    WHERE
        dbo.Countries2Organizations.Sport IN 
            (
                SELECT
                    dbo.Countries2Sports.SportName
                FROM
                    dbo.Countries2Sports
                WHERE
                    dbo.Countries2Sports.CountryCode = @CountryCode
            )
      AND (dbo.Countries2Organizations.CountryCode = @CountryCode OR 
           dbo.Countries2Organizations.CountryCode = '')

Upvotes: 0

Views: 126

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

If you do not want result sets in output then you can use CTE which will be better than temp table if number of records are low as below:

;WITH CTE AS (
    SELECT
        dbo.Countries2Sports.rank,
        dbo.Countries2Sports.SportName,
        dbo.Countries2Sports.imageURL,
        dbo.Countries2Sports.imageALTtext

    FROM
        dbo.Countries2Sports
    WHERE
        dbo.Countries2Sports.CountryCode = @CountryCode
    --ORDER BY
    --    dbo.Countries2Sports.rank ASC
)
--Get Organizations related to each country's sports
SELECT
    dbo.Countries2Organizations.ID,
    dbo.Countries2Organizations.OrganizationCode,
    dbo.Countries2Organizations.OrganizationName,
    dbo.Countries2Organizations.LogoURL,
    dbo.Countries2Organizations.Sport
FROM
    dbo.Countries2Organizations
WHERE
    dbo.Countries2Organizations.Sport IN 
        (
            SELECT
                dbo.Countries2Sports.SportName
            FROM
                CTE
        )
AND (   dbo.Countries2Organizations.CountryCode = @CountryCode  OR dbo.Countries2Organizations.CountryCode = '')

Upvotes: 3

Related Questions