CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Dhwani
Dhwani

Reputation: 7626

Calling a SP from another SP via EXECUTE SP_EXECUTESQL

I am using EXECUTE SP_EXECUTESQL for calling a stored procedure from another stored procedure but I am getting an error. I haven't tried it before so I don't know what is wrong.

Here SPGetServiceState is SP and @Id(IN), @Return_State(OUT), @Return_Execute_Date(OUT) are parameters needed to pass and @Request_Id has id needed to pass.

EXECUTE SP_EXECUTESQL N'SPGetServiceState', N'@Id int,
  @Return_State tinyint, @Return_Execute_Date smalldatetime', 
  @Id = @Request_Id, @Return_State = 0, @Return_Execute_Date = NULL

Upvotes: 0

Views: 1732

Answers (1)

Andomar
Andomar

Reputation: 238176

Unless you retrieve the name of the stored procedure from a variable, there is no need for dynamic SQL. You can just:

exec dbo.SPGetServiceState(@Id, @Return_State output, @Return_Execute_Date output);

Don't forget the output specifier. Without it, the parameter is treated as an input parameter.

Upvotes: 1

Related Questions