| Author |
 Topic  |
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
 Posted - 11/13/2003 : 20:17:15
|
My boss ran the Best Practices Analyzer on one of our development servers and one of the things he traced for was SELECT *. It found a few instances (legacy code that we don't have time right now to change), but the strange thing was what it said. It said that SELECT * would not be permitted in stored procedures or UDFs in future versions. Now that's great, but starting at which version?
Rob, Nigel, Jasper, Vyas, do you know if Yukon has done this?
Tara |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 11/13/2003 : 21:02:06
|
| So far the beta version allows SELECT * in a sproc and UDF. I'd take exception to a "best practices" analyzer or compiler that disallowed that though. Might as well remove the SELECT * syntax entirely, what would be the point of disallowing it in sproc's and UDF's but allowing it in views and general queries? |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 11/14/2003 : 12:00:47
|
It's interesting that something scanning for best practices would tell you about something that would be completely forbidden in the future. However, at PASS they did show a little bit of what I will call auto-scripting of stored procedures. They demo'd some features that would build your sproc parameters based on the table definition (like for a simple INSERT sproc). This looked more like what someone would see who builds stored procedures in Enterprise Manager, and nobody here would do a thing like that, would they?
I also heard rumblings that they would detect a data type change in a table and automatically update stored procedure parameter definitions, etc. I didn't see this actually demo'd, and again, I'm sure this is only for those who are making their changes via the Enterprise Manager aspect of Workbench. I could rant on this topic for a long time, but let me summarize as this: 1) SCRIPT IT! and 2) I don't want the computer making changes in other objects for me automatically (at least not without my prior consent on each individual change).
--------------------------------------------------------
Visit the SQLTeam Weblogs at http://weblogs.sqlteam.com |
|
|
|
chadmat
The Chadinator
USA
1951 Posts |
Posted - 11/14/2003 : 13:19:15
|
I doubt Yukon will forbid this, however I hope it does happen soon. Usually they talk about forbiding something for a few releases before actually taking it out (To give you time to clean up existing code).
-Chad
http://www.clrsoft.com
Software built for the Common Language Runtime. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/14/2003 : 13:21:23
|
Whenever it does happen, it will be a dream come true.
Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 11/14/2003 : 18:44:17
|
I'd love to see it. It would upset SO many people 
Mark, if you want to see how to do cool code generation now, check out <broken record>Codesmith</broken record> from here http://www.ericjsmith.net/codesmith/
Maybe I should write an article about it, this tool saves me SO much time.
Damian |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 12/02/2003 : 15:25:43
|
It'd be awesome...but do you know how much stuff would begin to blow up?
Brett
8-) |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 12/02/2003 : 16:00:36
|
| stuff blows up now because people use select * |
|
|
|
Billpl
Yak Posting Veteran
USA
71 Posts |
Posted - 06/03/2004 : 21:53:12
|
ya know, the more I think about this subject the more I start to disagree.
For starters. When I developing I always use *. That way I can focus on the business object and pick and chose fields as needed. When I'm done I will simply determine which fields are needed and adjust the SP. Plus when I'm in SQL Analyzer (which is always open when I work) I'd hate to have to type in the field names. That would waste countless hours.
Second. What would be the logic in having it removed from SP's & UDF's but not general queries and Views. Wouldn't it be the other way around? The procedure level is where the best decisions can be made about the output data. The guy that's more likely to burn down the server is the arrant client who *'s out some 45 field monster table just to grab a few fields, not the low level guy who's maintaining tightly controlled systems that are prone to RAD business changes, etc.
One thought in the opposite direction. Expanding * functionality. How about something like this "Select a.*( -Field1, -Field6,.. )..." where you could leave off fields instead.
just a thought... |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 06/03/2004 : 22:32:57
|
As much as I hate to say it.. this is not a good idea. Actually I think they have it the wrong way around..as Bill said.. It should be prohibited from views!
By all means, tell me I suck when the "analyser" finds its use but please, leave it to me!
Bill, the REMOVE keyword is used in only one product I know and the language is not SQL..
FYI, you're wish would be written as "SELECT Employee REMOVE { Field1, field6 }".. There is no need for a "*" simply because it has a real projection operator.
"SELECT Employee" returns all columns, projection is achieved with the "OVER" keyword..
http://docs.alphora.com/D4LGProjectandRemove.html
DavidM
"Always pre-heat the oven" |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/04/2004 : 07:29:09
|
if exists (select * from ...)
select * is really useful to get the column names when you are looking at a system from query analyser.
Would be nice to prevent it in SPs, views, ufd's etc except against temp tables but you would also want to prevent it being used from applications - don't see how you could do that and stil allow it from query analyser.
An app to build SPs based on table defintions and updating when the table structure changes has to be one of the most useless things I've heard of.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/05/2004 : 16:29:42
|
:) Well, at least you have an opinion Nigel.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 06/06/2004 : 07:22:10
|
quote:
Originally posted by nr
select * is really useful to get the column names when you are looking at a system from query analyser.
I have a little SProc that does that - gives me a comma separated list (e.g. for INSERT), each column name on a separate line with a trailing comma (e.g. for SELECT), repeated using friendly name (e.g. SELECT [Customer name] = CustName, ...), DECLARE statement, JOINs for all FKeys and the PKs as a comma separately list (useful for ORDER BY).
I just cut&paste the bit from the results into whatever I'm building (I appreciate that this is mostly available from Object Browser, but there were some style things I wanted of my own).
quote:
An app to build SPs based on table defintions and updating when the table structure changes has to be one of the most useless things I've heard of.
We do that! We have standard SProcs for GET / SAVE and DELETE. These are mechanically generated with defined areas where we add Business Rules etc. When we change the schema of the table we re-run them and compare&merge the "New" and "Actual" versions.
(FWIW our SAVE SProcs are "UpSert" - they INSERT if not exists, otherwise UPDATE. They have another of other abilities (basically to cater for data conversion or parameters originating from Web based FORMs)
Kristen |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/06/2004 : 20:44:53
|
>> I have a little SProc that does that
you mean
select name + ' ,' from syscolumns where id = object_id('tbl) order by colid
I always just code it when I need it.
I meant getting the col name to paste into a query.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 06/07/2004 : 05:29:16
|
quote:
Originally posted by nr
>> I have a little SProc that does that
you mean
select name + ' ,' from syscolumns where id = object_id('tbl) order by colid
Not quite, it does that but in a variety of different flavours so I can cut & paste whichever chunk I need
quote:
I meant getting the col name to paste into a query.
Yup, I do do that - SELECT TOP 10 * FROM MyTable - seems so much quicker to me than clicking around the Object Browser, but its horses for courses.
I'll bung some sample output from my SProc below, using Northwind [Order Detail] as an example. I've had to contrieve the JOINs 'coz my SProc relies on some intermediate data in tables which I haven't got set up in Northwind, so I did find&replace on some of the names to make the example, but you'll get the idea no doubt!.
One slight benefit is that I can pass a partial LIKE table name to the SProc, or even a column name, which saves a bit of typing - it provides a list of suitable EXEC commands if the table name is ambiguous.
Kristen
------------ -------------------------------------------------
Column list: OrderID, ProductID, UnitPrice, Quantity, Discount
Table: dbo.[order details]
PK: OrderID, ProductID
SELECT (syscolumns)
--------------------------------------------------------
SELECT
OrderID, -- Order Number
ProductID, -- Product Number
UnitPrice, -- Unit Price
Quantity, -- Quantity
Discount -- Discount
FROM dbo.[order details]
ORDER BY OrderID, ProductID
SELECT (sysproperties)
----------------------------------------------------------------------------
SELECT
[Order Number] = OrderID,
[Product Number] = ProductID,
[Unit Price] = UnitPrice,
[Quantity] = Quantity,
[Discount] = Discount
FROM dbo.[order details]
ORDER BY OrderID, ProductID
DECLARE
----------------------------------------------------------------------------
DECLARE @OrderID [int], -- Order Number
@ProductID [int], -- Product Number
@UnitPrice [money], -- Unit Price
@Quantity [smallint], -- Quantity
@Discount [real], -- Discount
PK Columns
---------------------------------------------------------
OrderID,
ProductID,
JOIN Description Parent Column Child table
---- ---------------------------------------- -------------- ---------------
J Order ID:Order Header OrderID [Orders]
SELECT TOP 10 *
FROM [order details] OD
LEFT OUTER JOIN dbo.[Orders] OI
ON OI.OrderID = OD.OrderID
JOIN Description Parent Column Child table
---- ---------------------------------------- -------------- ---------------
J Product Code:Product ProductID [Products]
SELECT TOP 10 *
FROM [order details] OI
LEFT OUTER JOIN dbo.[Products] P
ON P.ProductID = OI.ProductID
JOIN Description Parent Column Child table
---- ---------------------------------------- -------------- ---------------
D Order ID:Order Item OrderID [order details]
SELECT TOP 10 *
FROM [Orders] OI
LEFT OUTER JOIN dbo.[order details] OD
ON OD.OrderID = OI.OrderID
-- Use this query to produce a column list with a count of the number of rows (where used)
------------------------------------------------------------------------------------------
SELECT ' OrderID,', '--',
(select SUM(CASE WHEN OrderID IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT ' ProductID,', '--',
(select SUM(CASE WHEN ProductID IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT ' UnitPrice,', '--',
(select SUM(CASE WHEN UnitPrice IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT ' Quantity,', '--',
(select SUM(CASE WHEN Quantity IS NULL THEN 0 ELSE 1 END) from [order details])
UNION ALL SELECT ' Discount,', '--',
(select SUM(CASE WHEN Discount IS NULL THEN 0 ELSE 1 END) from [order details])
-- Use this query to produce a column list with Max Length of the data in that column
-------------------------------------------------------------------------------------
SELECT ' OrderID,', (select MAX(DATALENGTH(OrderID)) from [order details])
UNION ALL SELECT ' ProductID,', (select MAX(DATALENGTH(ProductID)) from [order details])
UNION ALL SELECT ' UnitPrice,', (select MAX(DATALENGTH(UnitPrice)) from [order details])
UNION ALL SELECT ' Quantity,', (select MAX(DATALENGTH(Quantity)) from [order details])
UNION ALL SELECT ' Discount,', (select MAX(DATALENGTH(Discount)) from [order details])
|
|
|
|
aiken
Aged Yak Warrior
USA
525 Posts |
Posted - 09/20/2004 : 18:05:53
|
I'll resurrect this one.
First, for getting column names, what's wrong with "sp_help tablename"? It's easier to type, and it shows you the datatypes as well.
Second, for "if exists", why not "if exists (select 1 from tablename where...)"?
Me, I'm happy about this change.
Cheers
-b |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 09/21/2004 : 07:21:26
|
quote:
Originally posted by aiken
Second, for "if exists", why not "if exists (select 1 from tablename where...)"?
this is acctualy the only exception when (select * ...) should be used. has to do with indexes. sql server chooses the best index to execute this and so it's faster.
i read it on this very forum 
Go with the flow & have fun! Else fight the flow  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 09/21/2004 : 07:56:54
|
as soon as the search page doesn't time out on me i'll try to find it.
Go with the flow & have fun! Else fight the flow |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/21/2004 : 13:59:44
|
"what's wrong with "sp_help tablename"?"
It doesn't present the columns in a style suitable for use in SELECT or INSERT etc. which is what I wrote my little SProc to do instead.
I have since added the ability to create a SELECT list suitable for INSERT statement and XML in/output:
SELECT
sop_ordh_ID = 9999999, -- Order ID [Identity] [PK]
sop_ordh_Date = 'ddmmyyyy', -- Date
sop_ordh_cust_Code = 'XXX30', -- Customer Code
sop_ordh_TotalPrice = $99999.99, -- Total Price
...
FROM dbo.DNG_SOP_ORDH_OrderHeader
ORDER BY sop_ordh_ID
where the required type of the field is indicated (mainly used for importing from "weird" sources, hence need to constrain input to appropriate type and length (my SProc is aware of the superset-datatypes we use, hence 'XXX30' implies max 30 alphanumerics, in a forced Capitals field)
and
SELECT
[Tag] = 1,
[Parent] = NULL,
[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_ID!element] = 9999999, -- Order ID [Identity] [PK]
[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_Date!element] = 'ddmmyyyy', -- Date
[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_cust_Code!element] = 'XXX30', -- Customer Code
[DNG_SOP_ORDH_OrderHeader!1!sop_ordh_TotalPrice!element] = $99999.99, -- Total Price
...
Kristen |
|
|
|
Topic |
|
SELECT * not allowed in Yukon?
Forum Locked
