Return values from function to be passed to the stored procedure

• parry

  SSC Journeyman

  Points: 91

  More actions

  July 29, 2015 at 2:24 pm

  #104277

  I have a afunction to check the reference number, also a stored procedure is written .

  The stored procedure passes the reference number to the function 'checkref' which returns a '1' or 'o' (BIT)

  I want this value passed to the stored procedure.but I'm having problems trying to capture the value returned from the function.

  This is the draft stored procedure :

  SET NOCOUNT ON

  DECLARE @found varchar(1)

  EXEC dbo.Check_ref (Ref, @found ) OUTPUT

  SET NOCOUNT OFF

  SELECT @found AS msg

  .....

  Many thanks if you can just.give me some help

  Thank you

  .

• WayneS

  SSC Guru

  Points: 95460

  More actions

  July 29, 2015 at 3:31 pm

  #1815954

  parry (7/29/2015)

  ---

  I have a afunction to check the reference number, also a stored procedure is written .

  The stored procedure passes the reference number to the function 'checkref' which returns a '1' or 'o' (BIT)

  I want this value passed to the stored procedure.but I'm having problems trying to capture the value returned from the function.

  This is the draft stored procedure :

  SET NOCOUNT ON

  DECLARE @found varchar(1)

  EXEC dbo.Check_ref (Ref, @found ) OUTPUT

  SET NOCOUNT OFF

  SELECT @found AS msg

  .....

  Many thanks if you can just.give me some help

  Thank you

  .

  You don't call a function with EXEC (that is for stored procedures).

  For a scalar function, you would call it like:

  DECLARE @found BIT;

  SET @found = Check_ref(whatever you pass to it);

  SELECT @found;

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2