November 18, 2011 at 3:28 am
Hi guys,
I have a stored procedure that as another stored procedure inside.
I don't want that the the stored procedure inside produces output.
E.g:
Create proc test
as
begin
exec proc 1
insert ...................
delete...............
END
How can i achieve this goal (proc 1 don't produce output to proc test?
Thanks
November 18, 2011 at 3:28 am
P.S - Proc 1 as XP_cmdshell commands inside...
November 18, 2011 at 6:05 am
If the internal proc returns a data set... there's not much you can do. What is it that you're trying to do with the procs?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2011 at 6:13 am
Grant Fritchey (11/18/2011)
If the internal proc returns a data set... there's not much you can do. What is it that you're trying to do with the procs?
Well, you could always pipe the results to a temp table and then simply ignore those results:
Create proc test
as
begin
CREATE TABLE #someTempTable (
column list....
)
INSERT #someTempTable
exec proc 1
insert ...................
delete...............
END
-- Gianluca Sartori
November 18, 2011 at 6:16 am
Another way could be using a loopback linked server and execute the procedure as a pass-through query: http://spaghettidba.com/2011/11/16/discovering-the-output-of-dbcc-commands/
The technique described there uses DBCC commands, but it works with any pass-through query.
-- Gianluca Sartori
November 18, 2011 at 6:27 am
Gianluca Sartori (11/18/2011)
Another way could be using a loopback linked server and execute the procedure as a pass-through query: http://spaghettidba.com/2011/11/16/discovering-the-output-of-dbcc-commands/The technique described there uses DBCC commands, but it works with any pass-through query.
I like the temp table approach, but this seems kind of nuts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2011 at 6:41 am
Grant Fritchey (11/18/2011)
Gianluca Sartori (11/18/2011)
Another way could be using a loopback linked server and execute the procedure as a pass-through query: http://spaghettidba.com/2011/11/16/discovering-the-output-of-dbcc-commands/The technique described there uses DBCC commands, but it works with any pass-through query.
I like the temp table approach, but this seems kind of nuts.
When you have no other option, you start to explore foolish solutions. 🙂
This way you would also get rid of the messages generated by the stored procedure.
Much more overhead than a direct call, however.
-- Gianluca Sartori
November 18, 2011 at 7:19 am
Gianluca Sartori (11/18/2011)
Grant Fritchey (11/18/2011)
Gianluca Sartori (11/18/2011)
Another way could be using a loopback linked server and execute the procedure as a pass-through query: http://spaghettidba.com/2011/11/16/discovering-the-output-of-dbcc-commands/The technique described there uses DBCC commands, but it works with any pass-through query.
I like the temp table approach, but this seems kind of nuts.
When you have no other option, you start to explore foolish solutions. 🙂
This way you would also get rid of the messages generated by the stored procedure.
Much more overhead than a direct call, however.
I should add, smart, but still nuts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply