Jeff Moden

Forum Replies Created

Viewing 15 posts - 47,326 through 47,340 (of 59,095 total)

RE: Stored Procedure speed versus Select Statement

I probably wouldn't do a lot of "IF's" although those can be quite effective provided that it's not building dynamic SQL. A well formed WHERE clause with CASE statements...
--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)

November 11, 2008 at 6:05 am

#896891
RE: maximum procedures, function or view nesting error level 32 exceeded

Lynn Pettis (11/10/2008)
Seems to me that a stored procedure, a temp table and a variation on the running count would give you what you need.
What do you think, Jeff?
The data...
--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)

November 11, 2008 at 5:58 am

#896887
RE: maximum procedures, function or view nesting error level 32 exceeded

krayknot (11/10/2008)
if you want i can forward that function on your email id.
Heh... post it here so we can all enjoy 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)

November 11, 2008 at 5:39 am

#896882
RE: Need Test SQL command

...and that still won't guarantee that the user can write to the DB. Oracle has it's own "system" views... if you want to see what privs a user has,...
--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)

November 11, 2008 at 5:36 am

#896880
RE: Need Test SQL command

Mazharuddin is correct. The user won't necessarily be able to do anything... it just verifies that the user can connect. If you want to verify the user can...
--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)

November 11, 2008 at 5:34 am

#896879
RE: Stored Procedure speed versus Select Statement

The best thing to do, if it can be done, is to use variables like the original code. Of course, the OR still needs to be fixed as I...
--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)

November 11, 2008 at 5:28 am

#896876
RE: Nested Transactions - Best Practice

Adam Hardy (11/10/2008)
Hi Jeff,
Thanks for the input, but I think you may have miss-read my post, I said I was utilising triggers for data integrity tasks, not business rules....
--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)

November 11, 2008 at 5:20 am

#896872
RE: Introduction to DML Triggers

Jack Corbett (11/4/2008)
Thanks Jeff. I could write something more detailed about INSTEAD OF triggers, but I've never actually used one in one of my databases. I use stored...
--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)

November 11, 2008 at 5:07 am

#896867
RE: Storing Negative Numbers

Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew...
--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)

November 11, 2008 at 5:03 am

#896866
RE: Stored Procedure speed versus Select Statement

Be careful, Sue... having a fully dynamic WHERE clause is very suseptable to SQL Injection attacks if the code is exposed to the general public.
--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)

November 11, 2008 at 4:57 am

#896862
RE: Inserting problem

You shouldn't ever bulk insert directly into the final table that already has rows in it. Bulk insert into a staging table first, check it for dupes, than then...
--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)

November 10, 2008 at 9:37 pm

#896713
RE: "primary key" in Table Variable

Jeff Moden (11/10/2008)
That sounds more like problems with a table variable. Why aren't you using a temp table, instead?
Heh... sorry... I forgot... you're happy with the code you have....
--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)

November 10, 2008 at 9:25 pm

#896706
RE: Maximum stored procedure, function, trigger or view nesting level exceeded (Level 32)

Continued at the following... on the 2k forum...
http://www.sqlservercentral.com/Forums/Topic599997-169-1.aspx
--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)

November 10, 2008 at 9:08 pm

#896697
RE: Importing Excel

You've got me... I use OPENROWSET for that type of stuff. Or, better yet, I'll have Excel write an importable CSV when they close the spreadsheet.
--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)

November 10, 2008 at 8:55 pm

#896693
RE: maximum procedures, function or view nesting error level 32 exceeded

ajaykini (11/10/2008)
Hi Experts,
I have developed a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on 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)

November 10, 2008 at 8:52 pm

#896692

Viewing 15 posts - 47,326 through 47,340 (of 59,095 total)