By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

how to insert data by calling procedure

P: 4
Hi all,
as part of my project requirement we are migrating procedures from mssql to mysql
below is the code in mssql
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE TABLE_DEPEND_INSERT_ORDER
  2. AS
  3.   CREATE TABLE #TABLE_DEPEND_ORDER_SUMMARY (
  4.     TABLE_NAME varchar(128),
  5.     SORT_ORDER int
  6.   )
  7.   INSERT INTO #TABLE_DEPEND_ORDER_SUMMARY EXEC TABLE_DEPEND_ORDER 'F'
  8.   SELECT TABLE_NAME FROM #TABLE_DEPEND_ORDER_SUMMARY ORDER BY SORT_ORDER DESC;
  9.  
how can i insert data by calling the stored procedure as above.
please do the need full.
Regards,
Vijaya
Aug 18 '08 #1
Share this Question
Share on Google+
4 Replies


Atli
Expert 5K+
P: 5,058
Hi.

What is it that you are trying to do there? (I don't speak Microsoft)

What have you tried so far?
Why / how isn't it working?
Got any errors that may help find the problem?

We are happy to help you solve problems with your code, but we will not write it for you.

Also.
Please post your code examples inside [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.

MODERATOR.
Aug 18 '08 #2

P: 4
Hi, here is the procedure i created in mysql as replacement of the above mssql one.
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE TABLE_DEPEND_INSERT_ORDER()
  2. begin
  3.   CREATE TEMPORARY TABLE TABLE_DEPEND_ORDER_SUMMARY (
  4.     TABLE_NAME varchar(128),
  5.     SORT_ORDER int
  6.   );
  7.   INSERT INTO TABLE_DEPEND_ORDER_SUMMARY call TABLE_DEPEND_ORDER ('T','F')
  8.   SELECT TABLE_NAME FROM TABLE_DEPEND_ORDER_SUMMARY ORDER BY SORT_ORDER DESC;
  9.   end
  10.  
when i compile this code it is giving me the below error message
Expand|Select|Wrap|Line Numbers
  1. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call TABLE_DEPEND_ORDER ('T','F')
  2.   SELECT TABLE_NAME FROM TABLE_DEPEND_ORDER_S' at line 7
  3.  
please provide me solution

Regards,
vijaya
Aug 19 '08 #3

Atli
Expert 5K+
P: 5,058
vijaya.

I tried asking you nicely. Now I am telling you:
Use [code] tags when posting your code examples.

Following the Posting Guideliens is not optional, and failing to do so will get you banned from these forums.

I would prefer not to have to do that, so please read the guideliens and follow them in the future.

MODERATOR
Aug 19 '08 #4

Atli
Expert 5K+
P: 5,058
As to your code.

You can not call a procedure in the middle of an INSERT statement.
Procedures dont' technically return a value, although they can be used to display the result of a SELECT.

Not knowing what this "TABLE_DEPEND_ORDER" procedure does, I would suggest trying to fuse the two procedures together.

That is; try to use the INSERT INTO table SELECT syntax, like:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tempTable SELECT * FROM realTable;
  2.  
You won't be able to just copy/past the MSSQL syntax into MySQL, even if you replace the keywords, as you did.
The way in which they implement procedures is very different. You will probably have to re-design a lot of them to fit them into MySQL's implementation.
Aug 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.