--获取所有数据 根据自定义函数传人类型id返回类型名称
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 ,dbo.myfun1(TypeId) as typename from dbo.ClothColors;
END
这些储存过程都是修改的，吧ALTER改为create 就可以在数据库中创建
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
--自定义函数
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
//既有传人参数，又有返回参数
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
--储存过程
ALTER PROCEDURE [dbo].[Proc_all]
(
@name varchar(50), --默认传人
@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#实现传人参数和返回参数
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 = ParameterDirection.Output; //这个必须写，不写不会返回要传出的参数
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds=new DataSet ();
sda.Fill(ds);
转播
