Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Declare a variable in DB2 SQL

Does anyone know how to run the following SQL Server code in DB2?

I am converting SQL Server scripts so that they will run on a DB2 system and am having some problems wrapping my head around the use of variables in DB2.

T-SQL code

This is obviously not the actual code but works well as an example.

DECLARE @INPUT_VALUE INT
SET INPUT_VALUE = 4756

SELECT *
FROM TABLE1
WHERE TABLE1.COLUMN1 = @INPUT_VALUE
like image 747
I_AM_JARROD Avatar asked Jun 02 '11 15:06

I_AM_JARROD


People also ask

How do you DECLARE a variable in SQL query?

Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.

How do I DECLARE a host variable in Db2?

You can define a host variable in your program to hold each column. The host variable consists of the local variable name, preceded by a colon. You then can name the data areas with an INTO clause, as shown: EXEC SQL SELECT EMPNO, LASTNAME, DEPT INTO :CBLEMPNO, :CBLNAME, :CBLDEPT FROM EMP WHERE EMPNO = :EMPID END-EXEC.

How do you DECLARE an int variable in SQL?

Finally, let's look at how to declare an INT variable in SQL Server and assign an inital value. For example: DECLARE @site_value INT = 10; This variable declaration example would declare a variable called @site_value that is an INT datatype.


2 Answers

I imagine this forum posting, which I quote fully below, should answer the question.


Inside a procedure, function, or trigger definition, or in a dynamic SQL statement (embedded in a host program):

BEGIN ATOMIC
 DECLARE example VARCHAR(15) ;
 SET example = 'welcome' ;
 SELECT *
 FROM   tablename
 WHERE  column1 = example ;
END

or (in any environment):

WITH t(example) AS (VALUES('welcome'))
SELECT *
FROM   tablename, t
WHERE  column1 = example

or (although this is probably not what you want, since the variable needs to be created just once, but can be used thereafter by everybody although its content will be private on a per-user basis):

CREATE VARIABLE example VARCHAR(15) ;
SET example = 'welcome' ;
SELECT *
FROM   tablename
WHERE  column1 = example ;

like image 53
Michael Sharek Avatar answered Oct 01 '22 19:10

Michael Sharek


I'm coming from a SQL Server background also and spent the past 2 weeks figuring out how to run scripts like this in IBM Data Studio. Hope it helps.

CREATE VARIABLE v_lookupid INTEGER DEFAULT (4815162342); --where 4815162342 is your variable data 
  SELECT * FROM DB1.PERSON WHERE PERSON_ID = v_lookupid;
  SELECT * FROM DB1.PERSON_DATA WHERE PERSON_ID = v_lookupid;
  SELECT * FROM DB1.PERSON_HIST WHERE PERSON_ID = v_lookupid;
DROP VARIABLE v_lookupid; 
like image 36
JeffM Avatar answered Sep 29 '22 19:09

JeffM