CODEWITHSUNDEEP
sqlsql-servert-sql
mhd.cs
mhd.cs

Reputation: 721

How to Run Two T-Sql Statement By Exec Command in SqlServer?

I want run 2 SQL queries in Exec instruction, how can I do this?

ListOfLeaveRemainingInfo is a user-defined table type:

ALTER PROCEDURE [dbo].[SetLeaveRemainingTempTable]
    (@LeaveRemainingTempTable [dbo].ListOfLeaveRemainingInfo READONLY,
     @TempTableName NVARCHAR(420)
    )
AS
    EXEC('CREATE TABLE ' + @TempTableName + '
                     ( PersonnelId int, 
                     PersonnelNo nvarchar(512), 
                     FirstName nvarchar(512), 
                     LastName nvarchar(512), 
                     WorkSectionTitle nvarchar(512), 
                     StaffTitle nvarchar(512)
                     );

INSERT INTO ' +@TempTableName + ' 

SELECT * FROM  @LeaveRemainingTempTable 
'
)

When I run the stored procedure, I get this error:

Msg 1087, Level 15, State 2, Line 33
Must declare the table variable "@LeaveRemainingTempTable"

Upvotes: 2

Views: 155

Answers (1)

iainc
iainc

Reputation: 868

Scratch the previous.

@LeaveRemainingTempTable is a table variable. It therefore doesn't exist within the scope of the dynamic SQL.

If dbo.listofleaveremaininginfo always has the same columns you can pass it in as a table variable:-

alter PROCEDURE [dbo].[SetLeaveRemainingTempTable]
(
@LeaveRemainingTempTable     [dbo].ListOfLeaveRemainingInfo     READONLY,
@TempTableName NVARCHAR(512)
)
AS

Declare @mysql varchar(max), @params varchar(255)

Set @mysql='CREATE TABLE ' + @TempTableName + '
                     ( PersonnelId int, 
                     PersonnelNo nvarchar(512), 
                     FirstName nvarchar(512), 
                     LastName nvarchar(512), 
                     WorkSectionTitle nvarchar(512), 
                     StaffTitle nvarchar(512)
                     );

INSERT INTO ' +@TempTableName + ' 

SELECT * FROM  @LeaveRemainingTempTable_IN'

if exists (select * from sys.types where name = 'LeaveReaminingTempTable')
    drop type  LeaveReaminingTempTable

create type LeaveReaminingTempTable as table (id int)

declare @t  LeaveReaminingTempTable
insert @t 
select * from @LeaveRemainingTempTable

exec sp_executesql @mysql, N'@LeaveRemainingTempTable_IN  LeaveReaminingTempTable readonly', @t

Upvotes: 3

Related Questions