Searching for stored procedure groups
BlueSkyDev
Posts: 3
Hope someone can help.
I've come across a database that uses stored procedure groups (e.g. p_StoredProcName;2) that simulate .Net override functions. So there are n versions of the same s-p name each of which usually differ by virtue of having different arguments. I gather this is a deprecated function and not recommended practice and it's not how I would choose to do this. However this is what I've found and it is heavily used here. Are these groups included in the Index process so that the content of each separate proc is found when searching. I can't find a way of doing this. Given that I'm new to SQL Search and this technique it's possible that there is a way that I don't know about.
Thanks in advance.
I've come across a database that uses stored procedure groups (e.g. p_StoredProcName;2) that simulate .Net override functions. So there are n versions of the same s-p name each of which usually differ by virtue of having different arguments. I gather this is a deprecated function and not recommended practice and it's not how I would choose to do this. However this is what I've found and it is heavily used here. Are these groups included in the Index process so that the content of each separate proc is found when searching. I can't find a way of doing this. Given that I'm new to SQL Search and this technique it's possible that there is a way that I don't know about.
Thanks in advance.
Comments
Sadly I do not believe the search logic will be able to found the difference based upon the argument. For Stored Procedures the items listed will match on object name and on the text within the CREATE PROCEDURE syntax
For example, I have test database with a stored procedure called 'spINSERT_Guitar_Manufacturer', if I type manu into the search field, a number of items are listed. For this stored procedure, I have two entries one matching on the object name and the other matching on the text.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
Thanks for your response. Unfortunately I don't understand it!
If I select menu option (in SSMS 2014) "Script stored procedure as"/"CREATE TO"/"New Query Editor Window" from an s-p that I know is part of a group-named s-p I get the new window populated with the code for ALL the group-named s-p's in this group and going through that window I can show you each Create statement which I've copied here:
CREATE PROCEDURE [dbo].[stArrearsDeductions]
...s-p code...
CREATE PROCEDURE [dbo].[stArrearsDeductions];2
...s-p code...
CREATE PROCEDURE [dbo].[stArrearsDeductions];3
...s-p code...
CREATE PROCEDURE [dbo].[stArrearsDeductions];4
...s-p code...
This implies that SQL Server knows about this grouping and the association between the first and subsequent numbered s-p's.
However, the SSMS Modify option in 2014 doesn't return all these group-named s-p's and I only see the first (not-numbered) one. (Note that SSMS 2008 Modify option does return all the s-p's in the numbered group.)
So, the CREATE statement is in SQL Server with the semicolon - so if your search routine looks at that, I'd have thought it should be able to find the group-numbered s-p's?