如何使用Case语句在SQL中传递多个查询 [英] how to pass mutiple queries in SQL using Case statement

问题描述

如何在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 get ID 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屋！