KababChi KababChi - 1 year ago 106
SQL Question

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'

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 ?


Answer Source

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.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download