How to Join on a Stored Procedure

How can I join on a stored procedure?

I actually like the previous answer (don't use the SP), but if you're tied to the SP itself for some reason, you could use it to populate a temp table, and then join on the temp table. Note that you're going to cost yourself some additional overhead there, but it's the only way I can think of to use the actual stored proc.

Again, you may be better off in-lining the query from the SP into the original query.

How to Execute a Stored Procedure in a Join Statement SQL Server

Use a scalar function instead.

CREATE FUNCTION GETID
(
@id int
)
RETURNS int
AS
BEGIN
return (select top 1 id from test1 where testid=@id)
END

Or, review methods in post: Get top 1 row of each group

Use cross apply (or outer apply), which executes once on right side of query.
Or, use row_number() over partition to rank the group rows and select based on rank.

declare @test table (id int, name varchar(100))
insert into @test (id, name) values (1, 'abc')
insert into @test (id, name) values (1, 'te')
declare @test1 table (id int, testid int, name varchar(100))
insert into @test1 (id, testid, name) values (1, 1, 'xxx')
insert into @test1 (id, testid, name) values (2, 1, 'yyy')
insert into @test1 (id, testid, name) values (3, 1, 'zzz')
insert into @test1 (id, testid, name) values (4, 2, 'aaa')

Select * from @test t
cross apply (select top 1 * from @test1
          where testid = t.id
          order by id) -- change group order as needed
          as t1

Can we make join between two stored procedures in SQL Server

You can put result sets from both SP into temp tables and then join them:

CREATE TABLE #PackageAccept  (
    Id INT,
    PackageNumber INT,
    Size INT,
    Code NVARCHAR(100),
    TestPackageOrder INT
    --etc 
    --Here you add all columns from SP output with there datatypes
)

Then you can:

INSERT INTO #PackageAccept
EXEC [dbo].[TestPackageAccept]

The same way for second SP, then join:

SELECT *
FROM #PackageAccept pa
INNER JOIN #PackageProgress pp
    ON pa.id = pp.packageid

Don't forget to DROP temp tables:

DROP TABLE #PackageAccept
DROP TABLE #PackageProgress

The full batch will be like:

IF OBJECT_ID(N'#PackageAccept') IS NOT NULL 
BEGIN
    DROP TABLE #PackageAccept
END
ELSE
BEGIN 
    CREATE TABLE #PackageAccept (
        Id INT,
        PackageNumber INT,
        Size INT,
        Code NVARCHAR(100),
        TestPackageOrder INT
        --etc
        )
END

IF OBJECT_ID(N'#PackageProgress') IS NOT NULL 
BEGIN
    DROP TABLE #PackageProgress
END
ELSE
BEGIN 
    CREATE TABLE #P (
        PackageId INT,
        packagenumber INT,
        [Total] INT,
        Accept INT,
        Remain INT
        --etc
        )
END

INSERT INTO #PackageAccept
EXEC [dbo].[TestPackageAccept]

INSERT INTO #PackageProgress
EXEC [dbo].[TestPackageProgress]

SELECT *
FROM #PackageAccept pa
INNER JOIN #PackageProgress pp
    ON pa.id = pp.packageid

How to make a join with the stored procedure result?

The answer is that you should use an 'iterator function' in the FROM clause:

FROM TABLE(FUNCTION stored_procedure(arg1, arg2)) AS alias(col1, col2, ....)

The 'alias' is not always necessary, but look hard at the column names in the output of executing the procedure directly:

EXECUTE stored_procedure(arg1, arg2)

If that contains unique column names, you'll be OK; if not, you won't and you'll need the AS alias(col1, col2, ...) notation.

---

Here's an analogue (more or less) to your query.

Trivial function that could be written differently, but works:

CREATE FUNCTION SimpleElements(lo INTEGER, hi INTEGER) RETURNING INTEGER AS Atomic_Number;
    DEFINE an INTEGER;
    FOREACH SELECT Atomic_Number
              INTO an
              FROM Elements
             WHERE Atomic_Number BETWEEN lo AND hi
             ORDER BY Atomic_Number
        RETURN an WITH RESUME;
    END FOREACH;
END FUNCTION;

Query using TABLE(FUNCTION stored_procedure(arg1, arg2)):

The MOD function is used as simple way of generating some groups to count, but it illustrates the notation which is the main point of the exercise.

SELECT COUNT(*) AS group_count, MOD(b.atomic_number, 3) AS mod_num
  FROM TABLE(FUNCTION SimpleElements(1, 10)) AS a(Atomic_Number)
  JOIN Elements AS b
    ON a.atomic_number = b.atomic_number
 GROUP BY mod_num
 ORDER BY mod_num;

Output:

3       0
4       1
3       2

Table of Elements

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 'Y');
INSERT INTO elements VALUES(  2, 'He',  'Helium',          4.0026, 'Y');
INSERT INTO elements VALUES(  3, 'Li',  'Lithium',         6.9410, 'Y');
INSERT INTO elements VALUES(  4, 'Be',  'Beryllium',       9.0122, 'Y');
INSERT INTO elements VALUES(  5, 'B',   'Boron',          10.8110, 'Y');
INSERT INTO elements VALUES(  6, 'C',   'Carbon',         12.0110, 'Y');
INSERT INTO elements VALUES(  7, 'N',   'Nitrogen',       14.0070, 'Y');
INSERT INTO elements VALUES(  8, 'O',   'Oxygen',         15.9990, 'Y');
INSERT INTO elements VALUES(  9, 'F',   'Fluorine',       18.9980, 'Y');
INSERT INTO elements VALUES( 10, 'Ne',  'Neon',           20.1800, 'Y');
INSERT INTO elements VALUES( 11, 'Na',  'Sodium',         22.9900, 'Y');
INSERT INTO elements VALUES( 12, 'Mg',  'Magnesium',      24.3050, 'Y');
INSERT INTO elements VALUES( 13, 'Al',  'Aluminium',      26.9820, 'Y');
INSERT INTO elements VALUES( 14, 'Si',  'Silicon',        28.0860, 'Y');
INSERT INTO elements VALUES( 15, 'P',   'Phosphorus',     30.9740, 'Y');
INSERT INTO elements VALUES( 16, 'S',   'Sulphur',        32.0650, 'Y');
INSERT INTO elements VALUES( 17, 'Cl',  'Chlorine',       35.4530, 'Y');
INSERT INTO elements VALUES( 18, 'Ar',  'Argon',          39.9480, 'Y');
…

Conditional Join in SQL Server stored procedure

The left join is fine. But you can also use exists:

Select u.*
from users u
where (u.filter1 = 1 and
       exists (select 1 from filtertable1 ft where u.filtercount1 = ft.filtercount1)
      ) or
      (u.filter2 = 1 and
       exists (select 1 from filtertable2 ft where u.filtercount2 = ft.filtercount1)
      ) or
      (u.filter3 = 1 and
       exists (select 1 from filtertable3 ft where u.filtercount3 = ft.filtercount1)
      ) ;

Note: I'm not sure if you want and or or between the conditions. With and the logic would be slightly different.

Inner Join in stored procedure

You need to define reference for you UnitsTbl. Think of these references as new objects of same class, in terms of OOP. Also, it is a good habit to use references for whenever querying over a table.

SELECT *
FROM 
    ItemsMainTbl t1
INNER JOIN
    UnitsTbl u1 ON t1.FirstUnit = u1.UnitID 
INNER JOIN
    UnitsTbl u2 ON t1.SecondUnit = u2.UnitID 
INNER JOIN 
    UnitsTbl u3 ON t1.ThirdUnit = u3.UnitID

stored procedure ( inner join from same table)

Consider using a row-limiting query:

create proc precipitacao_maxima_data 
    @data date  
as
    select top (1) t.*
    from weather_station.precipitacao t
    where data_observacao = @data
    order by valor_observacao desc;

This simply selects all rows whose data_observacao matches the given @data parameter, sorts them by descending valor_observacao, and retains the first row only.

---

Related Topics