Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

A Function Gotcha with the Use of an Asterisk Expand / Collapse
Author
Message
Posted Wednesday, September 16, 2009 5:15 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 12:52 AM
Points: 1, Visits: 23
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'
Post #788837
Posted Wednesday, October 07, 2009 2:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 02, 2010 3:36 AM
Points: 10, Visits: 25
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'
Post #798982
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse