MySql Stored Procedure

Question

Hi I am creating a stored procedure for Insert and Select data in MySql  my stored procedure is - DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(_action varchar(10) File varchar(100)) BEGIN if _action = 'INSERT' then insert into tab_file (File) values (File); end if; if _action = 'select' then select IDFile from tab_file; end if; END With this stored procedure i am able to insert data but select query is not working.It is giving error like Expected 2got 1 Please help me how to write stored procedure in mysql forselect insert update and delete scripts? Thanks

Sunny Sharma · Answer

Dear Rachayita No problem I'm glad I could help and I promise I will make you learn this thing :) In case you have different parameters like namephoneaddressfile........so on create a string[] that contains all the param names i.e.: ------------------------------------------------------------------------------- string[] NullParams = {namephoneaddressfile...}; cmd.parameters.addwithvalue(_actionSelect); foreach(string str in NullParams) { cmd.parameters.addwithvalue(str null ); } -------------------------------------------------------------------------------- How's that?

Sunny Sharma · Answer

It's a pleasure helping you cheers!

rachayita jaiswal · Answer

Hi Sunny It's working fine.Thank you so much for helping me. Rachayita

rachayita jaiswal · Answer

Hi Sunny Thanks for reply and sorry i am keep asking you questions but parameters will be different like - namephoneaddressfile........so on cmd.parameters.addwithvalue(name null ); cmd.parameters.addwithvalue(phone null ); cmd.parameters.addwithvalue(address null ); cmd.parameters.addwithvalue(file null ); cmd.parameters.addwithvalue(- null ); - - how can we use for loop for this?

Sunny Sharma · Answer

Dear Rachayita This is where we need to apply our logic i.e. how could this be achieved. of course there are many ways to do so and the best I can suggest you is to us a for loop. Use a for loop if you already know that you've twenty parameters and you need to send only one. See this: --------------------------------------------- cmd.parameters.addwithvalue(_actionSelect); for(int i=0;i { cmd.parameters.addwithvalue(File null ); } --------------------------------------------- Hope it satisfies your question :) Cheers!

rachayita jaiswal · Answer

Hi I did this only but my question was same that if i have 20 parameters in stored procedure then i have to write here- cmd.parameters.addwithvalue(_actionSelect); cmd.parameters.addwithvalue(File null ); ' ' ' ' 20 times ??

Sunny Sharma · Answer

Do it like this: cmd.parameters.addwithvalue(_actionSelect); cmd.parameters.addwithvalue(File null ); //(use null values when second parameter is not required) For your second query Yes it will expect two parameters every time you call this stored procedure ( as I've already explained ). It doesn't matter what you do inside the procedure with a variable. What matters is the count and type of parameters are matching or not :) Cheers!

rachayita jaiswal · Answer

Hi cmd.parameters.addwithvalue(_actionSelect); cmd.parameters.addwithvalue(Filetest); /* this is my empty string... so where i should mention null value as parameter? i have mention File default null in mysql stored procedure but it is still asking for data in my c# code??

Sunny Sharma · Answer

Hi Rachayita Of course not in that case you can just pass a null value as a parameter.

rachayita jaiswal · Answer

Hi Sunny I tried with empty string and it is workingthanks for this but tell me one thing if i have 20 parameters in my stored procedure then we will define 20 empty strings in my code?

Sunny Sharma · Answer

Hello Rachayita Your code for stored procedure is all fine & working like a charm. The reason why you're getting this error is you're passing only one parameter while select. The procedure will expect always two parameters since none of them are optional and MySQL also doesn't support optional parameters in Stored Procedures yet. So the conclusion is pass any empty string (preferably) or whatever you want as second parameter also to get the Stored Procedure working fine. Hope you got the point. Happy Coding :) Mark it as answer if it helps. Cheers!