Comments
4 comments
-
tgoldstein@co.arapahoe wrote:Any pointers on using the Encapsulate function with parameters. I have been playing with it some to try and get a feel if it is useful to me. I am unable to determine how to write my queries that it consistently recognizes parameters. Yesterday I got it to recognize parametersm on a query. Today I can't seem to find the magic key. Here is what I am trying that it does not show any parameters for:
SELECT *
FROM dbo.authors
WHERE au_lname = @LName
I would expect it to pickup @LName as a parameter. Any hints?
Also, I tried making this into a proc with encapsulate:
/*
CREATE DATE:
CREATED BY: Tim Goldstein
**********************************************************************************************
FUNCTION:
**********************************************************************************************
MODIFICATIONS:
Date Name Description
**********************************************************************************************
PURPOSE:
*/
SELECT *
FROM dbo.authors
WHERE au_lname = @LName
The comment block is dropped. Anyway to get the encapsulate function to insert a standard comment block or at least use one I put in place?
Tim
Denver, CO
Hi Tim,
you must declare the variable you want to turn into parameter. You should also use the variable before the selected text that is to be encapsulated as a new stored procedure. Clearly if you do not declare the variable or do not assign a value to it it is not a useful input for a stored procedure, so we do not consider the variable. Unless the variable is used on the right hand side (you query it) after the selection block, in which case the variable is automatically determined as an output parameter.
Hope this helps,
Andras -
Andras,
Thank you for the explaination. I now understand how to make it work.
Another question. Why don't you layout the SQL for the proc according to the selected rules for Lay out SQL? Seems sort of crazy that the auto generated SQL is in a different layout that want I have specified. If I want to make my proc look as I wish I now have to take the second step of formatting. Sort of diminishes the value of a tool that is suppose to make me more efficient especially when the tool already has the second function.
Any input on the loss of the comment block? I have asked on the SQL prompt section about support for a comment block as an insert. Currently it crashes Prompt. Seems to be no one from Red Gate replying on that group.
Tim
Denver, CO -
tgoldstein@co.arapahoe wrote:...
Another question. Why don't you layout the SQL for the proc according to the selected rules for Lay out SQL? Seems sort of crazy that the auto generated SQL is in a different layout that want I have specified. If I want to make my proc look as I wish I now have to take the second step of formatting. Sort of diminishes the value of a tool that is suppose to make me more efficient especially when the tool already has the second function.
...
Hi Tim,
this is done on purpose. If someone does not want to use the lay out feature, then we should not force it on the already existing stored procedures. If someone does want to use it, then it is still there, and requires a single shortcut key (well, a double shortcut).
I like to use the building block/brick analogy for refactorings. They are small individual units of code modification, and can be used in a sequence to improve code. Joining these would make it more difficult to apply them separately. Somewhere I hope that in the future Microsoft will allow macros in Management Studio, and one can combine the refactorings. Well, maybe we will have to do this in some future version
.
Andras -
tgoldstein@co.arapahoe wrote:...
Any input on the loss of the comment block? I have asked on the SQL prompt section about support for a comment block as an insert. Currently it crashes Prompt. Seems to be no one from Red Gate replying on that group.
Tim
Denver, CO
Hi Tim,
The Prompt people are also flooded with work, sorry if no one responded. I'll ask Bart, the pm for Prompt to look at this problem.
Regards,
Andras
Add comment
Please sign in to leave a comment.
SELECT *
FROM dbo.authors
WHERE au_lname = @LName
I would expect it to pickup @LName as a parameter. Any hints?
Also, I tried making this into a proc with encapsulate:
/*
CREATE DATE:
CREATED BY: Tim Goldstein
**********************************************************************************************
FUNCTION:
**********************************************************************************************
MODIFICATIONS:
Date Name Description
**********************************************************************************************
PURPOSE:
*/
SELECT *
FROM dbo.authors
WHERE au_lname = @LName
The comment block is dropped. Anyway to get the encapsulate function to insert a standard comment block or at least use one I put in place?
Tim
Denver, CO