I think that extended procedures are unremovable, also, if Microsoft discourages their use.

Perfect qotd and explanation.

Thanks!

John Mitchell-245523 (9/26/2013)

---

I know it's a matter of preference, but I'd rather have any complexity tucked away in the CTE definition than in the body of...

No recursion needed to create a dynamic tally table with a CTE:

-- Code below is SQL Server 2008 or newer

with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

...

Be aware of case:

DECLARE @str VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @str

It should be:

DECLARE @str VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE sp_executesql @str

🙂

John Mitchell-245523 (9/25/2013)

---

Clearly not useless, even if your personal preference is not to use them. And no more or less complicated, in my...

Dscheypie (9/25/2013)

---

Carlos,

sure, you may be right.

And the topic of this QotD is CTE and the usage of DML.

With the example for identifying duplicate records I wouldn't agree to CTEs...

CTE is useless and complicated!

The same result is reachable in a standard way:

create table #a (i int)

insert #a SELECT object_id FROM sys.objects

delete from t

from (SELECT * FROM #a WHERE i...

+0.5

Never used a CTE at all.

kapil_kk (9/23/2013)

---

While using SELECT it will give the output in a commented lines while using EXEC it gives the error :crazy:

declare @var Varchar(1000)

set @var = '-- /* select ''Samith'' name

...

I remember this one http://www.sqlservercentral.com/questions/T-SQL/71215/

Dscheypie (9/16/2013)

---

Carlo Romagnano (9/16/2013)

---

No, the scope is to the end of the batch, here, you can reference @j-2 out of the loop.

declare @i int

set @i = 2

while @i <...

Stewart "Arturius" Campbell (9/16/2013)

---

Carlo Romagnano (9/16/2013)

---

In the following batch @j-2 is declared once, but initialized at every loop:

declare @i int

set @i = 2

while @i < 10

begin

...

In the following batch @j-2 is declared once, but initialized at every loop:

declare @i int

set @i = 2

while @i < 10

begin

declare @j-2 int...

Koen Verbeeck (9/15/2013)

---

The question itself is great, however the explanation is lacking.

There's no reference, and the explanation itself is incorrect.

There's no variable that is "reset".

The DECLARE is not ignored, it...