Encapsulate - how to get locals to be params
monkeygrind
Posts: 40
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:
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?
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
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
Red Gate Software Ltd.
?? 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:
it picks up both of the variables as parameters.
What text do you have selected?
Andras
Red Gate Software Ltd.
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
Red Gate Software Ltd.
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:
But what I'd really like is to modify them to set defaults like:
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
Red Gate Software Ltd.