Passing parameter variable in exec() function in procedure

Question

I am trying to use the parameter variable exec() in my procedure.

Here is the procedure:

CREATE PROCEDURE [dbo].[sp_CostBudgetedTabular](@start  AS datetime)
AS
BEGIN
    Exec('Declare @tempActual Table(MonthName nvarchar(MAX),Total float);
    Declare @tempBudgeted Table(MonthName nvarchar(MAX),Total float);

    insert into @tempBudgeted sp_CostBudgetedTabular @start
    insert into @tempActual sp_CostActualTabular @start  ')

It is throwing errors:

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'sp_CostBudgetedTabular'.

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'sp_CostActualTabular'.

sp_CostBudgetedTabular and sp_CostActualTabular are procedures which take a date as parameter

Side note: CREATE PROCEDURE: "Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures" — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 20, 2016 at 7:58
Also, why are you doing an EXEC on an entirely static string? Why not just have the code directly in this procedure? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 20, 2016 at 7:59
@Damien_The_Unbeliever Thank you for the advise , I will keep it in mind while creating procedures next time. Can you please help me with the issue i am having currently. — Shahzad Ahamad
– Shahzad Ahamad, Commented Jan 20, 2016 at 8:00

Devart · Accepted Answer · 2016-01-20 08:00:29Z

1

CREATE PROCEDURE [dbo].[sp_CostBudgetedTabular]
(
    @start DATETIME
)
AS BEGIN

    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '
    DECLARE @tempActual TABLE ([MonthName] NVARCHAR(100), Total FLOAT)
    DECLARE @tempBudgeted TABLE ([MonthName] NVARCHAR(100), Total FLOAT)

    INSERT INTO @tempBudgeted
    EXEC dbo.sp_CostBudgetedTabular @start

    INSERT INTO @tempActual
    EXEC dbo.sp_CostActualTabular @start  '

    EXEC sys.sp_executesql @SQL, N'@start DATETIME', @start = @start

END

answered Jan 20, 2016 at 8:00

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing parameter variable in exec() function in procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related