Sql-server – Do stored procedures have a “signature”, and how is that defined

sql serverstored-procedures

I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is SQL Server 2012.

When the stored procedure is a simple select, the third-party tool receives the result set, but when it accesses a temporary table first, it does not. For example, the following two stored procedures return the same result from SSMS, but only the first can be called successfully from the tool.

What I don't understand is how these two functions are different from an external perspective. They both take the same parameters, and the last query yields the same result.

Where is the stored procedure executed?

--This one works when called both ways
CREATE PROC [dbo].[sp_GetUsers] 
AS
    Select Top 1000 userId 
    from Users

--This only works from SSMS. 
CREATE PROC [dbo].[sp_ComplicatedGetUsers] 
AS

    If OBJECT_ID('tempdb..#TMPUsers') IS NOT NULL
    BEGIN
        DROP TABLE #TMPUsers
    END

    Select Top 1000 userId 
    INTO #TMPUsers
    from Users 

    Select * from #TMPUsers

Best Answer

Your 'tool', whatever it is, is not worth using. It does not follow the TDS protocol. Basically what happens it gets back a result from the server and stops short when parsing it, assuming the first entry is also the last entry. Whatever this tool does, I'm sure there is a decent replacement out there.

As to answer your question: the two procedures have different results, they are not identical from outside. The second incarnation creates additional 'results', in the form of the 'xxx rows affected' messages from the SELECT ... INTO... statement. This breaks your tool. NHibernate is notorious for similar problems, it breaks if the 'xx rows affected' is missing after an update (ie. if you do the update using a stored procedure that has SET NOCOUNT ON).

Pass along to the tool developers that they need to read the ODBC specifications about Processing Results:

Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. This count is made available when application calls SQLRowCount. ODBC 3.x applications must either call SQLRowCount to retrieve the result set or SQLMoreResults to cancel it. When an application executes a batch or stored procedure containing multiple INSERT, UPDATE, or DELETE statements, the result set from each modification statement must be processed using SQLRowCount or cancelled using SQLMoreResults. These counts can be cancelled by including a SET NOCOUNT ON statement in the batch or stored procedure.

Related Question