Jeff Moden (4/1/2008)

---

GSquared (4/1/2008)

---

But what advantage would this gain in real-world use? It's more work to set up, slightly more work to maintain, and I'm not clear on the...

And for a little more housecleaning, I'd replace:

CREATE Table #CertType (

Type VARCHAR(50),

XID INT

)

INSERT iNTO #CertType

SELECT DISTINCT Type, XID...

Replace:

and 1 =

case

when @CertType is null then 1

else

case

...

Since you've already hard-coded all of this part:

Create TABLE #CertStatus (

StatusID INT,

StatusDesc CHAR(20)

)

INSERT INTO...

Change:

ProcessDate BETWEEN @yesterday AND @today

to:

ProcessDate >=@yesterday AND processdate < @today

Otherwise, you'll run the risk of getting data from midnight last night (technically today) in the mix.

That one's Left Outer (as previously noted).

!= is not equal.

Okay. Now I can follow what's going on.

You have a trigger that uses a cursor to call a proc that uses a cursor to call another proc that calls...

Thinking about another post on this same thread, there is another solution, which would be to have every proc that needs the hierarchy create a temp table around a specific...

TheSQLGuru (4/1/2008)

---

Here is my take on reusability:

1) most code doesn't actually GET reused because a) all devs don't know about it, b) devs don't bother checking to see if their...

TheSQLGuru (3/31/2008)

---

GSquared (3/31/2008)

---

I've used UDFs in cases where the select logic was so insanely complex that a single query just couldn't do it (which leaves out views) and it needed...

Andre, I must be missing something. Your updateSettings proc doesn't seem to actually do anything except call itself recursively. I don't see any update/insert/delete commands in it. ...

You're welcome.

Without the temp table, you'd quickly run into the problem of too many selects, which Management Studio will bomb out on.

Of course, it's a really bad idea to run...

select dateadd(month, 1, cast(enddate + '01' as datetime)) - 1

from dbo.sasimp

Should give you what you need. If it gives you an error about converting enddate to varchar, try this:

select...

I modified the script as follows:

create table #Test (

DB varchar(100),

TableName varchar(100),

RowQty int)

DECLARE @dbname VARCHAR(50)

DECLARE @string VARCHAR(2500)

DECLARE db_cursor CURSOR

FOR SELECT name

...

I'm going to hazard a guess that [Date] and/or [DateImport] are datetime columns with the date AND the time in them. In which case, your Group By is almost...