Skip navigation
Up to Discussions in TwinFin Talk
4325 Views 9 Replies Latest reply: Feb 21, 2012 9:52 PM by Jordan Chernev RSS
venki_rapolu New Enzee
venki_rapolu
10 posts since
Mar 8, 2011
Currently Being Moderated

Feb 2, 2012 4:40 AM

declaration of local variables

 

 

 

 

 

 

 

 

 

 

Hi,

 

     can you please help me with the syntax for declaring the variable to be used in the queries.

     I have looked at the examples that are given in 'Stored procs' but it is not working

 

    I am looking for something like below:

 

   

     declare

 

@name varchar(10)

  

     set

 

@name='Venki'

 

 

 

     select 

 

 

 

     select @name=emp_name

     from EMPLOYEE

     where emp_id=12345

 

     select @name

 

 

 

Thanks

Venkat

 

 

 

  • Bhuvaneswaran K New Enzee
    Bhuvaneswaran K
    43 posts since
    Nov 26, 2011
    Currently Being Moderated
    1. Feb 2, 2012 7:10 AM (in response to venki_rapolu)
    Re: declaration of local variables

    You can write like this,

     

    CREATE OR REPLACE PROCEDURE TEST1()
    RETURNS VARCHAR(ANY)
    EXECUTE AS OWNER
    LANGUAGE NZPLSQL AS
    BEGIN_PROC

     

    DECLARE

     

    V_NAME RECORD;

     

    BEGIN

     

    SELECT EMP_NAME INTO V_NAME FROM EMP
    WHERE EMP_ID=1;

     

    --you can take the entire record as well

     

    RAISE NOTICE '%',V_NAME.EMP_NAME;

     

    END;
    END_PROC;

  • venki_rapolu New Enzee
    venki_rapolu
    10 posts since
    Mar 8, 2011
    Currently Being Moderated
    2. Feb 2, 2012 9:04 AM (in response to Bhuvaneswaran K)
    Re: declaration of local variables

    this is again writing in stored proc right?
    cant we write in simple query?

  • Jordan Chernev Rookie
    Jordan Chernev
    183 posts since
    Jun 24, 2011
    Currently Being Moderated
    3. Feb 2, 2012 1:33 PM (in response to venki_rapolu)
    Re: declaration of local variables

    It doesn't seem like you can.

     

    The question becomes completely different if you want to use a parametrized proc with dynamic SQL. Then you'd have to use a loop to execute the dynamically built SELECT statement and return the results back to you.

  • Kapil New Enzee
    Kapil
    26 posts since
    May 24, 2010
    Currently Being Moderated
    4. Feb 3, 2012 2:08 AM (in response to venki_rapolu)
    Re: declaration of local variables

    You can try using following method:

     

    1) set a variable (I am assuming you are using Linux client)

     

    export abc=500

     

    2) nzsql -c "select count(*) from test where col1 = $abc;"

     

    You can also create a shell script and do add the queries as follows:

     

    export abc=500
    nzsql -c "select count(*) from test  where col1 = $abc;"
    nzsql -c "select count(*) from test  where col1 = $abc;"

     

    Execute the script and queries will use the variable set in script.

  • venki_rapolu New Enzee
    venki_rapolu
    10 posts since
    Mar 8, 2011
    Currently Being Moderated
    5. Feb 3, 2012 2:42 AM (in response to Kapil)
    Re: declaration of local variables

    Hi Kapil

         I am using Aginity Workbench.

         can you please help me in writing a simple query which declares a variable and sets the value from a table( no strored procs please )

    something like these:   

        

         declare @name varchar(10)


         select @name=emp_name from EMPLOYEE where emp_id=12345


         select @name

     

    Thanks

    Venkat       

  • Jordan Chernev Rookie
    Jordan Chernev
    183 posts since
    Jun 24, 2011
    Currently Being Moderated
    6. Feb 7, 2012 10:07 AM (in response to venki_rapolu)
    Re: declaration of local variables

    As I mentioned above, it doesn't seem like you can do it in NZSQL alone. Here's an unofficial workaround that I'm using in some of my procs:

     

    CREATE OR REPLACE PROCEDURE VENKAT()
    RETURNS INTEGER
    LANGUAGE NZPLSQL AS
    BEGIN_PROC
    DECLARE

     

        -- table_name ALIAS FOR $1;
        sql_statement varchar = '';
        r record;
        max_key int;
       
    BEGIN
       
        -- Here's a bit of NZPLSQL nonsense... you can't dynamically execute a SELECT statement.
        -- We've to use a loop to retrieve the value of the SELECT statement below
       
        sql_statement = 'SELECT COUNT(*) as records FROM DB_NAME..SOME_TABLE' ;
       
        FOR r in EXECUTE sql_statement
        LOOP
                    max_key := r.records;
        END LOOP;

     

        INSERT INTO CURRENT_DB_CONTEXT..SOME_OTHER_TABLE values (max_key, NOW());

     

        RETURN max_key;

     

    END;
    END_PROC;

  • nickg Active Enzee
    nickg
    248 posts since
    Mar 16, 2011
    Currently Being Moderated
    7. Feb 7, 2012 10:50 AM (in response to venki_rapolu)
    Re: declaration of local variables

    I think what others are trying to imply here is that you cannot declare and use variables in NZSQL.  You can only do it in NZPLSQL (i.e. in a stored procedure).  SQL (i.e. NZSQL) is not a procedural language and does not support the use of variables because declaring, assigning and use of variables are procedural in nature.  That is why databases support procedural forms of SQL through stored procedures and functions.  In all cases, these procedural languages are proprietary in nature in that each DB vendor has their own dialect and functionality.

     

    Now, it is easy to get confused when looking at some other implementations of SQL front ends.  Some vendors allow you to write in-line procedures within their editor and execute it on the fly.  However, under the covers it is actually creating, executing and then discarding a stored procedure.  Neither Workbench or native NZSQL support this.  It is not clear if this will be readily available any time soon because of semantic differences in the BEGIN statement between NZSQL and NZPLSQL.  In NZSQL, BEGIN initiates a transaction, while in NZPLSQL, BEGIN initiates a code block.  Trying to incorporate both dialects within the same interface can be confusing and vague.

  • nickg Active Enzee
    nickg
    248 posts since
    Mar 16, 2011
    Currently Being Moderated
    8. Feb 7, 2012 11:02 AM (in response to Jordan Chernev)
    Re: declaration of local variables

    Jordan Chernev wrote:

     

     

        -- Here's a bit of NZPLSQL nonsense... you can't dynamically execute a SELECT statement.
        -- We've to use a loop to retrieve the value of the SELECT statement below
       
        sql_statement = 'SELECT COUNT(*) as records FROM DB_NAME..SOME_TABLE' ;
       

     

    Not quite sure what you are referring to here.  You can always do this:

     

    declare mycount bigint;

    begin

         select count(*) into mycount from tablex;

     

    You should also be able to paramitize it and use execute immediate:

     

    declare mycount bigint;

         mysql varchar(2000);

    begin

         mysql := 'select count(*) into mycount from ' || $1;

         execute immediate mysql;

  • Jordan Chernev Rookie
    Jordan Chernev
    183 posts since
    Jun 24, 2011
    Currently Being Moderated
    9. Feb 21, 2012 9:52 PM (in response to nickg)
    Re: declaration of local variables

    Nick, I believe my example abive is wrong.

     

    However, I recall that I ran into this limitation at some point and had to resolve it via a FOR LOOP as suggested by the documentation. I'll try to dig up what was preventing me from doing an EXECUTE IMMEDIATE and update my post.

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points