Post reply

A Function Gotcha with the Use of an Asterisk

  • 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'

  • 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'

Viewing 2 posts - 151 through 151 (of 151 total)

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