calling a procedure from another procedure

Maybe this isn't possible, but I'm trying to figure out how to call a procedure in a select statement where I can pass a new id each record found. Here is the procedure code that returns the desired results for any given ID:

CREATE PROCEDURE proc_amEnrollmentTrackingDetailGet_cst
@.syStudentID INTEGER
AS

DECLARE
@.SATACT DATETIME,
@.HSTranscript DATETIME,
@.AcceptLetter DATETIME,
@.FAFSA DATETIME,
@.FinAidApp DATETIME

--Get the last DateDue for each document type

SELECT
@.SATACT = CASE WHEN cmDocTypeID IN (81,110) THEN DateDue END,
@.HSTranscript = CASE WHEN cmDocTypeID IN (83) THEN DateDue END,
@.AcceptLetter = CASE WHEN cmDocTypeID IN (126) THEN DateDue END,
@.FAFSA = CASE WHEN cmDocTypeID IN (150) THEN DateDue END,
@.FinAidApp = CASE WHEN cmDocTypeID IN (155) THEN DateDue END
FROM
cmDocument (NOLOCK)
WHERE
syStudentID = @.syStudentID
ORDER BY
cmDocTypeID, DateDue

-- Return the results

SELECT
S.syStudentID,
S.StuNum,
@.SATACT AS SATACT,
@.HSTranscript AS HSTranscript,
@.AcceptLetter AS AcceptLetter,
@.FAFSA AS FAFSA,
@.FinAidApp AS FinAidApp,
S.FirstName,
S.LastName
FROM
syStudent S (NOLOCK)

WHERE S.syStudentID = @.syStudentID
GO

Now maybe there is a better way to do this, but what I want to do is to call the above procedure and have it return a row for every ID in the following code:

SELECT DISTINCT
S.syStudentID
FROM
syStudent S (NOLOCK)
JOIN cmActivity_vw A (NOLOCK)
ON A.syStudentID = S.syStudentID
AND A.cmTemplateID IN (16,195,283,290,291) --Application received
WHERE
S.StartDate BETWEEN '5/1/07' AND '9/1/07' -- Expected Start Date
AND S.amPrevEducID = 1 --High School Grad
AND S.syCampusID = 5 --Main Campus

Create a table valued function instead of a procedure.

See SQL Server 2005 Books Online topics:
CREATE FUNCTION (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186755.aspx

Using APPLY
http://msdn2.microsoft.com/en-us/library/ms175156.aspx

|||

Why do you even need a stored procedure? Why not merely use a larger query, e.g.,
SELECT
S.syStudentID,
S.StuNum,
CASE WHEN cmDocTypeID IN (81,110) THEN DateDue END as SATACT,
CASE WHEN cmDocTypeID IN (83) THEN DateDue END as HSTranscript,
CASE WHEN cmDocTypeID IN (126) THEN DateDue END as AcceptLetter,
CASE WHEN cmDocTypeID IN (150) THEN DateDue END as FAFSA,
CASE WHEN cmDocTypeID IN (155) THEN DateDue END as Fin AidApp,
S.FirstName,
S.LastName
FROM syStudent S (NOLOCK)
INNER JOIN
(SELECT DISTINCT
S.syStudentID
FROM syStudent S (NOLOCK)
JOIN cmActivity_vw A (NOLOCK)
ON A.syStudentID = S.syStudentID
AND A.cmTemplateID IN (16,195,283,290,291) --Application received
WHERE S.StartDate BETWEEN '5/1/07' AND '9/1/07' -- Expected Start Date
AND S.amPrevEducID = 1 --High School Grad
AND S.syCampusID = 5 --Main Campus
) TMP
on S.syStudentID = TMP.syStudentID
INNER JOIN cmDocument D (NOLOCK)
ON
D.syStudentID = S.syStudentID

Dan

|||

Dan:

One reason to use a stored procedure instead of a function is to encapsulate security -- specifically to dis-allow ad hoc ability to table yet still provide needed functionality. We have a number of clients that have in their standards that (1) rank-and-file users must not have any access to table components of databases and (2) all database data access be given by another means -- such as stored procedures or functions. There are also other reasons.

|||

Waldrop,

I certainly agree that if the user cannot use a Query that includes a sub-Query, because it violates some security regulation, then the solution I proposed is unsatisfactory.

I could not tell from the preceding posts that such security issues were involved, so I suggested what seems to me to be the simplest solution to obtain the desired dataset.

Dan

|||

Ok, I have created the following function:

CREATE FUNCTION dbo.fn_amEnrollmentTrackingDetailGet_cst (@.syStudentID INTEGER)
RETURNS @.amTrackingDetail TABLE (
syStudentID INTEGER,
SATACT DATETIME,
HSTranscript DATETIME,
AcceptLetter DATETIME,
FAFSA DATETIME,
FinAidApp DATETIME)
AS

BEGIN
DECLARE
@.SATACT DATETIME,
@.HSTranscript DATETIME,
@.AcceptLetter DATETIME,
@.FAFSA DATETIME,
@.FinAidApp DATETIME

SELECT
@.SATACT = CASE WHEN cmDocTypeID IN (81,110) THEN DateDue END,
@.HSTranscript = CASE WHEN cmDocTypeID IN (83) THEN DateDue END,
@.AcceptLetter = CASE WHEN cmDocTypeID IN (126) THEN DateDue END,
@.FAFSA = CASE WHEN cmDocTypeID IN (150) THEN DateDue END,
@.FinAidApp = CASE WHEN cmDocTypeID IN (155) THEN DateDue END
FROM
cmDocument (NOLOCK)
WHERE
syStudentID = @.syStudentID
ORDER BY
cmDocTypeID, DateDue

INSERT INTO @.amTrackingDetail
SELECT
S.syStudentID,
@.SATACT AS SATACT,
@.HSTranscript AS HSTranscript,
@.AcceptLetter AS AcceptLetter,
@.FAFSA AS FAFSA,
@.FinAidApp AS FinAidApp
FROM
syStudent S (NOLOCK)

WHERE S.syStudentID = @.syStudentID
RETURN
END

But when I try to reference that function in a SELECT statement like:

SELECT
dbo.fn_amEnrollmentTrackingDetailGet_cst(SyStudentID)
FROM
syStudent

I get: Invalid object name 'dbo.fn_amEnrollmentTrackingDetailGet_cst'.

|||

Charles:

You have defined a "Table valued function" and in your select statement you are referencing it like a "scalar function." Look at the CREATE FUNCTION article in books online to see the difference.

|||

This is an example using a table function from the 2005 AdventrueWorks database.

SELECT C.*

FROM HumanResources.Employee AS E

CROSS APPLY dbo.ufnGetContactInformation(EmployeeID)AS C

|||

Dan,

Thank you very much for your post. It was very helpful, especially here at the beginning of this project when we are verifying the dataset for accuracy. However, another reason that I am attracted to the function paradigm is that it seems that it would allow me to easily reference that function in other solutions.

|||

Charles:

Try altering your function call to something like:

select tf.syStudentID,
tf.SATACT,
tf.HSTranscript,
tf.AcceptLetter,
tf.FAFSA,
tf.FinAidApp
from syStudent a
cross apply fn_amEnrollmentTrackingDetailGet_cst(SyStudentID) tf

|||

I also saw another example of this at http://msdn2.microsoft.com/en-us/library/ms186755.aspx, but when I try the following code with a declared table, I get 'Line 22: Incorrect syntax near 'APPLY'.

DECLARE @.TableApplied TABLE (
syStudentID INTEGER)

INSERT @.TableApplied
SELECT DISTINCT
S.syStudentID
FROM
syStudent S (NOLOCK)
JOIN cmActivity_vw A (NOLOCK)
ON A.syStudentID = S.syStudentID
AND A.cmTemplateID IN (16,195,283,290,291) --Application received
-- and A.eventstatus = 'Closed'
WHERE
S.StartDate BETWEEN '8/1/07' AND '9/1/07' -- Expected Start Date
AND S.amPrevEducID = 1 --High School Grad
AND S.syCampusID = 5 --Main Campus

SELECT
C.*
FROM
@.TableApplied AS A
CROSS APPLY dbo.fn_amEnrollmentTrackingDetailGet_cst(A.SyStudentID) AS C

|||I noticed that the database compatibility level has to be 90 in order for CROSS APPLY to work. Ours is set to 80 and 90 is not an option in the drop down. :(|||If you are in 80 compatibility and cannot use CROSS APPLY, consider something like modifying your function to accept a NULL studentID as an argument; when a null student id is sent to the UDF return all of the rows from the table instead of rows for the specific student. Then you can INNER JOIN this to your syStudent table based on StudentID?