Yes I think sysdatabases in 2005 is implemented as a view on sys.databases

from 2005 BOL

In SQL 2005 you can use sys.databases or sysdatabases

On 2000 you only have sysdatabases

I think.

HTH - I know where to come looking when I my ADSL starts playing up 🙂

The t and p are table aliases, you put them after the table name in the FROM clause and can then use them to refer to that table elswhere in...

Something like (Air code)

CREATE PROCEDURE rpCountThePostcodes @InputString VARCHAR(255), @RowCount INT OUTPUT

AS

BEGIN

CREATE TABLE #Postcode

(Postcode CHAR(3))

/* get some code to parse the string into table above */

SELECT @RowCount = COUNT(*) FROM #Postcode...

Hi Steve,

My aproach would be something like,

Stored Proc that accepts the comma delimited string as input and returns an int.

In the stored proc unpack the string into a temp table...

Something like

CREATE TABLE #Rule

(ID INT IDENTITY,

[Rule] CHAR(10),

Pass DECIMAL(5,4),

Fail DECIMAL (5,4))

INSERT INTO #Rule SELECT 'rule1', '0.3', '0.7'

INSERT INTO #Rule SELECT 'rule2', '0.3', '0.7'

INSERT INTO #Rule SELECT 'rule3',...

I'm missing a comma after as OtherTotal

So something like

declare @OTMax decimal

set @RegMax = 40.0

select Dept,

sum(TotalHours) as PayTotal,

sum(case when (Type = '10') then TotalHours else 0 end) as RegTotal,

sum(case when (Type = '4') then TotalHours...

Can you post the actual error message, I'm guesssing here (because I can't see the message) but you might need to wrap a SUM( ) around your CASE.

What error message are you getting, it should be possible - probably a syntax probllem.

Are you on SQL 2005

But will your stored proc differentiate between 1 and 11 ?

Hi Raghavendra

Not sure I understand how you did it using LIKE.

Regarding performance it really depends on your SQL query and the indexes you have on your table.

Allen

Hi Raghavendra

Your second table is not normalised. The comms seperated document list is the problem.

You should remove the document list from the department table and create a third table...