How to call stored procedures (with two parameters) in a stored procedure?

Question

I have stored procedures with the same parameters (server name and date). I want to write a stored procedure and Exec them in that SP (called SP_All).

CREATE PROCEDURE [dbo].[SP_All] AS BEGIN exec sp_1 @myDate datetime, @ServerName sysname exec sp_2 @myDate datetime, @ServerName sysname exec sp_3 @myDate datetime, @ServerName sysname exec sp_4 @myDate datetime, @ServerName sysname END Go 

error: Must declare the scalar variable "@myDate".

Answer 1

Here I see two questions:

• Your procedure apparently takes two parameters @myDate and @ServerName , which you have not yet declared. Do this by adding names and types between the procedure name and AS.

• When calling sp_1 in sp_4, there is no need to specify the data type of the parameters again (which was considered in the declaration, see point 1).

   CREATE PROCEDURE [dbo].[SP_All] @myDate datetime, @ServerName sysname AS BEGIN exec sp_1 @myDate, @ServerName exec sp_2 @myDate, @ServerName exec sp_3 @myDate, @ServerName exec sp_4 @myDate, @ServerName END 

Answer 2

Try this option -

 CREATE PROCEDURE [dbo].[SP_All] @myDate DATETIME , @ServerName SYSNAME AS BEGIN EXEC dbo.sp_1 @myDate, @ServerName EXEC dbo.sp_2 @myDate, @ServerName EXEC dbo.sp_3 @myDate, @ServerName EXEC dbo.sp_4 @myDate, @ServerName END 

Answer 3

You are executing stored procedures in the wrong way.

 exec sp_1 @myDate datetime, @ServerName sysname 

- completely wrong syntax.

When you need to execute a stored procedure with parameters, first declare the parameter and pass it.

 declare @myDate datetime declare @ServerName sysname exec sp_1 @myDate, @ServerName 

This is the right approach.