| |

VerySource

 Forgot password?
 Register
Search
View: 19|Reply: 5

How to pass the value of a variable defined inside a stored procedure to a SQL statement?

[Copy link]

1

Threads

1

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 China

Post time: 2020-3-1 21:00:01
| Show all posts |Read mode
A variable is defined inside the stored procedure, and its value is used in the SQL statement, which is written as follows:
CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
 startTime in date, endTime in date
)
as
minHyId number;
begin
WHILE (minHyId <= 20) LOOP
         SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
         minHyId: = minHyId + 1
END LOOP;
end;

Compilable always prompts errors. How should I write it correctly?
I don't know what kinds of writing methods can be used for variable transfer in stored procedures?
Reply

Use magic Report

0

Threads

99

Posts

58

Credits

Newbie

Rank: 1

Credits
58

 China

Post time: 2020-5-12 09:45:01
| Show all posts
declare @str varchar (1000)
set @ str = 'select * from tablename'
exec (@str)
Reply

Use magic Report

0

Threads

66

Posts

45

Credits

Newbie

Rank: 1

Credits
45

 China

Post time: 2020-5-12 13:30:02
| Show all posts
You can set the parameters to be returned to out type parameters, or directly change to function
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-5-12 23:15:01
| Show all posts
CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
 startTime in date, endTime in date
)
as
minHyId number;
inum number;
begin
WHILE (minHyId <= 20) LOOP
         SELECT count (*) into inum FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;-The query result is not saved in the variable
         minHyId: = minHyId + 1; --Less semicolon
END LOOP;
end;
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-5-13 01:45:01
| Show all posts
Compile error, use show error to view detailed information
Reply

Use magic Report

0

Threads

37

Posts

22

Credits

Newbie

Rank: 1

Credits
22

 China

Post time: 2020-5-13 10:30:01
| Show all posts
我是杀人狂
 
 
   declare @str varchar (1000)
set @ str = 'select * from tablename'
exec (@str)
  
 
-
Caixia, this is sqlserver syntax


WHILE (minHyId <= 20) LOOP
         SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
         minHyId: = minHyId + 1
END LOOP;
---
There is no int in front of select or behind
Definitely wrong

SELECT * FROM TABLE1 WHERE HY_ID = minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
Input variables are used in this way
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list