Nesting a SP within another SP?

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)
ENDlaurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401272245.449a0756@.posting.google.com>...
> 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)
> END

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.

Simon

Nested Parent-Child Packages

I have a fairly simple SSIS project that has nested parent-child packages.
I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own.

For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much.

I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility.

However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package.

Though I'm sure there are others, the only two solutionsI can think of now are
(a)don't nest packages beyond 1 parent/child relationship -- not really an option or
(b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome.

So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something.

Is there a better way I am just not getting?

In a similar situation, I put all the 3rd level child packages in the same folder. Then I used a parent package configuration to pass a root path variable from the 1st level master to the second level master. I used an expression on the connectionstring property for each file system connection that concatenated the root path with the name of the package.

It worked pretty well for me, but there are some limits with parent package configurations, since they are evaluated later in the process than other types of configurations (hopefully something that will be addressed in Katmai).

Nested inserts with TSQL

Hi I have to strings that can both each contain an indeterminable length. These strings are

UserID
NoteID

and will contain something like

UserID = '1, 2, 3, 4'
NoteID = '4, 9, 18, 21, 23, 27'

However its not known the length of each and so we could have the reverse of the above.

I am to insert x amount of notes to one userId and vice versa, but I'm trying to figure out how to do both so the insert would resemble the following.

UserID NoteID

1 4
1 9
1 18
1 21
1 23
1 27
2 4
2 9
etc, etc

This is sp that does it and it works fine for just one or the other, I just don't have much experience in this kind of thing. The spInsertAssignedNoteDetail at the end simply makes the insert when I have both numbers.

Heres my attempt, but i'm just stuck as to where to go from here

CREATE PROCEDURE spInsertAssignedNotesByList
@.FK_UserIDList NVARCHAR(4000) = NULL,
@.FK_NoteIDList NVARCHAR(4000) = NULL

AS
SET NOCOUNT ON

DECLARE @.Length INT
DECLARE @.Note_Length INT

DECLARE @.FirstUserIDWord NVARCHAR(4000)
DECLARE @.FK_UserID INT
DECLARE @.FK_NoteID INT

SELECT @.Length = DATALENGTH(@.FK_UserIDList )
SELECT @.Note_Length = DATALENGTH(@.FK_NoteIDList )

WHILE @.Length > 0 or @.Note_Length > 0
BEGIN
EXECUTE @.Length = PopFirstWord @.FK_UserIDList OUTPUT, @.FirstUserIDWord OUTPUT

IF @.Length > 0
BEGIN
SELECT @.FK_UserID = CONVERT(INT, @.FirstUserIDWord)

EXECUTE spInsertAssignedNoteDetail @.FK_UserID, @.FK_NoteID
END
END
----------------
GOOk I got this far, which does what I want with the first loop but when it comes to do the second outer do while for some reason the counter is zero instead of going backing to what it was when it started. Anyone know why it does this. Thanks

CREATE PROCEDURE spInsertAssignedNotesByList
@.FK_UserIDList NVARCHAR(4000) = NULL,
@.FK_NoteIDList NVARCHAR(4000) = NULL

AS
SET NOCOUNT ON

DECLARE @.Length INT
DECLARE @.Note_Length INT

DECLARE @.FirstUserIDWord NVARCHAR(4000)
DECLARE @.FirstNoteIDWord NVARCHAR(4000)

DECLARE @.FK_UserID INT
DECLARE @.FK_NoteID INT

SELECT @.Length = DATALENGTH(@.FK_UserIDList )
SELECT @.Note_Length = DATALENGTH(@.FK_NoteIDList )

WHILE @.Length > 0
BEGIN

IF @.Length > 0
EXECUTE @.Length = PopFirstWord @.FK_UserIDList OUTPUT, @.FirstUserIDWord OUTPUT
SELECT @.FK_UserID = CONVERT(INT, @.FirstUserIDWord)

WHILE @.Note_Length > 0
BEGIN
EXECUTE @.Note_Length = PopFirstWord @.FK_NoteIDList OUTPUT, @.FirstNoteIDWord OUTPUT
SELECT @.FK_NoteID = CONVERT(INT, @.FirstNoteIDWord)

IF @.Note_Length > 0
EXECUTE spInsertAssignedNoteDetail @.FK_UserID, @.FK_NoteID
END
END
----------------
GO