How do convert below code from T-SQL into DB2 LUW?

Question

How do I convert this code from T-SQL into DB2 LUW, it seems so easy with T-SQL but in DB2 can't find any solution. See code below:

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)

SET @columnList = 'AddressID, AddressLine1, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM Person.Address WHERE City = ' + @city

EXEC (@sqlCommand)

Provide more details, format the code and tell us how you are executing that code. — data_henrik, Feb 01 '19 at 06:23
Actually, my goal here is how to put an SQL Statement like the code above @sqlCommand in DB2 LUW then Execute. — GeekyDad, Feb 01 '19 at 19:16

score 0 · Accepted Answer · answered Feb 02 '19 at 10:05

The problem is that you can’t ‘select to nowhere’ in a compound statement in DB2. Db2 CLP can return you the result set of a single sql statement, but it doesn’t try to do the same for select statements in a compound statement. If you want to print the result set from a select statement in a compound statement, you can, for example, declare a cursor, fetch it in a loop, and use dbms_output.put_line calls to print the values of variables.

score 0 · Answer 2 · answered Feb 16 '19 at 18:05

Not Pretty but you can find an example of the bottom of this page: Stored Procedures and Dynamic SQL Returning a Result set

Essentially you most:

1) create a dynamic SQL string 2) prepare the string into a statement 3) Link the statement to a cursor you're going to declare as WITH RETURN

Opening the cursor will be the last line in your procedure.

How do convert below code from T-SQL into DB2 LUW?

2 Answers2