Jeff Moden

Forum Replies Created

Viewing 15 posts - 58,891 through 58,905 (of 59,067 total)

RE: Deadline Tomorrow - Limiting the zero value in averaging a row of data

Karen,
Bob and I both made the same boo-boo... in the code where the fields get added up, you must include all of that addition in parentheses or you'll get the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2004 at 6:05 am

#535386
RE: Deadline Tomorrow - Limiting the zero value in averaging a row of data

There's many ways to do this but most will involve a loop or a (yeeeeech!) Cursor (Haaaaaack... Patooooooiiiii!), a temp table, or maybe some extra columns added to your existing...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 11:17 pm

#535209
RE: Gathering Year-to-Date Info

Beryl,
Borrowing on Kathi's idea, this may run a bit faster because it does no string manipulation or conversion and only has one comparson operator (=). And it could easily be...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 10:10 pm

#535207
RE: Get sp names who is using given table name

I agree that dependencies related tables are (more or less) unreliable but the SYSOBJECTS and SYSCOMMENTS tables are always up to date with the latest code for any given object. The...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 6:39 am

#535130
RE: Stored Procedure :: Multiple Users

You may want to avoid using the guest account for several reasons... here's what the MSDN link that yoav provided warns of...
"Avoid using the guest account; all logins without their...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 6:24 am

#535129
RE: Problem with UPDATE Using IN

>Colleague put a trigger on the table that was causing the error.
Paully21,
There may be a much larger problem lurking in the shadows of that trigger you found... many folks write...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 6:13 am

#535127
RE: Get sp names who is using given table name

This code also works... Like the code that Kenneth posted, you should be made aware that if the, say, stored procedure is longer the 8k bytes and the table name...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 6:02 am

#535125
RE: Return the value of @@IDENTITY - best way?

You bet, Gazley... BOL tells you EVERYTHING and sometimes it's much more than you need (although, I always start there) or can be difficult to draw a conclusion from. It...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 5:45 am

#535124
RE: Return the value of @@IDENTITY - best way?

John,
SCOPE_IDENTITY() does require the use of parentheses like GETDATE() does. I didn't see them in the code you posted and I'm hoping that was the only problem you had.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 4:54 am

#535117
RE: Return the value of @@IDENTITY - best way?

Gazley,
The reason I said that using @@IDENTITY in the presence of triggers is "Death by SQL" is because of the way @@IDENTITY works... it (basically) returns the IDENTITY of the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2004 at 4:50 am

#535116
RE: using variables as tablenames?

Like I said, Joe... all of your explanations are spot on and I agree with telling folks of the dangers that lurk out there... but there's two ways to tell...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 27, 2004 at 9:06 am

#535050
RE: using variables as tablenames?

Dang, Joe... lighten up! All of your suggestions are spot on but you have to remember that, sometimes, it's a third party database that these folks have to deal with...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 27, 2004 at 1:33 am

#535009
RE: Return the value of @@IDENTITY - best way?

Then you already know that using @@IDENTITY in the presence of triggers is "death by SQL" and that the use of "it" is hardly ever the "best way" (as Ken was...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 27, 2004 at 12:26 am

#535005
RE: Generating Unique ID on Server

Thanks nskr72... I missed that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 10, 2004 at 7:10 am

#533324
RE: Help with Datetime field ...Please help!

You can avoid this...
IF (@FromDate = NULL)
SET @FromDate = '1/1/1900'
... by doing this... (just a bit faster, I think)
SET @FromDate...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 9, 2004 at 9:34 pm

#533282

Viewing 15 posts - 58,891 through 58,905 (of 59,067 total)