Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    12

    Unanswered: insert exec procedure

    Hi

    I want to insert the value of the stored procedure into a table.

    insert into temp exec GetID 'Name', 'MyNo'

    I encountered the error: Incorrect syntax near the keyword 'exec'.

    Any help please?

    Thanks...

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    For syntax errors, you really should RTFM.

    Try:
    declare @x int
    -- ensure you have the correct datatype as per actual return parm of the sp
    exec @x = GetID 'Name', "MyNo'
    insert temp (column) values (@x)
    -- and do use a better table name
    go

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  3. #3
    Join Date
    Nov 2006
    Posts
    12
    Hi

    Thankas for the reply and I have tested it out. However I am unable to insert the actual value into the table.

    I am able to view the output of the ID on screen, but when I tried to insert into the table, the value is 0. It seems like it is unable to return the value of the procedure into the temporarily variable.

    My statement:

    declare @ID int
    exec @ID = GetID 'Name', "MyNo'
    select ID = @ID -- I print out the value to check before insert
    go

    Output screen:

    Warnings: --->
    W (1): 010P4: An output parameter was received and ignored.
    <---
    ID
    --------
    100

    1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

    ID
    --------
    0

    1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

    Is there any way to pass the result correctly? Please help.. ..Thanks a lot!

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    create proxy table on the proc
    Then insert into your table by selecting from the proxy table

    But why can't the proc insert into the required table?

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Yeah, it should work, INSERT with a value captured from a sproc is pretty ordinary.

    1 Well, the WARNING proves that the client software is not handling the return (output) parameter from the stored procedure. So, tweak the setting on your client software.

    2 Try just an INSERT with a specified value, eg. 9999. If that does not work (and the value inserted into the table is zero) then look for defaults, triggers and other gremlins.

    3 If you are a newbie, check for:
    - both chunks of code (the INSERT andd the SELECT FROM) are connected to the same db
    - both chunks of code are definitely looking at the same table (not dbo.table vs mooks.table)
    - both chunks of code have closed their transactions
    - you have INSERT permission on the table (you get an err msg if not, but your client s/w may not capture and report it)
    - try the whole sequence from isql (not your client s/w).

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Dec 2006
    Posts
    7
    hi,

    in your statement, you did not insert it into the table. was that the problem? because what DerekA has given shd work.

    However if you are looking to insert multiple rows [or columns], currently there is no easy way [to do it in sql itself].

    Meaning
    insert into table1 select blah from table2 --This will work
    insert into table1 exec getMyResultSet2 --This will not work

    conceptually both are resultsets. Don't know why Sybase did not provide this feature.

  7. #7
    Join Date
    Mar 2007
    Posts
    29
    Hi Experts,
    According to my understanding i feel that exec 'proc_name' will return the return status of the procedure. 0 if success.

    To insert the values returned by the procedure into the table is possible by selecting the output parameter of the procedure.

    This works only for one parameter output.

    wat is the case if multiple values are returned?

  8. #8
    Join Date
    Dec 2006
    Posts
    7
    A stored procedure can return several values; each must be defined as an output variable in the stored procedure and in the calling statements. The output keyword can be abbreviated to out.

    exec myproc @a = @myvara out, @b = @myvarb out

    use Sybase manual:
    http://manuals.sybase.com/onlinebook...44105;pt=44072

  9. #9
    Join Date
    Mar 2007
    Posts
    29
    Thank U Expert.

Posting Permissions

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