select table to insert into temp table based on condition TSQL-CodePudding

Pardon my query, I am new to T-SQL.

I am trying to choose a table based on a condition as shown below:

CREATE PROCEDURE dbo.FINDPEOPLE

DECLARE
 @GID varchar (50)
,@Status varchar (50)
,@PrintDate date

AS
BEGIN
SET NOCOUNT ON;


DECLARE @NEWPOST int = (SELECT TID FROM dbo.[GROUP] WHERE ID = @GIDS)

SELECT A.ID (...) into #A1
FROM
(CASE  WHEN @NEWPOST = 1
 THEN
 SELECT ID, (...) FROM dbo.get_NEW_PEOPLE (@GIDS, @PrintDate,@Status)
 ELSE
 SELECT ID, (...) FROM dbo.get_OLD_PEOPLE (@GIDS, @PrintDate,@Status) 
END) A
LEFT JOIN USERS B
ON A.ID = B.ID
END

The dbo.get is an inline TVF that works great on it's own.

I get an error message:

Incorrect syntax near the keyword 'CASE'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near ')'.

I have googled a lot and can't find the same question. Please tell me what's wrong with my code. All suggestions are appreciated.

Thanks in advance!

Edit: I am planning to left join it with a new table.

CodePudding user response：

CASE is an expression, not a logical flow operator; it returns a scalar value. You are confusing it with a Case (Switch) statement, which T-SQL does not support. Even if it did, you cannot use logical flow operations within the middle of a statement (such as a SELECT).

I would suggest using an IF. Also note the abundance of comments I add addressing multiple other flaws you have:

CREATE PROCEDURE dbo.FINDPEOPLE --Don't use the sp_ prefix.
                                --You don't have a DECLARE in the procedured definition
                                @GID varchar(50), --I doubt you need 8001-2billion characters, use a meaningful length
                                @Status varchar (50) --I doubt you need 8001-2billion characters, use a meaningful length
                                @PrintDate date

AS
BEGIN
    SET NOCOUNT ON;

    --I doubt you need 8001-2billion characters, use a meaningful length
    --Also note my later comment. Is varchar even the right data type?
    DECLARE @Newpost varchar(50) = (SELECT TID FROM dbo.[GROUP] WHERE ID = @GID); --GROUP is a reserved keyword; don't use these for object names                                                                          --I suggest renaming your object to something better.
    IF @NEWPOST = 1 --If you are checking if @NEWPOST is 1, why was it a varchar(MAX)? Why not an int?
        SELECT {YourColumns} --List your columns here explicitly; don't use *
        --into #A1 --You don't do anything with this table afterwards, so it would then be disposed of; the INTO seems pointless
        FROM dbo.get_NEW_PEOPLE (@GID, @PrintDate,@Status);
    ELSE
        SELECT {YourColumns} --List your columns here explicitly; don't use *
        --into #A1 --See prior SELECT's comment
        FROM dbo.get_OLD_PEOPLE (@GID, @PrintDate,@Status);
END;

CodePudding user response：

Your original stored procedure code has full of errors. Besides the errors, you are saying you would left join the result. That is not something you can do with an SP (doing that inside the SP doesn't seem to feasible but could be). A local temporary table created within an SP, gets out of scope at the of an SP. It looks like what you really need is a Table Valued Function. ie:

CREATE FUNCTION udf_FINDPEOPLE(@GID VARCHAR(50), @Status VARCHAR(50), @PrintDate DATE)
RETURNS @results TABLE
  (
    -- columns definition
    id INT,
    -- ...
  )
AS
  BEGIN

    DECLARE @NEWPOST INT;
    SELECT @NEWPOST=TID FROM dbo.[GROUP] WHERE ID=@GIDS;
    IF @NEWPOST=1
      INSERT @results(id)
      SELECT id, ... FROM dbo.get_NEW_PEOPLE(@GIDS, @PrintDate, @Status);
    ELSE
      INSERT @results(id)
      SELECT id, ... FROM dbo.get_OLD_PEOPLE(@GIDS, @PrintDate, @Status);
    RETURN;
  END;

and you could easily left join it, ie:

SELECT fp.id, ...
FROM   udf_FINDPEOPLE('GID', 'Status', '20220101') fp
       LEFT JOIN USERS                             B ON fp.ID=B.ID;