SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Function Gotcha with the Use of an Asterisk


A Function Gotcha with the Use of an Asterisk

Author
Message
Carl Janssen
Carl Janssen
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 62
Thanks for the explanation. Since you explained the root cause, I'll be even more motivated to list al the columns by name.

By the way, I just use the ms SQL server management studio to create these queries : right-click on the table and select "Script table as" --> SELECT to --> new query window or the clipboard.
It lists the columns nicely, so you can delete those you don't want :-)
e.g.
SELECT [LOG_id]
,[LOG_PartNum]
,[LOG_AgileAccount]
,[LOG_DateTime]
,[LOG_ehub]
,[LOG_IPAdress]
,[LOG_msg]
FROM [dbDocCreator_Autonumbering].[dbo].[tblLogUsage]

Also very nice to quickly create INSERT, UPDATE queries.

Have a nice day
kll
kll
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 53
This problem also arises with views. If you do "select * from" within a view, you will get the column mismatching as well.
The select will fail if you have cut a column off your table, but inserted columns will offset data as shown.
Martin Loman
Martin Loman
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 36
Neet trick Ian!
I quickly made a script to return columns for a table but your trick was way cooler. :-)

But if you're somewhere in a deserted island without a nifty GUI some thing like this might help.

CREATE procedure util_GetColumnNames
(
@TableName varchar (100),
@Delimiter varchar (10) = ',',
@AsRow bit = 0
)
as

if not exists(select 1 from sys.objects where type = 'U' and Name = @TableName)
print 'Table does not exist.'
else
if (@AsRow = 1)
begin
declare @cols varchar(2000)

SELECT @cols = ISNULL(@cols + @Delimiter + '[' + C.Name + ']',
'[' + C.Name + ']')
FROM sys.objects O
inner join sys.columns C
ON O.object_id = C.object_id
where type = 'U'
and O.Name = @TableName

SELECT @cols
end
else
select '[' + C.Name + ']' + @Delimiter
from sys.objects O
inner join sys.columns C
ON O.object_id = C.object_id
where type = 'U'
and O.Name = @TableName



This still returns a trailing delimiter in the last row, but Im not bothering to fix it.

/Martin
tony.dyer
tony.dyer
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 26
Does anybody know if this (the original '*' gotcha) happens with nested functions.

I have a set of functions that each use expicit field names to query from tables (Coercing the fields to the same set of names in each query), but then an overarching function that queries '*'from each sub-function. If I changed the selections in sub-functions would I need to rebuild the overarching function?
M.van Leewen
M.van Leewen
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 360
I can recommend the suggestion of Ian to work in SQL Query analyzer and use drag and drop. It helped me a lot.

Thanks for the great explanation of this problem. You never know were the problem can cross your path. I will store it in my memory.

Menno van Leewen
RichB
RichB
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4692 Visits: 1083
Do you not just mark them for recompile?



SuperDBA-207096
SuperDBA-207096
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3993 Visits: 711
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...
sushila
sushila
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14091 Visits: 639
Ian Yates (4/29/2008)
No problem Smile


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...Smile







**ASCII stupid question, get a stupid ANSI !!!**
StarNamer
StarNamer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3974 Visits: 1992
Ian,

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

Here it is anyway

create proc print_select_for
@table varchar(128)
as
begin
declare @s varchar(max)
set @s=''
select @s=@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=@s+char(13)+'from'+char(13)+' '+@table
print @s
end
go

exec print_select_for 'something'



Derek
Tim Goldstein-414423
Tim Goldstein-414423
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 25
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search