I've sort of cobbled something that works, my next question is how do I return the value @alcount rather than print it?

declare @TmpName as varchar (2000)

declare @TmpName1 as varchar (2000)

declare @sdate as varchar (20)

declare @svar as varchar (2000)

declare @CMD Nvarchar(100)

declare @Status1 as varchar(2000)

declare @alcount as int

set @alcount = 0

set @sDate = '29/Apr/2013'

DECLARE CUR1 CURSOR FAST_FORWARD FOR

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource'

OPEN CUR1

FETCH NEXT FROM CUR1 INTO @TmpName

WHILE (@@FETCH_STATUS = 0)

BEGIN

if @tmpname <> 'Date' and @tmpname <> 'temp'

begin

SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + ''''

exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out

if @Status1 like 'A/L%'

begin

set @alcount = @alcount + 1

end

end

FETCH NEXT FROM CUR1 INTO @TmpName

END

CLOSE CUR1

DEALLOCATE CUR1

print @alcount