存储过程——存储过程与函数（四）

-- =============================================
-- Author:        <Author,,Name>
-- Create date: 2014-04-11 21:10:42
-- Description:    根据产品类型查询产品
-- =============================================
CREATE FUNCTION [dbo].[FN_getproductbyCondition]
(    
    -- Add the parameters for the function here
    @condition nvarchar(200)
)
RETURNS TABLE 
AS
RETURN 
(
    select * from product where Type=@condition
)

select * FROM FN_getproductbyCondition(2)
--实际上等同于
select * from [dbo].[product] where Type=2

CREATE FUNCTION FN_getproducttypebyId
(
    -- Add the parameters for the function here
    @typeid int
)
RETURNS nvarchar(50)
AS
BEGIN
    -- Declare the return variable here
    --返回值
    DECLARE @typeName nvarchar(50)

    -- Add the T-SQL statements to compute the return value here
    --执行sql语句
    SELECT @typeName=Name from [dbo].[productType] where id=@typeid

    -- Return the result of the function
    --返回结果
    RETURN @typeName

END
GO

--测试标量函数
SELECT [ID]
      ,[Name]
      ,[Price]
      ,[Address]
      ,[Type]=dbo.FN_getproducttypebyId(Type)
  FROM [wisdominfo].[dbo].[product]

CREATE PROCEDURE GetProinfo 
AS
BEGIN
    SELECT [ID]
      ,[Name]
      ,[Price]
      ,[Address]
      ,[Type]
      ,TypeName=dbo.FN_getproducttypebyId(Type)
  FROM [wisdominfo].[dbo].[product]
END
GO