user3172066 user3172066 - 5 months ago 15
SQL Question

Create stored procedure from another stored procedure

I have a stored procedure like:

create procedure [dbo].[Mark]
SELECT
NAME, TOT1, TOT2, TOT3, (TOT1+TOT2+TOT3)/3 AS Average
FROM
(SELECT
NAME,
ISNULL([1],0) AS TOT1,
ISNULL([2],0) AS TOT2,
ISNULL([3],0) AS TOT3
FROM
(SELECT
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) RN,
NAME,
TOT
FROM
T) S
PIVOT (MAX(TOT) FOR RN IN ([1],[2],[3])) AS PVT
) T


The output of this stored procedure is:

name Tot1 Tot2 Tot3 Avg
-------------------------------
a 50 30 45 42
b 30 0 0 10


From this stored procedure, I want to create another stored procedure to get record the Average between 20-50. How to get? I try with temp table. But I could not access temp table to another stored procedure

My output must be

name Tot1 Tot2 Tot3 Avg
---------------------------------
a 50 30 45 42

Answer
DECLARE @MyTableVar TABLE(
    name VARCHAR(20),
    Tot1 [decimal](15, 6),
    Tot2 [decimal](15, 6),
    Tot3 [decimal](15, 6),
    Avg [decimal](15, 6))

INSERT INTO @MyTableVar EXECUTE sp_executesql [Mark]

SELECT * FROM @MyTableVar WHERE Avg BETWEEN 20 AND 50

Just replace the temporary table @MyTableVar with the data types that you need. I haven't tested this but I'm pretty sure sp_executesql is what your looking for.

EDIT: UPDATE the datatypes for you.