Click here to monitor SSC
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 Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 59
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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 Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

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



SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1479 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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2565 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 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
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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