A Function Gotcha with the Use of an Asterisk

Question

Post reply

A Function Gotcha with the Use of an Asterisk

Viewing 15 posts - 61 through 75 (of 151 total)

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

sushil.patel SSC Enthusiast Points: 177 More actions · Answer 1

You can simply write a query instead of doing all the copy paste and use of excel

Select ',', [Name] from information_schema.columns where table_name like '

' order by ordinal_position

Copy the resultset and put it into your function query. The only thing you have to delete first "," of the column list.

Sushil;-)

sushil.patel SSC Enthusiast Points: 177 More actions · Answer 2

Sorry the query should be like this

Select ',', [column_Name] from information_schema.columns where table_name like '' order by ordinal_position

Sushil

sushil.patel SSC Enthusiast Points: 177 More actions · Answer 3

just now I've replied with simple query isntead of this long code

Select ',', [column_Name] from information_schema.columns where table_name like '' order by ordinal_position

Sushil

Rich Mechaber SSChampion Points: 11130 More actions · Answer 4

This sounds suspiciously like what I ran across in creating a VIEW that was based on a table that I added columns to. The additional columns don't show up; you have to DROP and re-CREATE the VIEW.

I didn't find an explanation about this on MS's websites, but I did find one about SYBASE (see last paragraph):

HTH,

Rich Mechaber

Rob Fisk SSCommitted Points: 1861 More actions · Answer 5

Scott

That CR vs CRLF thing with FOR XML is annoying isn't it.

I've just been replacing the hex code rather than inserting placeholders for carriage returns. Using one of your examples:

SELECT REPLACE(SUBSTRING(

(SELECT ', [' + [name] + ']

'

FROM sys.columns

WHERE [object_id] = OBJECT_ID('dbo.actionlist')

ORDER BY column_id

FOR XML PATH('')), 3, 9999),' ','')

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Rob Fisk SSCommitted Points: 1861 More actions · Answer 6

of course the ampersand#x0D; didn't display there.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I use CHAR(13) + CHAR(10) when I need to insert a sequence in a string.

crab-592037 Old Hand Points: 385 More actions · Answer 8

Just as an FYI, I found the same problem with a view that uses 'Select *'

jdk4 SSC Rookie Points: 28 More actions · Answer 9

sp_refreshview is a way to refresh non-schemabound views when their underlying tables change.

Agreed, functions defined with a fieldlist are much preferred to ones defined with select *. But we've found it a best practice to run sp_refreshview for all views that use a table that's changed schema, even views defined by select lists. Turns out BOL recommends that, too...although I didn't realize that until I looked it up just now in "create view".

Thanks for tips on creating fieldlists. I'd used a similar excel method, & also the drag 'n drop of individual fields, but the drag 'n drop of an entire column list is a new one on me, and MUCH appreciated!

crab-592037 Old Hand Points: 385 More actions · Answer 10

I just want to smack the person who originally wrote 'Select *'. it couldn't have been me...;-) in fact, it's friday, maybe I'll blow some time tagging all scripts with 'Select *'

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 11

Lynn Pettis (9/11/2009)
I use CHAR(13) + CHAR(10) when I need to insert a sequence in a string.

Management Studio seems to be pretty forgiving on EOL sequences. You can use either CHAR(13) or CHAR(10), using both strikes me as overkill. Often I just include the line break in a literal string instead of using the CHAR() functions, and let SSMS decide what character(s) are involved.

I would worry about using specific EOL characters if writing to an external file, but it doesn't seem to make a difference in the way it is displayed in the SSMS results pane. They all produce a line break.

brian.mitchell Newbie Points: 9 More actions · Answer 12

I have see that problem occur on Views using SELECT * also.

I use the following Proc to get field names. The proc will allow you to pass an alias and will prefix it to the field names if you want otherwise you can leave the parameter blank.

CREATE PROCEDURE dbo.xusp_GetColumnNames

@tableName varchar(255),

@Prefix varchar(50) = NULL

AS

select

'Column_name'= CASE WHEN @Prefix is null THEN ', ' + name else ', ' + @Prefix + '.' +name end

from syscolumns where id = object_id(@tableName) and number = 0 order by colid

GO

george.hames Grasshopper Points: 18 More actions · Answer 13

Good article - this could have saved me a few hours and quite a bit of head scratching a while back. But you are all being way too kind. THIS IS A BUG. Expansion of "SELECT *" must be either static or dynamic; it can't be half and half.

Many have commented on never using "SELECT *" in production code, and in general that's a good policy, but not because it might not work. If it's part of the language, it should work, always. The rule is that, at the application level, you should explicitly select the columns you will be using. If you just drag the whole column list into your SELECT, you are probably still violating that rule.

On the other hand, there are times when you don't care what the specific columns are, but you need to select all of them. This would seem to be a useful thing to do with a table function where the purpose is to filter rows from a table without making any assumptions about which columns will be used. In that case, copying the full column list into your SELECT doesn't wouldn't satisfy the requirement. "SELECT *" would if were expanded dynamically, but it isn't, and can't be, in a table function.

gth

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 14

A couple of things

1) If you get into the habit of creating UDFs WITH SCHEMABINDING then it won't let you select * anyway - doing that gives a good performance boost too.

2) If you haven't tried sql prompt / apex sql edit yet then wait a bit. I'm writing a SQL editor which will have full (and i do mean *full*) code completion, basic refactoring / layout, execution on multiple servers / databases, auditing - loads more. It will be out in about November and the community edition will have the best price of all - $0. 😀 edit -> PM me if you want to be on the Beta testers list btw...

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Rob Fisk SSCommitted Points: 1861 More actions · Answer 15

Lynn and Scott.

The issue with FOR XML is that it escapes out the CHAR(10) portion of CRLF. It CRs after the 13 and then inserts the hex for the 10.

This is the same if you just press enter mid string.

If I had been thinking more when I first came up on the issue though, I may have replaced my actual carriage returns with ' + CHAR(13) + '.

I didn't even think about what the chatacter code might mean, just replaced it out.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]