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 ««12345»»»

A Function Gotcha with the Use of an Asterisk Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2008 1:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 6, 2010 7:39 AM
Points: 75, Visits: 58
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
Post #492688
Posted Wednesday, April 30, 2008 1:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:53 AM
Points: 10, 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.
Post #492692
Posted Wednesday, April 30, 2008 1:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 26, 2010 2:54 AM
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
Post #492694
Posted Wednesday, April 30, 2008 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 6:48 AM
Points: 23, Visits: 25
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?
Post #492709
Posted Wednesday, April 30, 2008 3:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 25, 2008 2:12 AM
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
Post #492755
Posted Wednesday, April 30, 2008 4:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:01 AM
Points: 1,070, Visits: 906
Do you not just mark them for recompile?


Post #492777
Posted Wednesday, April 30, 2008 5:27 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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...

Post #492822
Posted Wednesday, April 30, 2008 5:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 2,555, Visits: 591
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 !!!**
Post #492825
Posted Wednesday, April 30, 2008 5:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
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 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
Post #492827
Posted Wednesday, April 30, 2008 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 2:52 PM
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
Post #492892
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse