Manole,

your question is very good.

Procedure is created "with execute as caller".

When we call the procedure with a user that has no rights on underlying objects (objects that procedure uses),

it would...

Sorry, it will work in a view. My bad.

SELECT COUNT(DISTINCT ClientName) AS NoClients, COUNT(desc) AS NumMtrInv, desc, SUM(sales) AS Fees, AVG(Fee) AS Avgsales,

SUM(sales) / COUNT(DISTINCT ClientName) AS AvgSalesPerClient

FROM dbo.VW_sales

GROUP BY desc

ORDER BY COUNT(DISTINCT NoName), Desc

Thanks Stewart, you are right. I don't know from where I concluded cte is not for view. Maybe sometime ago I tried that and it didn't worked, but don't know...

Exactly. That's what I tried to explain. Not just...

Yes, but CTE bring limitations: it wont work in a view, and it works on less SQL versions that without CTE. So, my choice is to use a technique when...

Dear Gilla, I tried to...

RAISERROR with NOWAIT is also nice because you can build a message inline (using placeholders %d, %s, etc):

RAISERROR (' now is executing step %d...' ,10,1, @step_number) WITH NOWAIT

Add optional parameter...

CTE is good solution for recursions and if you need same expression more than once, but this is not the case here.

So, the same thing without CTE:

SELECT it.*

FROM

(SELECT items.*,

rn =...

You need to read some good literature about indexes. It's difficult to put it in few sentences, but I'll try.

Every index consumes space and it has nothing to do with...

You did not provide a script to create a table with sample data, so it makes much harder to answer your question.

Your example looks like you need a column with...

SELECT o.orderno,

Descr1_PriceSum = SUM(CASE WHEN o.Descr=1 THEN o.Price ELSE 0 END),

Descr2_PriceSum = SUM(CASE WHEN o.Descr=2 THEN o.Price ELSE 0 END)

from dbo.orders o

group by o.orderno

ordernoDescr1_PriceSumDescr2_PriceSum

1100,000,00

2142,000,00

388,000,00

40,00244,00

If you want Descr to be...

No, unfortunately you cannot call sp from a function. There are also other limitations (no PRINT, RAISERROR, no try-catch, temp tables not visible, no changing of db tables allowed etc).

But,...

Simplest form:

select Decade = convert(VARCHAR(9), s.s_date, 120), Amount = sum(s.amount)

from dbo.sample s

group by convert(VARCHAR(9), s.s_date, 120) -- 120 = yyyy-mm-dd hh:mi:ss

You could use variation, e.g. month + decade, fill empty...

Execute your query without GROUP BY to see the values it actually sums.

Probably you have bad join condition as Dwain wrote.