T-SQL表值函数中的多个案例

I need to create user defined function, that returns all values from a particular product from stock. It should be like this:

我需要创建用户定义的函数,它返回来自库存的特定产品的所有值。它应该是这样的:

 Select * from Task10('PC',100,500) 

Also all data are in different tables, and i don't know how to do this without IF statement. Another difficulty that user defined functions that returns TABLE need to start with Select. This code segment that doesn't work for me.

USE MyDataBase
GO
if OBJECT_ID ('Task10','FN') IS NOT NULL DROP FUNCTION Task10;
GO
Create function Task10 (@type_product VARCHAR(10),@min_price FLOAT, @max_price FLOAT)
RETURNS TABLE
AS RETURN(
Select *, case @type_product
when 'PC' then (products.Model = pc_uri.Model AND products.type='PC' AND pc_uri.value>@min_price and pc_uri.value<@max_price)
when 'Laptops' then (products.Model=laptop_uri.Model AND products.type='Laptops' AND laptop_uri.value>@min_price and laptop_uri.value<@max_price)
when 'Printers' then (products.Model=Printers.Model AND products.type='Printers' AND Printers.value>@min_price and Printers.value<@max_price)
from case @type_product
when 'PC' pc_uri,products
when 'Laptops' laptop_uri,products
when 'Printers' Printers,products
)

This one works, but doesn't return all attributes, just two.

USE MyDataBase
GO
if OBJECT_ID ('Task10','FN') IS NOT NULL DROP FUNCTION Task10;
GO
Create function Task10 (@type_product VARCHAR(10),@min_price FLOAT, @max_price FLOAT)
RETURNS TABLE
AS RETURN(
Select distinct product.Model,product.Type from product,laptops,printers,pc_uri
      where (
         (@type_product = 'PC' AND product.Model=pc_uri.Model AND product.Type='PC' AND pc_uri.value>@min_price and pc_uri.value<@max_price)
      OR (@type_product = 'printers' AND product.Model=printers.Model AND product.Type='printers' AND printers.value>@min_price and printers.value<@max_price)
      OR (@type_product = 'Laptop-uri' AND product.Model=laptops.Model AND product.Type='Laptop-uri' AND laptops.pret>@min_price and laptops.value<@max_price)
      )
)

1 个解决方案

#1

Try this.. using left join where you get to match everything but let the type of product decide where to get the value.

尝试这个..使用左连接,你可以匹配所有东西,但让产品的类型决定在哪里获得价值。

Create function Task10 (@type_product VARCHAR(10),@min_price FLOAT, @max_price FLOAT)
    RETURNS TABLE
    AS RETURN(
    Select products.*, case @type_product
    when 'PC' then pc_uri.value
    when 'Laptops' then laptop_uri.value
    when 'Printers' then Printers.value
      else 0 end price
    from products
      left join pc_uri on pc_uri.Model = products.Model and (pc_uri.value>@min_price and pc_uri.value<@max_price)
      left join laptop_uri on laptop_uri.Model = products.Model AND (laptop_uri.value>@min_price and laptop_uri.value<@max_price)
      left join Printers on Printers.Model = products.Model and (Printers.value>@min_price and Printers.value<@max_price)