How to define and use global variable in SQL

Question

I'm a tester and I have to run lots of select queries to filter out the information I'm testing.

Is there a way to make global variable and use it instead of pasting same values?

Sample code:

Select l.prod_package,m.* from avtt7m0 m, avtt7l0 l 
where l.cust_id='52317162090004' 
and l.ar_id=m.ar_id and m.lc_st_code='ACT'; 

Select m.* from avtt7m0 m, avtt7l0 l 
where l.cust_id='52317162090004' 
and l.ar_id=m.ar_id and m.lc_st_code='ACT'; 

Select * From AKTTD90 
where cust_id in ('52317162090004'); 

Select * From Kndt7m0 
where cust_id in ('52317162090004');

You can use a t-sql variable and set it beforehand. If you post the code you are using instead of a picture I'll show you how to use it — Nick.Mc, Dec 05 '16 at 09:53
Which dbms are you using. (The answer will probably be product specific.) — jarlh, Dec 05 '16 at 09:56
I'm using SQL developer. Here is the code : Select l.prod_package,m.* from avtt7m0 m, avtt7l0 l where l.cust_id='52317162090004' and l.ar_id=m.ar_id and m.lc_st_code='ACT'; Select m.* from avtt7m0 m, avtt7l0 l where l.cust_id='52317162090004' and l.ar_id=m.ar_id and m.lc_st_code='ACT'; Select * From AKTTD90 where cust_id in ('52317162090004'); Select * From Kndt7m0 where cust_id in ('52317162090004'); — VItas, Dec 05 '16 at 13:11
How about I post it in the question for you. There that wasn't too hard — Nick.Mc, Dec 05 '16 at 14:39
Take a look at this: http://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer — Nick.Mc, Dec 05 '16 at 14:42

Kumar_Vikas · Answer 1 · 2016-12-05T13:31:12.620

0

You can declare the variable and assign it the customer value.

DECLARE @Customer BIGINT='52371762090004'

SELECT * FROM AKTTD90 WHERE Cust_id=@Customer

UPDATE: Try executing the above query.

edited Dec 05 '16 at 13:31

answered Dec 05 '16 at 09:54

Kumar_Vikas

837
7
16

It is not working. Im using SQL developer 4.1 can it bet the cause ? I don't know much about SQL querys. So it can be that I don't give all information for you to help me. But I will try answer if I can. – VItas Dec 05 '16 at 13:21
@VItas i have updated the answer. Do Check. – Kumar_Vikas Dec 05 '16 at 13:31
It gives an error. Do you know what can be wrong ? Error starting at line 1 in command: DECLARE @Customer BIGINT='52371762090004' SELECT * FROM KNDT7L0 WHERE cust_id =@Customer Error report: ORA-06550: line 1, column 9: PLS-00103: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: – VItas Dec 05 '16 at 14:17
That's T-SQL syntax not oracle syntax – Nick.Mc Dec 05 '16 at 14:41

jleach · Answer 2 · 2016-12-05T14:20:53.423

0

This aims at SQL Server - your question doesn't have a specific RDMS. You can Declare a variable (which must begin with @ then Set it then use it (in most cases):

SET @CustomerID = 12345
SELECT * FROM dbo.Customers WHERE CustomerID = @CustomerID

Note that this cannot be used for certain things, such as a variable for a table name. Also note that these variables are not actually Global in the sense that they will not survive a GO statement.

edited Dec 05 '16 at 14:20

answered Dec 05 '16 at 09:55

jleach

7,410
3
33
60

It seems SQL developer 4.1 do not like this syntax. Sory if I didn't gave all info. It's just becouse I'm new at SQL so I don't know what's important to mention. – VItas Dec 05 '16 at 13:29
edited for mysql/sql developer. that should work now (the declare portion isn't required for mysql). – jleach Dec 05 '16 at 14:21

score 0 · Answer 3 · answered Dec 05 '16 at 16:26

One option to do this is with PL/SQL-Package Variables:

create or replace package steptest as
  procedure set(a number);
  function get return number;
end;
/

create or replace package body steptest as
  x number;
  procedure set(a number) is
  begin
    x:=a;
  end;
  function get return number is
  begin
    return x;
  end;
begin
  x:=0;
end;    
/

And then you can:

exec steptest.set(1234);

select steptest.get from dual;

How to define and use global variable in SQL

3 Answers3