Add where clause after a stored procedure.

Question

Add where clause after a stored procedure.

adonetok

SSCrazy Eights

Points: 8395
More actions
July 18, 2017 at 6:20 am

#322652

In my asp.net project, I need to code to select a data from a stored procedure and then bind to a grid.
Since I did not have permission to modify the stored procedure, how to add where clause to select data from the result of stored procedure?
For example, the result of the stored procedure may return 10000 records but for my project, I only need to select "where city='London'".
How to complete it?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 1

adonetok - Tuesday, July 18, 2017 6:20 AM
In my asp.net project, I need to code to select a data from a stored procedure and then bind to a grid.
Since I did not have permission to modify the stored procedure, how to add where clause to select data from the result of stored procedure?
For example, the result of the stored procedure may return 10000 records but for my project, I only need to select "where city='London'".
How to complete it?

Can you create an new procedure or have one created for you?
😎
Another option would be to build the query in your code.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If you're stuck with the stored procedure as it is, you can insert the results into a temp table and select from that.

John

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

No, this stored procedure is coming a product purchased by company.

Thom A SSC Guru Points: 99133 More actions · Answer 4

Are you returning the dataset into a datatable? If so, you can filter these. In VB, I think the syntax would be something along the lines of:
Dim sqlConn As New SqlConnection(connString) Dim sqlComm As New SqlCommand("dbo.your_SP", sqlConn) sqlComm.CommandType = CommandType.StoredProcedure

'Add a random parameter, in case you have those '(extra single quote cause of the silly code interpreter on SSC :( ) sqlComm.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.Int)) sqlComm.Parameters("@CompanyID").Value = 1

Try sqlConn.Open()

Dim rs As SqlDataReader = sqlComm.ExecuteReader Dim dt As DataTable = New DataTable

dt.Load(rs)

Dim dr() As DataRow 'This is your filter ' dr = dt.Select("Location='London'") dgvSales.DataSource = dr

Catch Ex as Exception Messagebox.Show (Ex.Message) Finally sqlConn.Close() End Try
Disclaimer: This is untested, and i am working from memory. This code will likely need some kind of amendment. If your application is in C# you will need to translate, I can't write the language (sorry).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

adonetok - Tuesday, July 18, 2017 6:35 AM
No, this stored procedure is coming a product purchased by company.

Can you copy it to make a new procedure?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2