1

I have read plenty of articles (particularly on here) about Parameter Sniffing in Stored Procedures and ways to get around it. For example, here: http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

Does the same apply to a stored procedure that is called by another stored procedure? i.e. does the solution described in the linked article also apply to nested stored procedure.

Also if you declare a variable called @PersonID in the calling stored procedure, can you decalre a variable called @PersonID in the called stored procedure i.e. DECLARE PersonID int. This would be variable shadowing.

w0051977
  • 15,099
  • 32
  • 152
  • 329

1 Answers1

2

Yes to the first one. Each stored procedure is separate and you need to apply anti-sniffing techniques (parameter masking, or the newer OPTIMISE FOR UNKNOWN) in each stored procedure

Yes to the second, but not why you think. A variable has scope only in that stored procedure. So any @PersonID in the callee is unrelated to the @PersonID in the caller. If you don't have @PersonID in the callee, then you the parent one isn't in scope there

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks. Therefore if I declared PersonID=1 in stored procedure 1 and PersonID = 2 in stored procedure 2, can you confirm that PersonID will equal 1 after control is passed back from stored procedure 2 to stored procedure 1. Are there any risks using the same variable name? – w0051977 Jan 31 '12 at 13:35
  • yes, PersonID=1 will stay in proc 1. The internals of proc 2 have no effect. Think of your .net or java methods and "encapsualtion". No risks if it makes sense. Your nested proc is a method in it's own right and may be called separately – gbn Jan 31 '12 at 13:39
  • Thanks. I am almost ready to mark this question answered. I am using 'XACT_ABORT ON' with explicit transactions to ensure that transactions are rolled back when there is an error in the stored procedure. Therefore if there is a timeout on the VB6 command object (this calls the stored procedure) or the stored procedure throws an exception; the program will deal with it. I have only recently discovered: 'XACT_ABORT ON'. Is there anything else I should think about when calling stored procedures from VB6 programs? – w0051977 Feb 01 '12 at 13:43
  • The fact of VB6 doesn't matter. The stored proc is opaque to the calling code, no matter what language. The ideal stored proc template is here (my answer, I'm biased): http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – gbn Feb 01 '12 at 13:47
  • The article you provided is good. I had not thought about setting NOCOUNT and I believe I will be in for a performance gain. You say: "the fact of VB6 doesn't matter". However, before I used XACT_ABORT ON, I was having problems were the stored procedure was returning an output parameter indicating success even though it was not successful. – w0051977 Feb 01 '12 at 13:54
  • @w0051977: you can use the CATCH block to set output params NULL or such too (It's my article I linked too) – gbn Feb 01 '12 at 13:56
  • this is exactly what I do. However, can I assume that the stored procedure transaction will always either committ or roll back e.g. what happens if the client connection times out or there is a power cut on the client side. I am still relatively new to stored procedures. – w0051977 Feb 01 '12 at 13:58
  • client timeout: you need SET XACT_ABORT ON. Client dies, the SQL connection is physically broken so it all rolls back. See this on dba.se (me again) http://dba.stackexchange.com/a/10929/630 – gbn Feb 01 '12 at 14:03
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7236/discussion-between-w0051977-and-gbn) – w0051977 Feb 01 '12 at 14:06