Convert code to dynamic Code
Convert selected CODE or a complete stored procedure to Dynamic SQL Code.
Such a feature has been on my mind for a long time. It would make a lot of development effort so much more bearable with respect to dynamic SQL. Turning code into DSQL manually is very often not fun at all (not to mention tedious and error-prone), especially dealing with quotation marks is a pain. I try to use the CHAR function as much as possible if I have to embed single quotation marks into the string and would expect to see any good solution around DSQL to do the same. Looking at monstrosities like ''''''a'''''' is not fun. Could Red Gate, please, have a look at this?
Mathieu Proulx commented
Yes! It would be really great if we could convert a normal SQL into a dynamic SQL to pass different variables in parameters.
SELECT p.Id FROM MyStore..Product AS p
LEFT JOIN OtherDatabase..Specs AS s on s.Id = p.Id
p.Deleted = 0 AND
s.Color = 'Blue'
Once selected and converted using the new SQL Prompt shortcut, would become :
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = N'SELECT p.Id FROM MyStore..Product AS p' +
N'LEFT JOIN OtherDatabase..Specs AS s on s.Id = p.Id' +
N'WHERE ' +
N'p.Deleted = 0 AND' +
N's.Color = ''Blue'' '
while converting single quote in double quotes '' and while keeping the code alignment provided by the "Format SQL" command.
I know it would be a HUGE asset and would save a lot of people lots of time formatting it by hand, since this is the only way of passing a Database name by parameter to a Stored Procedure in T-SQL.
Really I'd just like to see a lot more support for Dynamic SQL. Formatting, Intellisense, Dependencies. But failing that the ability to write something as a stored procedure and then convert to DSQL would be a easy way to start down such a road.