Is it possible to retrieve the selected fields from a sp?

arbo

I need to be able to retrieve the name and data type returned by a stored procedure - without calling the stored procedure.

For example:

-- Stored procedure
ALTER PROCEDURE [dbo].[AppMailSMSSubcriptionGet]
(@AAutoID BIGINT,
 @UserID BIGINT,
 @SubscriptionType INT
)
AS

SELECT dbo.AODBAppAlertSubscription.AppID, 
            dbo.AODBUser.AutoID, 
            dbo.AODBAppAlertSubscription.TypeID

FROM   dbo.AODBUser INNER JOIN
           dbo.AODBAppAlertSubscription ON 
           dbo.AODBUser.AutoID = dbo.AODBAppAlertSubscription.UserID

WHERE  (dbo.AODBAppAlertSubscription.TypeID = @SubscriptionType) AND      (dbo.AODBUser.AutoID = @UserID) AND 
             (dbo.AODBAppAlertSubscription.AppID = @AAutoID)[/color]

From this sp I can extract the inputparameters and their datatypes:
[color=green]AAutoID (BIGINT)
userID (BIGINT)
SubscriptionType (INT)[/color]

But is there a way to get the output parameters:
[color=darkred]dbo.AODBAppAlertSubscription.AppID (datatype ?) 
dbo.AODBUser.AutoID  (datatype ?)
dbo.AODBAppAlertSubscription.TypeID  (datatype ?)

Is that possible using the toolkit?

Best wishes,
Arne

Brian Donahue

Hello Arne,

As far as I know the most detail you get from a stored procedure from Toolkit is the SQL DDL script. I really don't have any suggestions for you except to write your own SQL code to parse the DDL for the stored procedure, unless anyone else has any ideas?

fordc03

youd have to get the returned SQL code from redgate, and parse it.

or get the returned object name from redgate and then execute a sp_helptext <objectname> against that stored proc and then parse the text that gets returned.

but otherwise, no, there's not a way to do it natively with the toolkit.