470,614 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

T-SQL FROM SQL to DB2

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
0 1264

Post your reply

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

Similar topics

2 posts views Thread by Steve | last post: by
2 posts views Thread by Steve | last post: by
2 posts views Thread by dynoweb | last post: by
1 post views Thread by TOM GUGGER | last post: by
16 posts views Thread by David Lozzi | last post: by
7 posts views Thread by Filips Benoit | last post: by
8 posts views Thread by David Lozzi | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.