I'm very new to Stored Procedures.
Say I have a IDCategory (int) and I pass that to a stored procedure. In normal-talk, it'd go:
Find me all the listings with an IDCategory equal to the IDCategory I'm telling you to find.
So it would find say 3 listing, and create a table with columns:
IDListing, IDCategory, Price, Seller, Image.
How could I achieve this?
The stored procedure accepts input parameters and also stored procedures return values after the execution.
In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.
Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
To execute a stored procedure with an output parameter we first need to declare variables to store the output values. storeParameter2Value variable is declared to store the value of Parameter2 that will be output from our stored procedure. Now in the EXEC condition, we list all our input parameters as usual.
To fill a dataset from a stored procedure you would have code like below:
SqlConnection mySqlConnection =new SqlConnection("server=(local);database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "IDCategory";
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add("@IDCategory", SqlDbType.Int).Value = 5;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet);
Your connection string will be different and there are a few different ways to do this but this should get you going.... Once you get a few of these under your belt take a look at the Using Statement. It helps clean up the resources and requires a few less lines of code. This assumes a Stored Procedure name IDCategory with one Parameter called the same. It may be a little different in your setup.
Your stored procedure in this case will look something like:
CREATE PROC [dbo].[IDCategory]
@IDCategory int
AS
SELECT IDListing, IDCategory, Price, Seller, Image
FROM whateveryourtableisnamed
WHERE IDCategory = @IDCategory
Here's a link on Stored Procedure basics: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx
Here's a link on DataSets and other items with ADO.Net: http://authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx
Have a table in your database that contains those 5 fields you wish and query it.
Example:
Select IDListing, IDCategory, Price, Seller, Image
From [listingtable] --whatever your table is called
where IDCategoryID = @IDCategoryID
Entire stored procedure:
CREATE PROCEDURE sp_Listing_Get
@IDCategory int
AS
DECLARE @categoryid
SET @categoryid = @IDCategory
BEGIN
SELECT t.idlisting,
t.idcategory,
t.price,
t.seller,
t.image
FROM [databaseName].dbo.LISTING t
WHERE t.idcategoryid = @categoryid
END
Replace [databaseName] with the name of your database. The benefit to using the two period format is that the sproc will return results as long as the user who is executing the sproc has access to the table (and database).
The @categoryid is used to deal with SQL Servers parameter sniffing issue.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With