0

Let's say, I have a table as follows

tbl_1

id             name                 status               usertype                         flag
================================================================================================
1              Name 1                  1                    2                               0
2              Name 2                  1                    3                               0
3              Name 3                  1                    1                               0
4              Name 4                  1                    4                               0
5              Name 5                  1                    5                               0

HTML PAGE

<select id="type" name="type">

  <option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>

</select>

My requirement is that I need to get all the data from table where the usertype is selected from the html page by the user (could be 1, 2, 3,4,5, etc.)

I want to create a view, and call that inside stored procedure

I cannot pass an argument inside of a View. So, I was told to create a stored procedure, and call the view inside it. The problem is, I don't know how to do it.

1

... I was told to create a stored procedure, and call the view inside it.

You don't need a View.
You don't need a Stored Procedure.

Just select the data that you want to display, based on the criteria that you have.

select id, name, status, usertype, flag 
from tbl_1 
where usertype = ?
order by id ; 

The usertype argument will come from [the value of the selected "option" element within] the "select" element.

Depending on how your page(s) are structured, you might need an AJAX request to get the data or submitting a form containing the "select" element and processing that.

4
  • I need to use SP and/or view – Asish Apr 13 at 9:38
  • Why? What is the Requirement to do so? For the Problem Statement as you've given it, there is no need for either of these. Simple SQL will do the job - unless there's something more that you haven't told us about ... yet. – Phill W. Apr 13 at 11:08
  • There is nothing more. Our lead said that, it is a better and performance wise faster approach to use stored procedures and views. Which is why, I must use them as well, despite knowing that a simple SQL statement can do the job. We are talking about 100s of 1000s of data after all – Asish Apr 14 at 5:29
  • 1
    In the absence of any other justification, I would strongly disagree with your "lead". There is /no/ performance benefit to be gained by putting queries into Stored Procedures as opposed to running them directly. Indeed, procedures tempt people to write loops, which are almost /always/ slower than regular, set-based, SQL operations. If they insist that you /have/ to use them, then ask them how you should go about doing so. They will have done so before and so should be able to tell you. Of course, if they don't know how to /either/ ... – Phill W. Apr 14 at 9:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.