sql server (alternate): Nesting a SP within another SP? -- sql server (alternate)

Nesting a SP within another SP?

laurenquantrell NO[at]SPAM hotmail.com
1/27/2004 10:45:09 PM

sql server (alternate):

I have a stored procedure that calls some UDF User Defined Functions,
the purpose of which is to create row strings out of numerous column
strings for matching uniqueIDs.

The problem is I need to join that SP with some other tables.

The SP I have reads something like:

mySPName
@myUserID int
SELECT myUniqueID, dbo.fn_myFunctionName(UniqueID) As myRunningString
FROM myTEMPTableName
GROUP BY myUniqueID
WHERE myTEMPTableName.UserID = @myUserID

I need to join that result with myTableName on myUniqueID such as:
Select myTableName.myField1, myTableName.myField2,
mySPName.myRunningString
From ...
-- joining myTableName.myUniqueID = mySPName.myUniqueID

Can this be done?
The reason I don't just do it with a View instead of an SP is that I
have that parameter that must be passed to filter the records in
myTEMPTableName.

Any help is appreciated.
lq

oh...
the UDF looks like:

Create Function dbo.fn_myFunctionName(@myUniqueID as int) returns
nvarchar(500)
AS
BEGIN
DECLARE @ret_value nvarchar(500)
SET @ret_value=''
SELECT @ret_value=@ret_value + ';' + myString
FROM myTEMPTableName
WHERE
myUniqueID =@myUniqueID
RETURN RIGHT(@ret_value,Len(@ret_value)-2)

Re: Nesting a SP within another SP?

sql NO[at]SPAM hayes.ch
1/28/2004 2:25:35 AM

[quoted text, click to view]

There are some options described here:

http://www.sommarskog.se/share_data.html

From your description, rewriting the stored procedure as a
table-valued UDF sounds like it should be possible.