SP naming convention policy

Question

SP naming convention policy

dallas13

SSCertifiable

Points: 7491
More actions
March 5, 2009 at 6:15 pm

#218948

hi
I have just started working on SQL 2008 and i m trying to create a policy
what i want is my procedures names should follow this naming conventions
like 'usP_ADVE_' where ADV = adventureworks database
How can i define that inside my policy so that i can apply that policy for all SP in all databases
i can define it like 'usP_substring(db_name(),1,4)_%' but its not working my be some syntax error
I am trying to put this expression in string or in ExecuteSQL task inside policy but something is wrong..
anyone can suggest anything here??
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

Gosh, I don't know how to build a policy. But, as a side bar, I'd never use a policy that forced any object name to use a prefix such as "usP_". Heh, in fact, I'd probably write a policy that forbids any stored proc from beginning with any form of "usp" or "sp" and maybe even forbid the use of any special characters especially underscores or dashes.

The reasons why I prefer not to name objects by their object type is...

1. Their use is obvious by the code they are contained in. For example, EXEC tablename would never happen but EXEC storeprocname would.

2. The objects are nicely grouped in the object tree of SSMS.

3. Object types can, in fact change. For example, what may be a view or table valued function today, could just as easily become a permanent working table tomorrow for performance reasons.

Yes, it's not likely that stored procedures will be changed to anything else, in my eyes, that kinda works against the whole idea of naming them after their datatype, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

Well thanks for ur extra comments and advice...

But if some one can share more knowledge abt how to build this kind of policy.. i appreciate

Thanks

Ross McMicken SSCarpal Tunnel Points: 4440 More actions · Answer 3

What happens whe you have the same application running several instances in different databases, which is how all of the apps I support work (same database structure many times, one for location a, one for location b, etc.)? Having the database name as part of the SP name seems to work against being able to do that, unless you rename all the SP's for every instance. What happens when you need to make a copy for testing purposes? I can almost understand including the application name in the SP, but not the DB name.

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

well i agree but irrespective of all tht we r going to create new system , we are building it from scratch..

Now so far i m using NAme facet...

Where @Name Like

and using this code inside executeSQL task

EXEC('DECLARE @Temp varchar(200);

DECLARE @Name sysname;

select @name where @name like ''_[a-z][a-z][a-z]%''

set @Temp = ''select ''''p_''''+substring(''''''+@Name+'''''',3,3)+''''_%''''''

EXEC (@Temp)

')

by aauming that @name will come from every SP name that this policy will scan.. but somehow its not working..

i want my code like p_abc_% format.. where abc = alphabets means position 3,4 and 5 are alphabets only..

Any feedback..?Thanks guyz..

and ya irerespective of any good or bad effects.. i want to achieve something like this..Thanks

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

dallas13 (3/12/2009)
well i agree but irrespective of all tht we r going to create new system , we are building it from scratch..
Now so far i m using NAme facet...
Where @Name Like
and using this code inside executeSQL task
EXEC('DECLARE @Temp varchar(200);
DECLARE @Name sysname;
select @name where @name like ''_[a-z][a-z][a-z]%''
set @Temp = ''select ''''p_''''+substring(''''''+@Name+'''''',3,3)+''''_%''''''
EXEC (@Temp)
')
by aauming that @name will come from every SP name that this policy will scan.. but somehow its not working..
i want my code like p_abc_% format.. where abc = alphabets means position 3,4 and 5 are alphabets only..
Any feedback..?Thanks guyz..
and ya irerespective of any good or bad effects.. i want to achieve something like this..Thanks

select @name where @name like ''__[a-z][a-z][a-z]__%'' ESCAPE '_'

Underscores are wild card characters, too. They need to be "escaped" to be used in a LIKE. Note that I doubled up the underscores in the LIKE filter string and then used it as a "self escaping" character.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

Sorry... almost for got...

select @name where @name like ''_\_[a-z][a-z][a-z]\_%'' ESCAPE '\'

The code above is one way to use underscores as both a single character wild card and a "literal". See Books Online for more on wild card characters in T-SQL's LIKE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dallas13 SSCertifiable Points: 7491 More actions · Answer 7

Thanks jeff

hmm almost there..but since i m not supposed to pass value or

declare variable in name facet of my policy..i cant use this code

which is fine..

EXEC (' declare @name sysname

set @Name = ''pftrigger''

select @name where @name like ''p\_[a-z][a-z][a-z]\_%'' ESCAPE ''\''')

instead i have to use something like this..but parsing error..

i have tried it different ways but still no result..

EXEC('Select ''p\_[a-z][a-z][a-z]\_%'' ESCAPE ''\'' ')

The goal now is

position 2 and 6 must be underscore..

so its giving me wrong results..when i m running this right now..

ExecuteSql('string', 'Select ''p_[a-z][a-z][a-z]_%''')

e.g.it says 'Promotesnapshotinfo' =correct..but its not..

same thing for 'p_newyork' -should be p_new_

how to embed escape in my dynamic code...!!?