Encapsulate - how to get locals to be params

monkeygrind

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?

Andras B

monkeygrind wrote:

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

monkeygrind

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?

Andras B

monkeygrind wrote:

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

monkeygrind

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.

Andras B

monkeygrind wrote:

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

monkeygrind

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?

Andras B

monkeygrind wrote:

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