Question Re: Local variables and select statements? in Technology Q&A

Local variables and select statements?

Former Member — Mon, 14 Dec 2015 16:11:59 GMT

If I use this in interactive it works:

declare @time1 time declare @Time2 time declare @time3 time select @time1 = t_time1, @Time2 = t_time2, @time3 = t_time3 from tblTimes select @time1, @Time2, @time3

But if I try to use it in a procedure I get error on the declares:

declare @time1 time; declare @Time2 time; declare @time3 time; select @time1 = t_time1, @Time2 = t_time2, @time3 = t_time3 from tblTimes;

So my question is how I do this in the best way in a procedure?

Re: Local variables and select statements?

MarkCulp — Mon, 14 Dec 2015 16:14:34 GMT

Please show us the procedure declaration and the error that you are seeing.

Re: Local variables and select statements?

Former Member — Mon, 14 Dec 2015 16:26:37 GMT

I get error message that the variables not exist. The declaration is what I showed in my question.

Re: Local variables and select statements?

Former Member — Mon, 14 Dec 2015 17:20:43 GMT

Without a create procedure block around that?
No Begin End block either?

If so then dbisql will execute each statement separately and your defines will go out of scope on the very next statement.

Re: Local variables and select statements?

Former Member — Tue, 15 Dec 2015 11:27:37 GMT

I do not know if I unclear or not. But what I mean is that I has a working procedure which I try to add this in the beginning of the procedure:

declare @time1 time; declare @time2 time; declare @time3 time; select @time1 = t_time1, @time2 = t_time2, @time3 = t_time3 from tblTimes;

My question was, why do I get the error message that the variables do not exist? Is it possible to do like this in a procedure?

Re: Local variables and select statements?

Breck_Carter — Tue, 15 Dec 2015 13:48:30 GMT

The "select @time1 = t_time1" is written using Transact SQL, so the procedure will have to be written using Transact SQL as well...

CREATE PROCEDURE p AS

declare @time1 time
declare @time2 time
declare @time3 time

select 
    @time1 = t_time1,
    @time2 = t_time2,
    @time3 = t_time3
from
    tblTimes 
select @time1, @time2, @time3
go

SELECT * FROM p()
go

If you want to use Watcom SQL (highly recommended!) then use the INTO clause...

CREATE PROCEDURE q()
BEGIN

declare @time1 time;
declare @time2 time;
declare @time3 time;

select 
    t_time1, t_time2, t_time3
into
    @time1, @time2, @time3
from
    tblTimes;

select 
    @time1, @time2, @time3;

END;
SELECT * FROM q();