Stored Procedure

Stored Procedure ( Management Tool)

1.View> Template Explorer> Stored procedure> Create Procedure basic template
2.Place cursor on query editor.
3.Query > Specify Value for Template Param.
4.Fill up all param , GETDATE() is current datetime.
5.Design your SP by using Query Designer ( Add table> RC> Choose select, update, delete , alter)

SP Format

Create Proc SP_Name ( @param1 int , @param2 nchar)

@param1 datatype , /* declare parameters */
@param2 datatype ,

WITH Recompile , Encryption // prevent user from running sp_helptext, view your code

AS
BEGIN

Declare var1 char /* declare variable*/

Set var1='hello world' /* initialize */

SQL Statement
Insert, Select ,Update , Delete

ELSE

Statement......

END

Show SP content
sp_helptext customerinfo

Calling SP from aspx
1. GV> SqlDataSource > custom Statement& SP > Stored Procedure > your SP ( SelectcmdType="StoredProcedure" )

SqlConnection conn = new SqlConnection( " ConnectionString");
Sqlcmd cmd = new Sqlcmd("GetProducts", conn);
cmd.comamdType = commandType.StoredProcedure;
cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = 1;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products"); //ds.table[0| Product].DefaultyView
dg.DataSource = ds;
dg.DataMember = "Products"