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?

asked 14 Dec '15, 16:11

Rolle's gravatar image

Rolle
345182539
accept rate: 0%

edited 14 Dec '15, 16:17

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

(14 Dec '15, 16:14) Mark Culp

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

(14 Dec '15, 16:26) Rolle
1

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.

(14 Dec '15, 17:20) Nick Elson S...

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?

(15 Dec '15, 11:27) Rolle

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();
permanent link

answered 15 Dec '15, 13:48

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10
×2

question asked: 14 Dec '15, 16:11

question was seen: 282 times

last updated: 15 Dec '15, 13:48