By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

T-SQL FROM SQL to DB2

P: 2
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
Oct 10 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.