0

My Stored procedure is like this

       SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create  PROCEDURE ph_GetAllStaffAddressByCamp
        @Year INT,
        @strYear VARCHAR(2) OUTPUT
    AS

    IF NULLIF(@Year, '') IS NULL
        SET @Year = Exec [cs_GetCurrentYear]  @strYear 
    SELECT DISTINCT [VolID], [CampID], [VolFName] FROM [vStaffJobAndCamp]
 where  CampCode Like @Year

As you can see I am trying to execute [cs_GetCurrentYear] inside this procedure (added the procedure below)

    ALTER PROCEDURE [dbo].[cs_GetCurrentYear]
    @strYear VARCHAR(2) OUTPUT
AS
    SELECT @strYear = Year FROM tblCurrentYear

But this throws an error on compile . And it looks like this

    Msg 156, Level 15, State 1, Procedure ph_GetAllStaffAddressByCamp, Line 8
Incorrect syntax near the keyword 'Exec'.
None
  • 5,582
  • 21
  • 85
  • 170
  • Have you checked this? http://stackoverflow.com/questions/170328/executing-a-stored-procedure-within-a-stored-procedure – SchmitzIT Aug 26 '14 at 06:38

2 Answers2

1

You can modify your second stored procedure to a stored function and call it from your first stored procedure. Read more here: How to call a scalar function in a stored procedure

Community
  • 1
  • 1
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • I can't do that. According to specific requirements in my project. Is there no way to call a storedprocedure here? – None Aug 26 '14 at 06:32
  • You might want to read this (http://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter) then – Lajos Arpad Aug 26 '14 at 07:16
1

You can create a table, and insert into it the result of your SP. Try

DECLARE  @TheYear TABLE
(

TheYear Varchar(2)

)

 INSERT INTO @theYear (TheYear)
  Exec [cs_GetCurrentYear]  @strYear 

   SET @Year = (SELECT TOP(1) TheYear FROM  @TheYear)
JamieA
  • 1,984
  • 4
  • 27
  • 38