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

arboarbo Posts: 5
edited August 1, 2017 12:44PM in SQL Toolkit Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • fordc03fordc03 Posts: 49 Bronze 2
    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.
Sign In or Register to comment.