2

Using SQL Server 2008.

Wanted to output a parameter (@countcase) from a stored procedure called sp256. But when I exec the stored procedure, an error shows up:

Procedure or function 'sp256' expects parameter '@countcase', which was not supplied.

The query was to count the total number cases with contact date later than 1 Nov 2016 and output as a parameter.

The stored procedure:

CREATE PROC sp256
     @countcase AS INT OUTPUT
AS 
BEGIN
    SELECT @countcase = COUNT(DISTINCT case_referenceid)
    FROM Cases
    WHERE case_contactdatetime > '2016-11-01'

    --RETURN
END

The EXEC statement:

  DECLARE @reference INT

  EXEC @reference = sp256

  SELECT @reference AS [List of cases]

PS: the @countcase should output 268 cases in total.

Can someone please help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ritak
  • 23
  • 2

1 Answers1

1

Your proc structure sets an output parameter, doesn't return a value. So that you need to provide an output parameter as expected.

EXEC dbo.sp256 @reference output
aozogul
  • 571
  • 5
  • 8