How to implement a store procedure for the following query

Question

4.00/5 (1 vote)

See more:

I am having query as follows which will get multiple data at a time but i am not knowing how to write a store procedure for this can any one help me
This is my query

Select File_Data from tblachmaster WHERE Id IN (1,2,3, and so on);

As from my code i has to pass i would like to declare a variable in stored procedure and has to execute as per i needed

This is my code

string deleteSQL = "Select File_Data from tblachmaster WHERE Id IN (" + gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")";

This instead of writing like this i have to use store proc to execute

Posted 24-Apr-11 22:05pm

demouser743

Updated 24-Apr-11 22:29pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2011-04-24T22:15:00

Solution 1

I hope you are aware of how to write SP in mysql .

Here is the logic for your query please do sandwitch this in SP.

SQL

SET @test = Select File_Data from tblachmaster WHERE Id IN (' + @ConditionVar + ')';
PREPARE stmt FROM @test;
EXECUTE stmt;

If you need more help give me some more time I will give you exact SP.
But please let me know the result of above solution.

Posted 24-Apr-11 22:15pm

RDBurmon

Updated 24-Apr-11 22:25pm

v3

Comments

demouser743 25-Apr-11 4:26am

Hey through code i am getting this but as per in store proc i am not aware how to create the exact as per i needed

RDBurmon 25-Apr-11 4:28am

Do you know How to create Proc in SP with parameter and use that parameter in SP code. ?

demouser743 25-Apr-11 4:29am

Ya i know that

demouser743 25-Apr-11 4:30am

CREATE DEFINER=`root`@`%` PROCEDURE `usp_SelectFileData`(_Id varchar(100))
BEGIN
Select File_Data from tblachmaster WHERE Id IN (_Id);
END

demouser743 25-Apr-11 4:30am

But if i write like this i can not pass multiple values here right

RDBurmon 25-Apr-11 4:41am

You are half way through.
instead of executing Select File_Data from tblachmaster WHERE Id IN (_Id); directly .

Please dynamically create the query
like :
<Pre Lang="SQL">
SET @test = Select File_Data from tblachmaster WHERE Id IN (' + @ConditionVar + ')';
PREPARE stmt FROM @test;
</Pre>

and execute it with EXECUTE statment

RDBurmon 25-Apr-11 4:43am

and one thing do Pass "1,2,3,4,5 and so on " as a parameter value