Sql-server – Specify Values for Template Parameters — command

sql serversql-server-2008ssmsstored-procedures

When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works:

-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.

I can use the keyboard combination ALT+Q, S to get the same results. How can I update the template so that it references the working combination rather than the deprecated one?

I sometimes go for long stretches between working with stored procedures, and I don't always remember the correct combination. It would be nice not to have to search for it each time this happens.

EDIT: I tried Jon's suggestion of opening up the template from the template explorer. I was surprised to find the correct combination listed in the template. Is there a way to force SSMS to use the correct template?

Best Answer

I take it back! Just stop using the Template Explorer. You can edit the files here:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\
  Common7\IDE\SqlWorkbenchProjectItems\Sql\

For example if you edit the file:

Stored Procedure\Create Stored Procedure (New Menu).sql

Then right-click Stored Procedures and select New... and you will see the changes you made. I'm not sure why the Template Explorer exposes that item to you when it's not the one used in the menu. I couldn't figure out why making changes to the template didn't reflect what I was getting when I right-clicked in Object Explorer.

I'll leave the rest of the answer here for posterity. Especially the part about SQL Server 2012, since it handles snippets in a much more intuitive and useful way.

I ~~don't~~didn't believe you ~~can~~could interfere with what Management Studio adds to the template when you open them... as far as I ~~can~~could tell that comment is injected internally.

But I have a better recommendation anyway: use SQL Server 2012 Management Studio and its new Snippets feature. Some benefits over templates:

You can store snippets in a network location, and all of your users can point their custom snippets folder to the same place. This means that if you want to change the template for your stored procedure, let's say, you change it in one place and everyone's next use is up to date - you don't have to distribute an updated template to every single user.
No hokey VB6-looking template parameters dialog. You can just use tab to cycle through and replace tokens in the snippet. You should try it out, I can almost guarantee you'll like it better. (Plus you'll get a bunch of other enhancements, such as IntelliSense that actually starts to live up to its name.)

Back in January, I wrote a detailed tip about setting up snippets:

http://www.mssqltips.com/sqlservertip/2589/use-sql-server-code-snippets-to-encourage-consistent-conventions/

Related Solutions

Sql-server – Specify Connection in Management Studio T-SQL Query

No way to do this as part of a script from SSMS, but you do have two options.

One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file.

Another thing you can do is configure a Central Management Server and then run your script against multiple servers at once.

Mysql – Understanding the use of Sql command parameters

Your stored procedure, itself, in a vacuum, as written now, does not appear vulnerable to SQL injection... but that is not the only consideration, and is not the final answer.

It's always possible that it could be updated later and become vulnerable, but there's yet something else to consider.

For a much bigger potential problem, take one step back and look at your calling convention.

If someone could manage to pass the following string value to your application as txt_f8.Text... it would be obediently concatenated into your MySqlCommand()...

stuff'); GRANT ALL PRIVILEGES ON *.* TO 'hacker'@'%' IDENTIFIED BY 'pwn3d'; SELECT ('1

...then what would be executed by your server?

Possibly, nothing. Possibly, way too much.

This randomly-googled tutorial reinforces the point of not crafting queries with string concatenation, and using SqlParameter instead:

This situation invites a hacker to replace that string with something malicious. In the worst case, you could give full control of your computer away.

Instead of dynamically building a string, as shown in the bad example above, use parameters. Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.

Certainly not a good trade-off for having to do less typing.

I'm a MySQL DBA and not a .NET person, but from what I can tell from a quick search it appears that declaring CommandType.StoredProcedure allows you to take better advantage of the capabilities of stored procedures, by making some of the parameters INOUT or OUT, and not just IN. This might be useful, for example, if you wanted one of the parameters to be the ID of the newly-inserted user, which the SP could return to your code.

If you don't need return values, you don't have to do it that way... but feedback/confirmation isn't usually a bad thing.

Best Answer

Related Solutions

Sql-server – Specify Connection in Management Studio T-SQL Query

Mysql – Understanding the use of Sql command parameters

Related Question