A Function Gotcha with the Use of an Asterisk

  • Do you not just mark them for recompile?

  • All good, but in general, using Select * is a bad idea, even if you need all of the columns. Better to specify in case someone changes something in the underlying schema.

    Using Select * can also burn you with views...

  • Ian Yates (4/29/2008)


    No problem 🙂

    Ian - in all the years that I've been using the query analyzer I never knew I could do that - if you have any more tricks up your sleeve, maybe you could compile them into an article and post it on this site...:)







    **ASCII stupid question, get a stupid ANSI !!!**

  • Ian,

    I too was going to post a script to do the job, but the drag/drop method is waaaaaaaaaaay better! 😎

    Here it is anyway

    create proc print_select_for

    @table varchar(128)

    as

    begin

    declare @s-2 varchar(max)

    set @s-2=''

    select @s-2=@s+case ordinal_position

    when 1 then 'select '

    else ', '

    end+char(13)+' '+column_name

    from information_schema.columns

    where table_name=@table

    order by ordinal_position

    set @s-2=@s+char(13)+'from'+char(13)+' '+@table

    print @s-2

    end

    go

    exec print_select_for 'something'

    Derek

  • I got tired of typing out the column names on large tables and simply bought a license of Redgate SQL Prompt and SQL Refactor. Both product provide for inputing the column names automatically. I was taught to never put SELECT * in any production code. Still feel that is good advice.

    Tim

    Denver, CO

  • The article is nice,

    Is there any other way,

    keeping the function same like tablea.* , can't we retrive the original values. like refreshing the function.

    Can we refresh the function after adding table.

  • Stephen,

    Interesting article. Thanks.

    Ian,

    I've been using QA for a long time and didn't know that trick. Thanks a bunch.

    This is why I read the posts after an article.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Another alternative to drag-and-drop is click on word Columns then Ctrl+C to copy then in query window position your coursor where it should go and Ctrl+V to paste. This way you don't have to be as precise with your mouse and you can paste it in multiple places if you need to.

  • Since you want to select all, the lazy way to do it and remain itemized is to simply script table as select to clipboard and paste it in. This also works well if you're going to include most of the columns and just need to delete a few from the list. Otherwise as someone has already mentioned you can use the object explorer to drag out the names of there are only a few columns needed.

  • Great article.

    Also, SQL Prompt is great. For those who haven't already tried it - go ahead, you'll be hooked. Note: I am just a happy user.

    Briefly described, it is Intellisense for your database queries. The three features I'd hate to query without are:

    1: It gives you column names and table names as you type. Two or three letters and a tab and you're done. Of course it needs to know which table(s) you are querying. Type the prefix and period, and it lists the columns for you to select. Or use ctrl+shift for a list of all.

    2: Killer feature: Joins. You type "JO" - it shows JOIN, you TAB & space and it shows your most likely join targets. You TAB and it picks the (selected) join target and includes a default alias for it. You type "ON " and it shows the most likely expressions to use, you TAB. With these 9 keystrokes I routinely generate well over 50 characters of a SQL statement. I have a database where I routinely perform between 7-10 joins, and with SQL Prompt I can write it in seconds - and my typing is far from stellar. This feature alone has paid for my SQL Prompt license, probably several times over.

    3: Want most columns and don't want to have to type - or even pick them all? Just type your query (joins and all) with SELECT * FROM , position your cursor behind the * and press TAB. Voila - you have a complete column list from all the tables in the join, prefixed with table aliases and ready for you to make any modifications you may want.


    Regards,

    Tore Bostrup

  • All you needed to do was to recompile the function (or drop and re-create it)

    Explanation: All compiled objects (Stored Procedures, Triggers, Functions, views, etc) are stored in the database in a separate area, and when you modify objects (i.e. table structure) used by these compiled objects they keep using 'last-know' structure until you update it (i.e. recompile).

    To see dependency of objects you can use the stored procedure: sp_depends

    A. to see all compiled objects which depend on a table:

    sp_depends tablename

    B. to see all objects (i.e tables, views, other SPs) which depend on a compiled object:

    sp_depends compiled_objectname

  • Why can't we just recompile the function everytime we execute a DML Statement on one of the tables in the Return List?

  • I use a function and a view in tandem to handle lists of columns. This is useful because I don't have to remember the actual table name, I can just query the view where stName like '%someFragment%'

    create schema mgmt

    go

    create function [mgmt].[ListColumns](@objectId int)

    returns varchar(max)

    as

    begin

    declare @list varchar(max)

    set @list = '';

    ;with cols as

    (

    select

    c.name as cName,

    row_number() over (order by c.column_id) as rowNum

    from sys.columns as c

    where c.object_id = @objectId

    )

    select

    @list = @list +

    case rowNum

    when 1 then '[' + cname + ']'

    else ', [' + cname + ']'

    end

    from cols

    return (@list);

    end

    go

    create view [mgmt].[cols]

    as

    select

    stName = s.name + '.' + o.name,

    o.object_id,

    mgmt.ListColumns(o.object_id) as columnList

    from sys.objects as o join sys.schemas as s on o.schema_id = s.schema_id

    where type in ('u', 'v')

    go

    Really I should just case the rowNum instead of defining the CTE twice, but it gets the job done anyhow.

    EDIT - the case thing was bothering me so I updated the function to use it.

  • Great article. We have all heard not to use SELECT *, but this is a concrete example of why. I generally stay away from SELECT * just for form, but now I can see how it can truly have disastrous consequences.

    I also great appreciated everyone chiming in with their tips. Both the tip to drag the columns folder and the tip to use the script feature were perfect. It was also fun to see people's scripts.

  • I ran into this same issue with views a few years back. Basically it does not recache the schema of the view without it being rebuilt so that the data appears in the same order as the columns in the table, without consideration that a column was added, so the names don't always match up, and there are no additional columns added to the view. I agree that it is good practice to always use table names in views and functions to avoid this problem, especially when there a many of them. There are a couple of ways to do this that work well, you can either use SQL Prompt(an awesome tool) and tab right after the * which breaks out the list of fields, or you can right click the table in the object explorer and create a select script which in turn creates a select statement with the field broken out. I did not know about dragging the columns folder.

Viewing 15 posts - 16 through 30 (of 151 total)

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