在数据库操作中,对存储过程的删除、查询、更新、调用等操作是我们经常用到的,为了方便初学者学习,本文我们给出了存储过程的删除、查询、更新以及它们的调用过程的代码示例,希望能够对您有所帮助。接下来就让我们一起来了解一下这部分内容。
1:删除
- CREATE PROCEDURE Sp_deletedatabyCondition
- @tablename nvarchar(100),
- @condition nvarchar(200)
- AS
- BEGIN
- DECLARE @Sql nvarchar(500)
- SET @Sql='delete from '+@tablename+ ' where '+@condition
- EXEC(@Sql)
- END
- GO
2:查询
- CREATE PROCEDURE SP_getColumnsByCondition
- @tablename nvarchar(100),
- @columns nvarchar(300),
- @condition nvarchar(200)
- AS
- BEGIN
- DECLARE @sql nvarchar(1000)
- SET @sql='select '+@columns+' from '+@tablename+ ' where 11=1 '+@condition
- EXEC(@sql)
- END
- GO
3:更新
- CREATE PROCEDURE Sp_UpdateTablebyCondition
- @tablename nvarchar(100),
- @condition nvarchar(300),
- @columns nvarchar(500)
- AS
- BEGIN
- DECLARE @sql nvarchar(1000)
- SET @sql='update '+@tablename+' set '+@columns+' where '
- +@condition
- -- PRINT @sql
- EXEC(@sql)
- END
- GO
对以上操作的调用:
- string tablename = "news",where="id=21";
- SqlParameter[] para = new SqlParameter[] {
- new SqlParameter("@tablename",tablename),
- new SqlParameter("@condition",where)
- };
- int result = DB.ExecuteProcCommand("Sp_deletedatabyCondition", para);
- if (result > 0)
- {
- Response.Write("删除成功");
- }
- DB
- public static int ExecuteProcCommand(string proc,params SqlParameter[] values)
- {
- SqlConnection myconn = getcon();
- SqlCommand cmd = new SqlCommand(proc, myconn);
- cmd.Parameters.AddRange(values);
- cmd.CommandType = CommandType.StoredProcedure;
- int result = -1;
- try
- {
- result = cmd.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- myconn.Close();
- myconn.Dispose();
- }
- return result;
- }
关于存储过程的查询、删除、更新和调用的知识就介绍到这里了,希望本次的介绍能够对您有所收获!
【编辑推荐】