sql serversql-server-2008stored-procedures
As a follow-up to my previous question:
is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ?
It seems my customer there has several stored procedures and a few triggers that have been "tweaked" – but unfortunately, using a "wrong" set of those database settings. Can I find out which procs and triggers are affected, or do I need to re-create all of them with the proper settings?
Each SELECT statement that does not insert into a table or a variable will produce a result set.
If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.
UPDATE
Here are examples of stored procedures.
This stored procedure would return one result set:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
END;;
DELIMITER ;
This stored procedure would return two result sets:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
SELECT * FROM table_2;
END;;
DELIMITER ;
This is fairly easy to test yourself. Let's create two very simple procedures:
CREATE PROCEDURE dbo.sp_mystuff
AS
SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
SELECT 'x';
GO
Now let's build a wrapper that executes them a number of times, with and without the schema prefix:
CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.mystuff;
SET @i += 1;
END
END
GO
Results:
Conclusions:
The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.
Also I performed some pretty extensive testing of this pattern in the following blog post:
http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Best Answer
This can be determined from
sys.sql_modules:Results:
Some other useful columns:
Note that some settings (e.g.
arithabort,ansi_warnings,ansi_padding) are not stored with the object. Also you will need to recompile the procedures with the correct settings for any that have been identified as "wrong."