stored procedure within stored procedure

Question

Post reply

stored procedure within stored procedure

puresmile

SSC-Addicted

Points: 407
More actions
July 4, 2006 at 2:49 pm

#168824

can u use another stored procedure in one procedure i.e. stored procedure within stored procedure?
if yes then plz give example

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 1

surely

CREATE PROCEDURE dbo.USP_BATCH

AS

EXECUTE dbo.USP_BATCH_START

EXECUTE dbo.USP_BATCH_MIDDLE

EXECUTE dbo.USP_BATCH_CLEANUP

peter-430650 SSC Rookie Points: 36 More actions · Answer 2

are stored procedures selectable?

ie...

create proc dbo.sp

as

select * from tableA where

col in( ( select col from (select exec spB) ) )

Andrew in WV SSCommitted Points: 1655 More actions · Answer 3

Not directly. You can load the results returned by a stored procedure into a table, including temporary tables or table variables if necessary, which can then be used in a subquery.

There is an example in BOL (2000) under the topic INSERT (described) titled "Load data using the SELECT and EXECUTE options" that shows how you can do this.

--Andrew

peter-430650 SSC Rookie Points: 36 More actions · Answer 4

solution:

CREATE TABLE #t(v varchar(100))--columns&column metadata must match resultset of query

INSERT

#t EXEC dbo.sp

select

* from tableA where col in (select col from #t)

DROP

TABLE #t

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

>You can load the results returned by a stored procedure into a table, including . . . table variables if necessary

Actually this is incorrect. You cannot do:

INSERT @TabVar EXEC dbo.mysproc

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lowell SSC Guru Points: 323518 More actions · Answer 6

what Guru is saying is you will receive an error if you try and use EXEC to populate a @table variable;you must use a #temp or ##temp table

here's a proc calling a proc example:

create proc pr_InnerTest As

BEGIN

SELECT TOP 5 NAME,ID FROM SYSOBJECTS WHERE XTYPE='U'

END

GO

CREATEPROC PR_OUTERTEST AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #TABVAR (OBJNAME VARCHAR(30),OBJID INT,EXTRACOLUMN INT)

INSERT INTO #TABVAR(OBJNAME,OBJID)

EXEC DBO.pr_InnerTest

SELECT NAME AS COLNAME,#TABVAR.* FROM SYSCOLUMNS

INNER JOIN #TABVAR ON SYSCOLUMNS.ID=#TABVAR.OBJID

END

GO

--this returns the error

--EXECUTE cannot be used as a source when inserting into a table variable.

CREATE PROC PR_OUTERTEST2 AS

BEGIN

SET NOCOUNT ON

DECLARE @TABVAR TABLE(OBJNAME VARCHAR(30),OBJID INT,EXTRACOLUMN INT)

INSERT INTO @TABVAR(OBJNAME,OBJID)

EXEC DBO.pr_InnerTest

SELECT NAMEAS COLNAME,@TABVAR.* FROM SYSCOLUMNS

INNER JOIN @TABVAR ON SYSCOLUMNS.ID=@TABVAR.OBJID

END

GO

EXEC PR_OUTERTEST

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

peter-430650 SSC Rookie Points: 36 More actions · Answer 7

Hmm I think this didn't compile because you replaced # with @

try this:

you can't define tables using '@', you must use '#'?

create

proc dbo.p1

as

select

id from syscolumns

go

create

proc p

as

CREATE

TABLE #t(id varchar(100))--columns&column metadata must match resultset of query

INSERT

#t EXEC dbo.p1

select

* from sysobjects where id in (select id from #t)

DROP

TABLE #t

Andrew in WV SSCommitted Points: 1655 More actions · Answer 8

You're right. Temporary tables work; table variables do not. That's what I get for inferring without testing first.

--Andrew

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 9

Table variables are stronger in 2005. If you're using it, you're in luck!

This works in SQL Server 2005:

create

proc spTestInsert @tlike sysname = '%'

as

begin

select [name] tablename from sys.tables where [name] like @tlike

end

declare

@t table (tblname sysname)

insert

into @t (tblname)

exec spTestInsert 'prod%'

select

* from @t

But in 2000, you get this error:

Msg 197, Level 15, State 1, Procedure spTestInsert, Line 14

EXECUTE cannot be used as a source when inserting into a table variable.

Hope this helps...

Carter Burleigh

But boss, why must the urgent always take precedence over the important?

zubamark Right there with Babe Points: 735 More actions · Answer 10

I use it a lot to execute multiple sps from 1 click

CREATE PROCEDURE D

@DTE datetime,

@ID varchar(6)

AS

execute A @DTE,@ID

execute B @DTE,@ID

execute C @DTE,@ID

peter-430650 SSC Rookie Points: 36 More actions · Answer 11

is there any way to make a stored procedure within the scope of a stored procedure? ie ,

create/alter proc _

as

create/alter proc #_

as

exec #_

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

That's cool... do you know if table variables still have the same fault of not being able to use statisics and not be alterable once formed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)