stored procedures

What is Parameter Sniffing in SQL Server?

by

Parameter sniffing is a feature in SQL Server where the query optimizer examines (or “sniffs”) the parameter values the first time a stored procedure or parameterized query executes. It uses these specific values to create an execution plan optimized for those particular parameters. The plan is then cached and reused for subsequent executions, even when different parameter values are passed in.

This behavior can be efficient if the initial parameters are representative of typical queries, but it can also cause performance problems if later calls use very different parameter values that make the cached plan inefficient. 

Read more

How to Identify Dependencies Before Dropping a Column in SQL Server

by

Dropping or modifying a column in SQL Server can look straightforward, but it often isn’t. That column might be referenced by other objects in the database, and removing it without checking can break things silently. Unlike dropping a whole table, where SQL Server is very strict about dependencies, column-level references are not always enforced or even tracked. That’s why it’s important to do some homework before making the change.

Read more

Get All Parameters from a SQL Server Database (T-SQL)

by

In SQL Server we can query the sys.parameters system catalog view to return all parameters that belong to user-defined objects.

For system objects, we can query the sys.system_parameters view. We can alternatively query the sys.all_parameters system catalog view to return all parameters that belong to either user-defined or system objects.

Read more

How to Redefine the Columns Returned by a Stored Procedure in SQL Server

by

When you execute a stored procedure that returns a result set in SQL Server, the columns returned are defined in the stored procedure.

But did you know that you can redefine those columns?

What I mean is, you can change the names and/or the data type of the columns returned in the result set.

This could save you from having to fiddle with the column headers and data formats in the event you needed to use that result set in another setting.

For example, if a stored procedure returns a datetime2 column, but you only need the date part, you could specify date for that column, and your result set will only include the date part.

And the best part is that you can do it as part of the EXECUTE statement. No need to massage the data after executing the procedure. way to do this is by using the WITH RESULT SETS clause of the EXECUTE statement.

Read more

How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s)…” in SQL Server

by

If you encounter error Msg 11535, Level 16 while trying to execute a stored procedure, it’s because you didn’t define enough result sets in the WITH RESULT SETS clause.

Some stored procedures return multiple result sets. When using the WITH RESULT SETS clause, you need to define each expected result set. You need to do this even if you only want to change the definition of one or some of the result sets.

To fix this error, simply add the additional result sets to the WITH RESULT SETS clause, each separated by a comma.

You could also fix it by removing the WITH RESULT SETS clause, but I’ll assume you’re using it for a reason (i.e. you need to redefine the result set returned by the procedure).

Read more