--获取所有数据 根据自定义函数传人类型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);