listing columns in view and stored procedures

Using SS2000 SP4. If I create a view and list the columns in the select
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:

> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:

> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:

> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT