CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
None
None

Reputation: 5670

Executing one stored-procedure from another throws error

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

Upvotes: 0

Views: 116

Answers (2)

JamieA
JamieA

Reputation: 2013

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)

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76508

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

Upvotes: 1

Related Questions