If you encounter error Msg 11537, Level 16 in SQL Server, chances are that you’re trying to execute a stored procedure by using the WITH RESULT SETS clause, but you haven’t included all the columns in your definition.
When you use the WITH RESULT SETS clause in the EXECUTE/EXEC statement, you must provide a definition for all columns returned by the stored procedure. If you don’t, you’ll get this error.
Have you ever run a stored procedure, only to be overwhelmed at the number of columns returned? Maybe you only needed one or two columns, but it presented you with way too many columns for your needs on this particular occasion.
Fortunately, there’s a little trick you can use to retrieve selected columns from a stored procedure. This enables you to get just the columns you need.
And the best part is, it doesn’t involve having to create temporary tables and shuffling the data around.
All you need to do is pass your stored procedure to the OPENROWSET() function.
The same concept can be applied to the OPENQUERY() function.
This article presents two ways to return a list of stored procedures in a SQL Server database.
In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption.
To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE.
If you’re getting the following error (or similar error) while trying to execute a natively compiled stored procedure in SQL Server 2019, read on.
Query failed: Internal connection fatal error.
If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.
Stored procedures are a bit different.
Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.
But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.
In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a stored procedure.
This function accepts two parameters: the object ID, and the property for which you’re checking it for.
Therefore, you can pass the object ID as the first argument, and IsProcedure as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a stored procedure.
A return value of 1 means that it is a stored procedure, and a value of 0 means that it’s not.
This article presents 4 ways of using T-SQL to get the definition of a stored procedure in SQL Server.
The definition is the actual T-SQL statement used to create the stored procedure.
Three of the methods here are exactly the same as the ones used for returning the definition of a view (except here, they’re being used on stored procedures instead of views).
In SQL Server, you can use the Transact-SQL ROUTINES system information schema view to return a list of stored procedures and functions in the current database.
More specifically, it returns a list of all stored procedures and functions that can be accessed by the current user in the current database.
You can also use ROUTINES simply to return information about a specific procedure or function if required.
To use this view, use the fully qualified name of INFORMATION_SCHEMA.ROUTINES.
Here are a couple of ways to return information on all languages in SQL Server. One method uses a system compatibility view, the other method executes a system stored procedure. Both methods return the same result.