Stored Procedure select Query In SQL server

There are lots of situation, Where we have to use Store Procedure in our project. But beginners don’t get proper code for  Select Query For Store Procedure. Using this code they can do it:

In Class File give the query same as given below

public static DataTable SelectItemDetailsSearch(string str1 string str2)
        {
                SqlParameter[] sqlParams = new SqlParameter[2];

                sqlParams[0] = new SqlParameter("@Category", SqlDbType.VarChar, 50);
                sqlParams[0].Value = str1 ;

                sqlParams[1] = new SqlParameter("@ItemType", SqlDbType.VarChar, 150);
                sqlParams[1].Value = str2;

                DAL.Sql ocp = new DAL.Sql();
                return ocp.GetDetails_DT_SP("ViewItem_Search", sqlParams);
        }


Then give the query in sql server


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[ViewItem_Search]
(
@Category varchar(50),
@ItemType varchar(150)
)

AS

Begin
if(@Category != 'All'and @ItemType!='All' )

SELECT  a.Item_Id AS Code, d.CategoryName as Category,a.ItemName, a.ItemDefaultPrice AS Price,
                      CONVERT(varchar, a.LastUpdateDate,
                      103) + ' ' + CONVERT(varchar, a.LastUpdateDate, 108) AS LastUpdateDate
FROM     ItemDetails AS a INNER JOIN
                      ItemCategory AS d ON a.ItemCategory_Id = d.ItemCategory_Id
WHERE      (d.CategoryName = @Category and a.ItemType=@ItemType)
ORDER BY d.CategoryName,a.ItemCode
END

Then Select All The Sql Query Then Run or Press F5

Command(s) completed successfully.
Message Display In Your SQL server page...
Then Run your project ..