SQL Server 2012 – Contradiction in Book Related to Variable Scope in Stored Procedures

sql-server-2012stored-procedurest-sql

I was reading the 70-461 TSQL training book and the part about stored procedures said this:

Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1

But in the same book was this example code:

CREATE PROCEDURE Sales.ListCustomerByAddress
(@address AS NVARCHAR(60))
AS
DECLARE @SQLString AS NVARCHAR(4000);
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @address';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @address = @address;
RETURN;  
GO

My understanding of the quoted statement is that the @SQLString variable and @address parameter cannot be visible to and referenced by the sp_executesql procedure? A variable is an object that holds a value so If it is not there or cannot be seen (for instance in @statement = ) how can the value be used or passed

Can someone help reconcile this contradiction?

Best Answer

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating how variable scope often works):

int OuterVariable = 5;

function int Sub1 (int Param1)
{
  int InnerVariable = 10;

  return InnerVariable + OuterVariable + Param1;
}

int Result = Sub1(3);

Since variables declared in an outer scope are often visible to inner scopes (i.e. global), the code above wouldn't error when calling Sub1 stating that OuterVariable hasn't been declared. Instead, the subroutine would have access to the value of OuterVariable (and in some languages, it might even be able to overwrite that value). Hence, the value of Result at the end would be 18.

So, when the book states:

"Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by Proc1"

they are saying that T-SQL does not work like that. They are not saying that variables in Proc1 cannot be passed into a sub-Proc call; they are saying that if you want those values in the sub-Proc call, then you must pass them in explicitly via input parameters. Essentially, "visible" means "available without explicitly passing in via input parameters".

Maybe this will help illustrate:

CREATE PROCEDURE dbo.InnerProc(@InnerInputParam INT, @InnerName NVARCHAR(30))
AS
SELECT @InnerInputParam AS [InnerParam], @InnerName AS [InnerName];

-- SELECT @OuterName; -- commented out as this is a parse error
GO

CREATE PROCEDURE dbo.OuterProc(@OuterInputParam INT)
AS
DECLARE @OuterName NVARCHAR(30) = N'Mr. Outer Scope';

EXEC dbo.InnerProc
        @InnerInputParam = @OuterInputParam,
        @InnerName = @OuterName;
GO

Executing EXEC dbo.OuterProc 5; or EXEC dbo.OuterProc @OuterInputParam = 5; (same thing) will return:

InnerParam     InnerName
5              Mr. Outer Scope

Those values are "known" by dbo.InnerProc because they were passed in. And they were passed in because they were not "visible" to dbo.InnerProc, even though they existed in the outer / parent scope of dbo.OuterProc during execution. But neither @OuterInputParam nor @OuterName can be referenced in dbo.InnerProc because inner / child scopes have no knowledge of outer / parent scopes, at least not for variables (including Table Variables) and parameters.

Finally, to relate all of this back to the initial example in the book, this is why I do my best to change variable names when using sp_executesql: to make the code more readable. Changing one variable name might make it quite a bit clearer:

SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @InnerAddress';

EXECUTE sp_executesql
         @statement = @SQLString,
         @params = N'@address NVARCHAR(60)',
         @InnerAddress = @address;
Related Question