存储过程小结

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
1.联合查询
CREATE PROCEDURE GetalltbProduct 
AS
BEGIN
SELECT a.ID,a.ProName,a.ProPrice,a.ProAddress,a.ProType,b.TypeName  FROM dbo.tbProduct AS a INNER JOIN tbProType AS b ON a.ProType=b.ID
END
GO

2.创建函数
CREATE FUNCTION FN_getTypeNamebyTypeID 
(
@typid int
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @TypeName nvarchar(50)
SELECT @TypeName = TypeName FROM tbProType WHERE ID=@typid
RETURN @TypeName
3.使用函数查询产品详细信息
CREATE PROCEDURE getProInfo 
AS
BEGIN
SELECT
ID,
ProNo,
ProName,
ProPrice,
ProAddress,
ProType,
ProName=dbo.FN_getTypeNamebyTypeID(ProType)
FROM
tbProduct
END
GO
4.通用删除
alter 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
5.通用的修改功能
alter 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
6.根据指定列，指定表，指定的条件筛选数据
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 1=1 '+@condition
EXEC(@sql)
END
GO
7.查询指定表，指定列的数据
CREATE PROCEDURE SP_getColumnsbyTable 
@tablename nvarchar(100),
@columns nvarchar(500)
AS
BEGIN
DECLARE @sql nvarchar(1000)
SET @sql='select '+@columns+' from '+ @tablename
EXEC(@sql)
END
GO
8.添加一条用户信息返回最大的ID值
create PROCEDURE addtbUser 
@username nvarchar(50),
@pwd nvarchar(50),
@sex bit,
@address nvarchar(50),
@id int output
AS
BEGIN
INSERT INTO dbo.tbUser
VALUES(
@username,
@pwd,
@sex,
@address
)
SELECT @id=max(ID) FROM dbo.tbUser
END
GO
9.根据产品类型ID查询产品类型名称
CREATE FUNCTION FN_getTypeNamebyTypeID 
(
@typid int
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @TypeName nvarchar(50)
SELECT @TypeName = TypeName FROM tbProType WHERE ID=@typid
RETURN @TypeName
END
GO
10.通过添加数据自动生成流水账号
CREATE TRIGGER CreateProNOByID
   ON   dbo.tbProduct
   AFTER  INSERT
AS 
BEGIN
  DECLARE @year nvarchar(10)
  DECLARE @month nvarchar(10)
  DECLARE @Day nvarchar(10)
  DECLARE @str nvarchar(10)
  DECLARE @ProNo nvarchar(50)
  DECLARE @ProID nvarchar(50)
  set @year=  Datename(yyyy,getdate())
  set @month= Datename(mm,getdate())
  set @Day=  Datename(dd,getdate())
  SET @str='000000'
  SELECT @ProID=MAX(ID) FROM tbProduct
  SET @ProNo=@year+@month+@Day+RIGHT(@str+@ProID,6)
--  EXEC dbo.Sp_getDataByTableName 'tbProduct',' id='+@ProID,'ProNo='+@ProNo
  UPDATE tbProduct  SET ProNo = @ProNo  WHERE ID=@ProID
END
GO
11.根据条件查询产品
ALTER FUNCTION [dbo].[FN_getProductbyCondition] 
(
-- Add the parameters for the function here
@typeid int
)
RETURNS TABLE 
AS
RETURN 
(
 -- Add the SELECT statement with parameter references here
SELECT ID, ProNo,ProName,ProPrice,ProAddress, ProTypeFROM tbProduct  WHERE ProType=@typeid
)
12.程序中应用
 private void Form1_Load(object sender, EventArgs e)
        {
            int typeid=1;
            SqlParameter[] pars = new SqlParameter[]{
                                                                                          new SqlParameter("@typeid",typeid)
                                                                                         };
           this.dataGridView1.DataSource=  SqlHelper.SelectSqlReturnTable("getProInfoByTypeid", CommandType.StoredProcedure, pars);
        }
 /// 执行一个SQL语句或则存储过程实现查询
 /// <param name="sql">SQL语句或则存储过程</param>
/// <param name="type">指定命令类型</param>
/// <param name="pars">所需参数</param>
/// <returns>DataTable</returns>
public static DataTable SelectSqlReturnTable(string sql, CommandType type, SqlParameter[] pars)
  {
          SqlConnection con = new SqlConnection(ConnString);
          SqlDataAdapter sda = new SqlDataAdapter(sql, con);
          sda.SelectCommand.CommandType = type;
          if (pars != null && pars.Length > 0)
          {
              foreach (SqlParameter p in pars)
              {
                  sda.SelectCommand.Parameters.Add(p);
              }
          }
          DataTable dt = new DataTable();
          sda.Fill(dt);
          return dt;
  }