Press "Enter" to skip to content

Category: Stored Procedures

Permissions To Create Stored Procedures

Published 2016-03-03 by Kevin Feasel

Kenneth Fisher shows the permissions necessary to create a stored procedure:

The user still won’t be able to create procedures or views. And if you think about it in the right way it makes sense. While the CREATE permissions give us the ability to create objects what they don’t give us is a place to put them. Schemas are containers for most of the objects in databases. So in order change the schema (by putting an object in it) we need to grant ALTER on it. So for the CREATE to work we need to:

Getting the right granularity for permissions is a vital part of securing a SQL Server instance.

Comments closed

External Temp Tables And Plan Cache Bloat

Published 2016-01-22 by Kevin Feasel

Sebastian Meine warns us about using external temporary tables in stored procedures:

When a stored procedure is compiled that is accessing an external temp table, SQL Server has no guarantee that the next time this stored procedure is called it is called from the same connection. However, if it is called from a different connection, the accessed temp table might contain significantly more (or less) data making a different execution plan preferable.

A simple way to deal with this situation is to force a recompilation every time a procedure that works with external temporary tables is executed. SQL Server is not going that route. Instead, SQL Server caches the procedure once for each connection. That can safe a significant amount of CPU resources when the procedure in question is called within a loop.

Try to avoid using external temp tables.  There are some cases in which it’s a very useful construct, but

Comments closed

Parameterizing Procedures

Published 2015-12-25 by Kevin Feasel

Monica Rathbun shows how to combine several report queries:

I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it be a sort, a where clause, or even just an extra field or two that makes it different, some developers think you need a different procedure for each one . Why not consolidate and parameterize?

The next step might be using dynamic SQL to build a query if there’s as much overlap as we see in Monica’s example.

Comments closed