首页 > 代码库 > sql 储存过程的使用

sql 储存过程的使用

2024-07-05 19:00:57   222人阅读

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-<span style="background-color: rgb(255, 255, 0);">-获取所有数据 根据自定义函数传人类型id返回类型名称</span>
USE [Cloths]
GO
/****** Object:  StoredProcedure [dbo].[Proc_all]    Script Date: 05/23/2014 12:10:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE   [dbo].[Proc_all]
AS
BEGIN
    SET NOCOUNT ON;
   select ClothColorId ,Name ,<span style="background-color: rgb(255, 0, 0);">dbo.myfun1(TypeId)</span> as typename from dbo.ClothColors;
END

　　这些储存过程都是修改的，吧ALTER改为create 就可以在数据库中创建 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE [Cloths]
GO
/****** Object:  UserDefinedFunction [dbo].[myfun1]    Script Date: 05/23/2014 13:53:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
<span style="background-color: rgb(255, 255, 0);"> --自定义函数</span>
ALTER FUNCTION  [dbo].[myfun1]
(  
 @a varchar(50)
      
)
 returns varchar(50)
as
begin
 declare @name varchar(50);
 select @name=[types].name from [types] where typeid=@a;
 return @name;
end

　　//既有传人参数，又有返回参数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE [Cloths]
GO
/****** Object:  StoredProcedure [dbo].[Proc_all]    Script Date: 05/23/2014 13:56:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO<br>--<span style="background-color: rgb(255, 255, 0);">储存过程</span>
ALTER PROCEDURE   [dbo].[Proc_all]
(
 @name varchar(50), --<span style="background-color: rgb(255, 255, 0);">默认传人</span>
 @count int output
)
AS
BEGIN
    SET NOCOUNT ON;
   select ClothColorId ,Name ,dbo.myfun1(TypeId) as typename from dbo.ClothColors where Name=@name;
   select @count=COUNT(1) from dbo.ClothColors;
   
END

　　//是c#实现传人参数和返回参数

?
1
2
3
4
5
6
7
8
9
10
SqlCommand comm = new SqlCommand();
          comm.Connection = new SqlConnection("Data Source=.;Initial Catalog=Cloths;Integrated Security=True");
          comm.CommandType = CommandType.StoredProcedure;
          comm.CommandText = "Proc_all";
          comm.Parameters.Add(new SqlParameter("@name", "大红"));//传人参数
          comm.Parameters.Add(new SqlParameter("@count",DbType.Int32));
          comm.Parameters["@count"].Direction = <span style="background-color: rgb(255, 255, 0);">ParameterDirection.Output; //这个必须写，不写不会返回要传出的参数</span>
          SqlDataAdapter sda = new SqlDataAdapter(comm);
          DataSet ds=new DataSet ();
          sda.Fill(ds);

　　


联系
我们