CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Ali Hanifi
Ali Hanifi

Reputation: 418

ERROR : Using EXEC and Select in Same Procedure

i have the following statements for my procedure

    DECLARE @StockA table (GoodID int,NominalQty decimal(16,2),ActualQty decimal(16,2))

    INSERT INTO @StockA(GoodID,NominalQty,ActualQty)
    EXEC ('INV.[usp_GetStorageAvailability] ' + @SysYear +',"2015-01-01",1')

    UPDATE @ReqItems 
    SET Stock = S.ActualQty , Rem = (S.ActualQty - R.Qty) FROM @ReqItems R JOIN @StockA S on R.GoodID = S.GoodID

    DECLARE @HasMinus tinyint
    SET @HasMinus = (SELECT TOP 1 * FROM @ReqItems WHERE Rem < 0)

    IF @HasMinus > 0 begin
    DECLARE @GC nvarchar(10)
    SET @GC = (SELECT TOP 1 GoodCode FROM @ReqItems WHERE Rem < 0)
     SET @Outcome = 0
     SET @Descr = 'XZY'
    end


    SELECT @Outcome,@Descr

and i get the following error :

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

what should i do ?

thanks

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The problem is here:

SET @HasMinus = (SELECT TOP 1 * FROM @ReqItems WHERE Rem < 0)

I think the error is pretty clear. The * refers to multiple columns. You need to choose one:

SET @HasMinus = (SELECT TOP 1 ?? FROM @ReqItems WHERE Rem < 0);

I'm not sure which column you want to select, however.

If you just want a count, you can do:

SELECT @HasMinus = COUNT(*) FROM @ReqItems WHERE Rem < 0;

This might be the intention of your code. (Note that the SET isn't necessary.)

Upvotes: 1

Related Questions