根据SQL Server中的行值调用特定的存储过程/选择语句。

In SQL Server, is there a way to somehow call a specific stored procedure/inline select statement based on the value of a column in a table?

在SQL Server中，是否有一种方法可以根据表中列的值调用特定的存储过程/内联select语句?

E.g. for each row in table #IdNumbers (below), check the value of the Number column - if the value is 1 then call the first stored procedure, if the value is 2 then call the second procedure, and so on.

对于表# idnumber(下面)中的每一行，检查Number列的值——如果值为1，则调用第一个存储过程，如果值为2，则调用第二个过程，以此类推。

I'm trying to achieve something similar to the code below (but without the pseudo-code).

我正在尝试实现与下面的代码类似的东西(但是没有伪代码)。

CREATE PROCEDURE [CallSpecificStoredProcedure]
(
 @Name VARCHAR(50)
,@StartDate DATETIME
,@EndDate DATETIME
)
AS
BEGIN
CREATE TABLE #IdNumbers
(   
    Id UNIQUEIDENTIFIER, 
    Number INT
)

INSERT INTO #IdNumbers 
exec GetIdNumbers @StartDate, @EndDate

--for each row in #IdNumbers
    --If Number = 1
    exec StoredProcedure1(Id, Number, @Name, @StartDate, @EndDate)

    --else If Number = 2
    exec StoredProcedure2(Id, Number, @Name, @StartDate, @EndDate)

    --else If Number = 3
    exec StoredProcedure3(Id, Number, @Name, @StartDate, @EndDate)
END

2 个解决方案

#1

You need cursors for this. This hopefully works:

为此需要游标。这个希望工程:

CREATE PROCEDURE [p_CallSpecificStoredProcedure]
(
  @Name VARCHAR(50),
  @StartDate DATETIME,
  @EndDate DATETIME
)
AS
BEGIN
CREATE TABLE #IdNumbers
(   
    Id UNIQUEIDENTIFIER, 
    Number INT
)

INSERT INTO #IdNumbers
exec GetIdNumbers @StartDate, @EndDate

DECLARE @Id UNIQUEIDENTIFIER
DECLARE @Number INT
DECLARE IdCursor CURSOR FAST_FORWARD FOR
SELECT Id, Number FROM #IdNumbers

OPEN IdCursor
FETCH NEXT FROM IdCursor
INTO @Id, @Number
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Number = 1
    EXEC StoredProcedure1 @Id, @Number, @Name, @StartDate, @EndDate
  ELSE IF @Number = 2
    EXEC StoredProcedure2 @Id, @Number, @Name, @StartDate, @EndDate
  ELSE IF @Number = 3
    EXEC StoredProcedure3 @Id, @Number, @Name, @StartDate, @EndDate

  FETCH NEXT FROM IdCursor
  INTO @Id, @Number
END
CLOSE IdCursor
DEALLOCATE IdCursor

END

#2

I gess you're searching for CURSOR.

我知道你在搜索光标。

CREATE PROCEDURE [CallSpecificStoredProcedure]

 @Name VARCHAR(50)
,@StartDate DATETIME
,@EndDate DATETIME

AS
BEGIN
CREATE TABLE #IdNumbers
(   
    Id UNIQUEIDENTIFIER, 
    Number INT
)

INSERT INTO #IdNumbers EXEC GetIdNumbers @StartDate, @EndDate  --Hope here you insert your numbers

DECLARE @Id UNIQUEIDENTIFIER
DECLARE @Number INT

DECLARE NUMBERS_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT Id, Number
FROM #IdNumbers

OPEN NUMBERS_CURSOR 
FETCH NEXT FROM NUMBERS_CURSOR INTO @Id, @Number 
WHILE @@FETCH_STATUS = 0 --If there is still rows
BEGIN 
    --here your logic
    IF @Number = 1
    BEGIN
     EXEC StoredProcedure1 @Id, @Number, @Name, @StartDate, @EndDate
    END
    IF @Number = 2
    BEGIN
     EXEC StoredProcedure2 @Id, @Number, @Name, @StartDate, @EndDate
    END

    FETCH NEXT FROM NUMBERS_CURSOR INTO @Id, @Number 
END
CLOSE NUMBERS_CURSOR 
DEALLOCATE NUMBERS_CURSOR

#1