Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

suganzenia
Starting Member

2 Posts

Posted - 2011-01-04 : 02:23:10

How to run a Dynamic SQL statment which has been coded in a COBOL-DB2 program.

If possible help me with sample RUN JCL for executing dynamic SQL in COBOL program.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 03:34:52

Well Not sure about the Cobol Syntax, however if you need help for running a Dynamic query at SQL server level... Then you can use the following

Create Procedure xyz
as
(
Declare @SqlString varchar(100) -- Give the length as required
set @SqlString ='Select ColumnName1,ColumnName2,...,ColumnNameN From DatabaseName.dbo.TableName'
Exec (@SqlString)

And Simply call this procedure in the the code.

Hope this may helps!

Cheers!
MIK

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 04:37:30

"Exec (@SqlString) "

Can I urge the O/P (and you Ibrahim, if you are not familiar with it

), to use

EXEC sp_ExecuteSQL

with PARMETERISED dynamic SQL instead. Avoids the risk of SQL Injection, and will perform MUCH better (100-fold speed improvement over "EXEC (@SqlString)" is entirely possible, although not guaranteed!)

suganzenia
Starting Member

2 Posts

Posted - 2011-01-04 : 06:10:26

Mik,
Thanks for the reply.

Actually i tried the cobol code as follows:

MOVE 'SELECT * FROM CUSTOMER WHERE PRIMARY_NM_IND="Y"' TO SQLCMD.

EXEC SQL
EXECUTE IMMEDIATE :SQLCMD
END-EXEC.

but after execution got "(System Completion Code=0C1)." abend.

Please help me out with this issue.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 06:20:22

Your SQL code needs to be


SELECT * FROM CUSTOMER WHERE PRIMARY_NM_IND='Y'

which may be the issue (not sure what you do about the single-quotes in Cobol though)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 06:44:26

@Kristen, Thanks for your comments and advise ..i'll check it. :)

@Suganzenia, Agreed with Kristen, the SQL syntax should be as follow

SELECT * FROM CUSTOMER WHERE PRIMARY_NM_IND='Y'

Since varchar datatype is enclosed in Single-Quotes in SQL server.... but this way the query will look like as follow

--> MOVE 'SELECT * FROM CUSTOMER WHERE PRIMARY_NM_IND='Y'' TO SQLCMD

and might give you an error due to the qoutes, and if does so may be you need to apply some sort of concatenation in order to construct correct assignment of the query to the Variable SQLCMD

Cheers!
MIK

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 08:45:51

Doesn't COBOL use double-quotes to delimit strings? In which case you need:


MOVE "SELECT * FROM CUSTOMER WHERE PRIMARY_NM_IND='Y'" TO SQLCMD.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources