Creating Functions for Stored Procedures

• msorens

  SSC Veteran

  Points: 288

  More actions

  October 14, 2008 at 11:50 am

  #884818

  Alex:

  Technical folks are busy people. Like me, they probably scan through a lot of articles very quickly, the vast majority of which are either (a) not relevant to them or (b) poor code or poor writing. Because I find your writing reasonable and your topics interesting, I thought it would be worthwhile to offer one tip, as a fellow author (http://cleancode.sourceforge.net/wwwdoc/articles.html). The general principal that applies to many things is this:

  Make it easy for people to do business with you.

  Applying this to the context of writing this becomes:

  Make it easy for people to see why your article is useful to them.

  I almost skipped your article because I had to scan and rescan and rescan again until I could say "Oh, yes. That is useful to know." What I would have found most useful--and no doubt others would as well--is to FIRST provide a concrete example, as in: "Say in your DB you have a GetMapDetails stored procedure which takes parameters x and y, and a InsertNewCustDetails stored procedure which takes parameters a, b, and c. If you run my SQL-to-.NET function creator, this will provide a .cs or .vb file containing this code: ."

• cliffb

  SSCarpal Tunnel

  Points: 4598

  More actions

  October 28, 2008 at 12:15 pm

  #890847

  A couple of options depending on your version of SQL to exclude microsoft objects.

  In SQL 2005, there is a field called is_ms_shipped. It's a bit value. 1 means it's a microsoft object, 0 if it is not.

  SELECT name

  FROM sys.objects

  WHERE type = 'p' and name not like 'sp[_]%' AND is_ms_shipped=0

  ORDER BY name

  To use this in SQL 2000 you would modify the SQL code to this:

  SELECT name

  FROM sysobjects

  WHERE type = 'p' and name not like 'sp[_]%' AND OBJECTPROPERTY(object_id,'isMSShipped')=0

  ORDER BY name

  Thanks for sharing this code.