如何使用Case语句在SQL中传递多个查询 [英] how to pass mutiple queries in SQL using Case statement
本文介绍了如何使用Case语句在SQL中传递多个查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在case语句中使用不同的查询我在执行后尝试这样做必须返回ID
how to use different queries in case statement i am trying like this after execution must return ID
DECLARE @Type int
SET @Type = 1
SELECT CASE(@Type) WHEN 1 THEN (set @ID=select MechanicID from MechanicRegistration)
WHEN 2 THEN (set @ID=select ShopkeeperID from ShopKeeperRegistration)
WHEN 3 THEN (set @ID=select DistributorID from DistributorRegistration) end
推荐答案
DECLARE @Type int
SET @Type = 1
SELECT CASE(@Type)
WHEN 1 THEN (select top 1 MechanicID from MechanicRegistration)
WHEN 2 THEN (select top 1 ShopkeeperID from ShopKeeperRegistration)
WHEN 3 THEN (select top 1 DistributorID from DistributorRegistration)
end
你可以试试这个
You can try this
DECLARE @ID int
DECLARE @Type int
DECLARE @ParmDefinition nvarchar(500);
DECLARE @qry nvarchar(max)
SET @Type = 1
SET @qry = (
SELECT CASE(@Type) WHEN 1 THEN 'select top 1 @ID_OUT = MechanicID from MechanicRegistration'
WHEN 2 THEN 'select top 1 @ID_OUT = ShopkeeperID from ShopKeeperRegistration'
WHEN 3 THEN 'select top 1 @ID_OUT = DistributorID from DistributorRegistration' end)
SET @ParmDefinition = N'@ID_OUT int OUTPUT'
EXECUTE sp_executesql @qry,@ParmDefinition,@ID_OUT=@ID OUTPUT;
SELECT @ID
它在SQL Server 2012中进行了测试。
It is tested in SQL Server 2012.
如果我没错,你想获得ID
来自相应的表,取决于作为参数传递的值。
我建议阅读有关动态查询的内容:在存储过程中构建动态SQL [ ^ ]
If i'm not wrong, you want to getID
from corresponding table depending on value passed as an argument.
I would suggest to read about dynamic queries: Building Dynamic SQL In a Stored Procedure[^]
这篇关于如何使用Case语句在SQL中传递多个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文