Executing SP inside SP dynamically

I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!In Books OnLine, look up the keywords OUTPUT variable and RETURN.|||Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.|||I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues

Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!

Try This

DECLARE @.sql nvarchar(2048)
SET @.sql = ' SET @.count = ( SELECT COUNT(*) FROM table1 )'
DECLARE @.temp int
EXEC sp_executesql @.sql, N'@.count int OUTPUT', @.temp OUTPUT

Jamessql

Execute Stored Procedure from User Defined Function

Is it possible to execute a Stored Procedure from within a user defined
function.
The purpose of the user defined function is to be able to use the results of
the Stored Procedure in a select statement. The user defined function should
return a table.JC,
Why do you need both? Can't you just use a stored procedure?
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
> Is it possible to execute a Stored Procedure from within a user defined
> function.
> The purpose of the user defined function is to be able to use the results
> of
> the Stored Procedure in a select statement. The user defined function
> should
> return a table.|||I need to use the results of the stored procedure in an inner join.
Here is an Example:
Select *
From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
<ResultReturnedBy_StoredProcedure>.ID
You can use UDF functions in this manner but as far as I know I can only get
results from a Stored Procedure using the Execute statement. If there is a
another way to accomplish what I'm trying to do besides using UDF please let
me know.
"Jerry Spivey" wrote:

> JC,
> Why do you need both? Can't you just use a stored procedure?
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
>
>|||JC,
Create a temp table, load the data into the temp table from the proc via
INSERT...EXEC, join with the temp table.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>I need to use the results of the stored procedure in an inner join.
> Here is an Example:
> Select *
> From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
> <ResultReturnedBy_StoredProcedure>.ID
> You can use UDF functions in this manner but as far as I know I can only
> get
> results from a Stored Procedure using the Execute statement. If there is a
> another way to accomplish what I'm trying to do besides using UDF please
> let
> me know.
>
> "Jerry Spivey" wrote:
>|||Jerry,
Thanks for your responses.
I had considered the temp table idea but was hesitant about performance. Is
performance really a concern with temp tables. Also just to know, Execute
statements are not allowed inside a UDF, Y or N?
"Jerry Spivey" wrote:

> JC,
> Create a temp table, load the data into the temp table from the proc via
> INSERT...EXEC, join with the temp table.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>
>|||Depends on the number of records. I don't really use UDFs too much so I
don't know the answer to the second question.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
> Jerry,
> Thanks for your responses.
> I had considered the temp table idea but was hesitant about performance.
> Is
> performance really a concern with temp tables. Also just to know, Execute
> statements are not allowed inside a UDF, Y or N?
> "Jerry Spivey" wrote:
>|||The temp table will consist of one int primary key column and no other
columns. It will usually have between 5K to 20K rows but in some instances i
t
can potentially have more.
"Jerry Spivey" wrote:

> Depends on the number of records. I don't really use UDFs too much so I
> don't know the answer to the second question.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
>
>|||I'm having the same problem. I would like to use a UDF to call my stored
procedure so I could create a view using it. I am developing an application
in C# that uses Crystal Reports. It is so much easier for the Reports to us
e
Tables/Views/Functions (Stored Procs are not even listed) by the wizard. An
y
suggestions?
"JC" wrote:
> The temp table will consist of one int primary key column and no other
> columns. It will usually have between 5K to 20K rows but in some instances
it
> can potentially have more.
> "Jerry Spivey" wrote:
>

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it