What are the challenges you face when working across database platforms? Take the survey
Options

Encapsulate - how to get locals to be params

monkeygrindmonkeygrind Posts: 40
edited December 7, 2006 6:16AM in SQL Refactor Previous Versions
Like most folks I tend to write some sprocs first as a script and then make them sprocs later - so the sproc encapsulation feature is a great selling point.

Normally when you start to write something out you make variables that will eventually be params declared as locals vars, like this:
declare @promoID int,
		@locationID int,
		@oldPromoID int

insert into Promotion
select	@promoID,
		@locationID,
-- rest of select list removed for brevity
		getdate()
from	dbo.Promotion
where	promoID = @oldPromoID

insert into dbo.join_PromotionDistributor
select	@promoID,
		distributorID
from	dbo.Distributor
where	locationID = @locationID
and		distributorName <> 'None'

When I try to encapsulate this script though the three vars declared are kept as local vars. The help doc says "SQL Refactor automatically determines whether each variable will be an input parameter or an output parameter.". How does it determine this?

Comments

  • Options
    Like most folks I tend to write some sprocs first as a script and then make them sprocs later - so the sproc encapsulation feature is a great selling point.

    Normally when you start to write something out you make variables that will eventually be params declared as locals vars, like this:
    declare @promoID int,
    		@locationID int,
    		@oldPromoID int
    
    insert into Promotion
    select	@promoID,
    		@locationID,
    -- rest of select list removed for brevity
    		getdate()
    from	dbo.Promotion
    where	promoID = @oldPromoID
    
    insert into dbo.join_PromotionDistributor
    select	@promoID,
    		distributorID
    from	dbo.Distributor
    where	locationID = @locationID
    and		distributorName <> 'None'
    

    When I try to encapsulate this script though the three vars declared are kept as local vars. The help doc says "SQL Refactor automatically determines whether each variable will be an input parameter or an output parameter.". How does it determine this?


    Hi,

    the reason these variables are not included in the encapsulate as new sp, is that they are not used at all.
    Encapsulate as new stored procedure tries to identify the variables you use inside the selection. Other variables are clearly irrelevant in case of a working script, and should not be passed on as parameters. Once the used variables are identified, we analyze the script before and after the selection. If a variable is assigned to before the selection, then the variable should be passed on as an input variable, since the selected block is using its value. If the variable is used after the selection (its value is queried or used on a right hand side), and it is assigned to inside the selection, then it will be automatically picked up as an output parameter.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    Do you mean like:
    declare @promoID int,
          @locationID int,
          @oldPromoID int
    
    set @promoID = 1
    set @locationID = 2
    
    insert into Promotion
    select   @promoID,
          @locationID,
    -- rest of select list removed for brevity
          getdate()
    from   dbo.Promotion
    where   promoID = @oldPromoID
    

    ?? Tried that and it still doesn't pick them up. Possible to post a quick code sample of what you mean?
  • Options
    Do you mean like:
    declare @promoID int,
          @locationID int,
          @oldPromoID int
    
    set @promoID = 1
    set @locationID = 2
    
    insert into Promotion
    select   @promoID,
          @locationID,
    -- rest of select list removed for brevity
          getdate()
    from   dbo.Promotion
    where   promoID = @oldPromoID
    

    ?? Tried that and it still doesn't pick them up. Possible to post a quick code sample of what you mean?


    This example works for me. If you select the text:
    insert into Promotion
    select   @promoID,
          @locationID,
    -- rest of select list removed for brevity
          getdate()
    from   dbo.Promotion
    where   promoID = @oldPromoID
    

    it picks up both of the variables as parameters.
    What text do you have selected?

    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    Both all on my example, and just using your code selection, I get the same result. I can email a screenshot if you need it.
  • Options
    Both all on my example, and just using your code selection, I get the same result. I can email a screenshot if you need it.

    Could you do this please. email is
    Andras.Belokosztolszki (at) red-gate.com

    please highlight your selection, and the first page of the wizard.
    Could you also send me the collation name of your database please.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • Options
    OK, so this seemed to automagically sort itself out, but here's another question:

    Are you open to allowing the parameter list to be modified in step #1 in the future? Right now it's correctly marked two parms as:
    @testCenterID	uniqueidentifier	input	
    @userID	uniqueidentifier	input
    

    But what I'd really like is to modify them to set defaults like:
    @testCenterID	uniqueidentifier = null	
    @userID	uniqueidentifier = null
    

    Possible?
  • Options
    OK, so this seemed to automagically sort itself out, but here's another question:

    Are you open to allowing the parameter list to be modified in step #1 in the future? Right now it's correctly marked two parms as:
    @testCenterID	uniqueidentifier	input	
    @userID	uniqueidentifier	input
    

    But what I'd really like is to modify them to set defaults like:
    @testCenterID	uniqueidentifier = null	
    @userID	uniqueidentifier = null
    

    Possible?

    This is on our list of possible features for the future versions. At the moment you will need to edit the generated stored procedure manually. (this we open in a new window, and you could edit it before executing the create statement). But this is something we will look into.

    Many thanks for reporting this issue,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
Sign In or Register to comment.