Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Exclamation Unanswered: sp_executesql in DB2 and running scripts

    I must generate a DB2 database from a SQL Server database.
    I am new in DB2. I have Enterprise 8.1 on Windows 2000.
    Can you help me with two problems, please:
    1) I don't know how can I translate sp_executesql in DB2 (in SQL PL).
    The procedure (in sql server) is the following:

    CREATE PROCEDURE dbo.NMS_Return_Records_rwc
    (
    @Table_Name VARCHAR(100),
    @Select VARCHAR(1000),
    @Where VARCHAR(1000) = NULL,
    @Order_By VARCHAR(1000) = NULL
    )

    AS

    DECLARE @vSELECT NVARCHAR(4000)

    SELECT @vSELECT = N'SELECT ' + @Select + N' FROM ' + @Table_Name

    IF COALESCE (@Where, '') <> ''
    SELECT @vSELECT = @vSELECT + N' WHERE ' + @Where

    IF COALESCE (@Order_By, '') <> ''
    SELECT @vSELECT = @vSELECT + ' ORDER BY ' + @Order_By

    EXEC sp_executesql @vSELECT

    I try and try but I don't know yet how Must I proceed.
    2) How can I run a script that will generate a number of stored procedures? I must use Command Center? Because until now I met some errors and I was forced to create the procedures one by one in development center.
    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    1) What does sp_executesql do ?

    2) Use @ as a delimiter for CREATE PROCEDURE STATEMENTS stored in file, say sp_creates.sql

    Then from the command line

    db2 -td@ -f sp_creates.sql

    HTH
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Running scripts from command prompt and sp_executesql

    SO if I understand correctly the @ symbol is something like go in SQL Server.
    So a script who must create a number of stored procedures in a database is like this one?

    script named generate_sp.sql

    connect to database_name

    @

    create procedure sp_1
    etc

    @

    create procedure_2
    etc

    @
    ...

    terminate

    IS it ok? If I want to build the stored procedures is it posible?
    I try to run this script using the following syntax:
    db2 td@ -f script named generate_sp.sql
    I receive the message "Command line environment not initialized". Is it a problem with the connection I suppose. Thanks in advanced for your support.
    ON the other hand, the sp_executesql permit dynamic execution of sql phrases. In that example I need to generate a simple sql query over a table. I would like after that to call that generic procedure giving the table name as parameter. I don't know exactly yet to call a procedure from another.
    For example a call like that (over a table Test)
    call NMSReturn_Records ('Test', '*', 'ID=1', 'ID')
    Last edited by AStefan; 06-23-04 at 04:51.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The command window should be a db2 command window (invoked using db2cmd from Start--> Run) and not the usual cmd ...

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •