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))
SELECT @InnerInputParam AS [InnerParam], @InnerName AS [InnerName];
-- SELECT @OuterName; -- commented out as this is a parse error
CREATE PROCEDURE dbo.OuterProc(@OuterInputParam INT)
DECLARE @OuterName NVARCHAR(30) = N'Mr. Outer Scope';
@InnerInputParam = @OuterInputParam,
@InnerName = @OuterName;
EXEC dbo.OuterProc 5; or
EXEC dbo.OuterProc @OuterInputParam = 5; (same thing) will return:
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
@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';
@statement = @SQLString,
@params = N'@address NVARCHAR(60)',
@InnerAddress = @address;