A Function Gotcha with the Use of an Asterisk

• NO37

  Newbie

  Points: 3

  More actions

  September 16, 2009 at 5:15 am

  #1054350

  For those lazy guys like me...

  You can create a SP that takes one varchar parameter that does all the lazy code generating work and with some extras like generating C# code to use as 'Parameters.Add(...'

  OR CRUD SP's for your solution.

  Just add the code generator SP to one of your Keyboard shortcuts (SQL2005/8: Tools/Options. Environment/Keyboard).

  Now you just highlight the table name in your query window and hit Ctrl+5 and it spits out the generated code for you.

  Then there is code generator SP's for your SP's for use in C# code too.

  You can do this with all the other popular sp_'s (sp_lock, sp_helptext, sp_who...)

  Here is a sample of a simple Select statement:

  select

  case WHEN ca.mincol = c.colorder THEN 'SELECT [' ELSE ' [' END

  +c.[name]

  +case WHEN ca.maxcol = c.colorder THEN ']'+char(10)+'FROM ['+o.[name]+']' ELSE '],' END

  from syscolumns c

  join sysobjects o on c.id = o.id

  cross apply (select max(colorder) as maxcol, min(colorder) as mincol from syscolumns c2 where c2.id = o.id) as ca

  where o.[name] = 'TableName'

  order by c.colorder

  If you want my code generator SP's just drop me a message.

  You can then modify it to your hearts content.

  Use it, don't use it. Up to you.

  LEON 'NO37'

• M. Furqan

  SSC Rookie

  Points: 26

  More actions

  October 7, 2009 at 2:01 am

  #1062626

  Another way to keep the function using * up-to-date is :

  1. alter the table (which u r using in function)

  2. sp_helptext 'MyFunctionName'

  3. Copy the result

  4. Replace "Create Function" with "Alter Function"

  5. Run it and its ALL DONE!

  UPDATE: for view use following

  sp_refreshview 'MyViewName'