i want to execute a stored procedure

Question

i want to execute a stored procedure

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
June 28, 2004 at 1:33 am

#108162

can i execute stored procedure from
a stored procedure and get the result set and work accordingly. ??
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 6 posts - 1 through 5 (of 5 total)

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

Yes,

I have a stored procedure that has a @choice argument. Depending on the value of that argument I execute various stored procedures.

The other thing that is possible is to create a temporary table with the same structure as your results set.

For example, if you inner stored procedure returns and id and description column then you can do the following

CREATE TABLE #tmp (ID INT , Description VARCHAR(50))

INSERT #tmp

EXEC usp_MyProc

Having done this your outer procedure can use #tmp for its own purposes.

LinkedIn Profile

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 2

u got a nasty signature dude,

but what does it mean anyway ?

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

David.Poole SSC Guru Points: 76000 More actions · Answer 3

The signature is allegedly the marriage proposal that the actor, Robert Mitchum, made to an actress whose name escapes me. I think she accepted.

LinkedIn Profile

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 4

You could replace the called sproc with a user defined function (UDF) which can return a temporary table. You then use the temporary table as your result set.

When in doubt - test, test, test!

Wayne

sholliday SSCrazy Points: 2100 More actions · Answer 5

Here is a UDF which return a table:

--START tsql

if exists (select * from sysobjects where id = object_id('dbo.fnc_10_parse_string') and xtype = 'TF')

drop function dbo.fnc_10_parse_string

GO

CREATE FUNCTION dbo.fnc_10_parse_string( @list varchar(8000))

RETURNS @tablevalues TABLE

( itemid int IDENTITY(1,1) , item varchar(8000) )

AS

BEGIN

declare @pos1 int

select @pos1 = 0

declare @startStringLen int

select @startStringLen = LEN(@list + '*') - 1

--This adding the '*' and then substracting 1 char is to get around the LEN

--issue of

--LEN

--Returns the number of characters, rather than the number of bytes, of the given string expression,

-- --->>>> excluding trailing blanks <<<< -----.

DECLARE @P_item varchar(255)

WHILE (@pos1 < @startStringLen)

BEGIN

select @pos1 = @pos1 + 1

SELECT @p_Item = SUBSTRING(@List,@pos1,1)

INSERT INTO @tablevalues

SELECT Item = @p_Item

END

RETURN

END

GO

--end tsql

and how to call it

--

SELECT * FROM

dbo.fnc_10_parse_string('abcdefghijklmnopqrstuvwzyz')

--