Hello,
I am trying to convert some code from MSSQL to DB2.
This is something inherited, I would never make a table structure
like this. Please, can someone show me a sample how to do this
inside a stored proc.
Here is how I would do it in MSSQL
CREATE PROCEDURE myProc(@inputString VARCHAR(255),
@inputParam VARCHAR(255))
AS
DECLARE @SQL
SQL = 'SELECT * FROM myTable' + @inputString + ' WHERE myField LIKE ''' + @inputParam + '%'''
EXECUTE(SQL)
NOTE: The table name is dynamic... something like myTableA or myTableB.
There are potentially a bunch of tables with more coming, so it cannot be
split into multiple procedures.
Here is what I am trying to do in DB2
CREATE PROCEDURE myProc
(
IN IN_INPUT_STRING VARCHAR(255),
IN IN_INPUT PARAM VARCHAR(255)
)
RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
BEGIN
DECLARE STMT CLOB;
SET STMT = 'SELECT *
FROM myTable' + IN_INPUT_STRING + ' WHERE myField LIKE ''' + IN_INPUT_PARAM + '%''';
PREPARE SQLSTMT FROM STMT;
DECLARE C1 CURSOR FOR
EXECUTE SQLSTMT;
OPEN C1 ;
SET RESULT SETS CURSOR C1 ;
END
| 
