The 'column has no name' error is caused by the statement 'select 1/0'. I suggest to use the RAISERROR statement for raising errors. I tested this using the following procedure:
create procedure up_test
as
begin
if (datepart(hh, getdate()) not between 12 and 24)
begin
-- select 'Error - Running outside allowed time window'
-- select 1/0
-- return
raiserror ('Error - Running outside allowed time window', 16, 0)
end
select 'A' as A into #temp
select A from #temp t
drop table #temp
end
Next problem you will face is that BIDS is not able to collect metadata of procedures returning data from temporary tables. If you call the procedure above with an OLE DB reader, the object will have no columns. Questions about this behaviour in SSRS projects comes up every now and then (for example, see the thread last week started by someone name 'scott'). In SSRS the workaround is to add the columns of the output of the stored procedure manually to a dataset. Unfortunately, in the above example I wasn't able to add the column using the Advanced Editor. If I hit the datatype property, the dialog disappears. Anybody know what the 'Add column' button is for?
Peter