Get Data Moving 1
Results 1 to 4 of 4

Thread: Stored procedure variables

  1. #1
    Integration Engineer is offline Junior Member
    Join Date
    Jun 2007
    Location
    Florida
    Age
    56
    Posts
    2
    Rep Power
    0

    Stored procedure variables

    Hi,

    my task is to take in a single parameter that contains two fields of my select where clause (they're both in the parameter).

    I want to have them separate because that's how the key is set up and seems to maximize. I don't want to prompt for the same parameter twice, I'd like to use a variable that I can parse for the data.

    How are variables used in virtual datasource stored procedures?
    Can they be used?

    for example:
    create stored procedure mine as
    declare @string as char (3)
    set @string = ?
    select a,b,c from table where keyfield1 = substring(@string, 1,2) and keyfield2 = substring(@string, 3,1)

    The syntax may not be exact but this should give you an idea of what I'm looking for.

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    AIS does not support declaring variables as in TSQL or PLSQL. Instread, when you define an AIS stored procedure, you can use named parameter so if you use the name in multiple places it will be the same value.

    Consider the following example using the default demo datasource NAVDEMO:

    create procedure sys:demoParam
    select r.r_name,n.n_name from navdemo:region r, navdemo:nation n where r.r_regionkey = n.n_regionkey and
    (substr(r.r_name,1,1) = :firstChar or substr(n.n_name,1,1) = :firstChar);
    Here :firstChar is used twice, obviously, the same value is used in both places.

    For example:

    NavSQL > call sys:demoParam('E');


    r_name n_name

    AFRICA ETHIOPIA
    EUROPE UNITED KINGDOM
    EUROPE RUSSIA
    EUROPE ROMANIA
    EUROPE GERMANY
    EUROPE FRANCE
    MIDDLE EAST EGYPT

    7 rows returned

    NavSQL >
    Last edited by DrorHarari; 06-16-2007 at 05:06 PM.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Integration Engineer is offline Junior Member
    Join Date
    Jun 2007
    Location
    Florida
    Age
    56
    Posts
    2
    Rep Power
    0

    Stored procedure variables

    Are named parameters supported in AIS 4.8?
    When I try to use a named parameter I am still getting a request for two parameters.

    "WHERE cust.office_1 = Upper(substr(:OfficeParam,1,2)) and cust.office_2 = upper(substr(:OfficeParam,3,1))"

  4. #4
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Named parameters are supported in 4.8

    When I try to use a named parameter I am still getting a request for two parameters.
    Still, your question is not clear. What are you trying and what do you get back in the log?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

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