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))
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @address';

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

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?

  • Why is this contradicting? – ypercubeᵀᴹ Jun 26 '16 at 22:58
  • The sp_executesql proc being called in the sales.ListCustomerByAddress proc can see the @sqlstring that was declared in the latter – ola Jun 26 '16 at 23:02
  • No it doesn't. The value of @SQLString is passed to the parameter @statement of the sp_executesql. – ypercubeᵀᴹ Jun 26 '16 at 23:03
  • But in the statement i quoted above, it says the @SQLString and @address shouldn't be visible to the sp_executesql proc @ypercubeᵀᴹ – ola Jun 26 '16 at 23:13
  • 1
    @SQLString and @address are not visible and cannot be modifed by sp_executesql. Their values though are passed to the procedure. All procedures have parameters. If I call procedure alpha() with parameters b and c: execute alpha(b=13, c='Peter'), I expect that the values 13 and 'Peter' to be indeed available inside the procedure. If I do bbb=13; ccc='Peter'; execute alpha(b=bbb, c=ccc), I expect the same. The values 13 and Peter are still available inside the procedure but variables bbb and ccc are not visible. – ypercubeᵀᴹ Jun 26 '16 at 23:18

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';

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

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;
  • Beautifully put. In other words, the nested scope of the sub proc cannot gain access to the @SQLString and @address stated in the higher scope of the nesting proc without stating said variables/parameters clearly again as inputs. Thank you. – ola Jun 27 '16 at 2:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.