Sql-server – sp_execute expects parameter ‘@handle’ of type ‘int’

dynamic-sqlsql serversql-server-2008t-sql

I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. However when I use sp_execute to try and bring back if the table exists I'm receiving the error
Procedure expects parameter '@handle' of type 'int'.

I'm not using an @handle parameter. Can someone tell me what this error means and why I'm receiving it? The relevent portion of my code is below.

DECLARE @SQL NVARCHAR(MAX),
        @Parameters NVARCHAR(4000),
        @TableNotExists INT,
        @SourceTable NVARCHAR(200),
        @DestDB NVARCHAR(200)

BEGIN

SET @SourceTable = 'table'
SET @DestDB = 'database'
SET @Parameters = N'@SourceTableIN NVARCHAR(200), @TableNotExistsOut INT OUTPUT'
SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

EXEC sp_Execute @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOUt = @TableNotExists OUTPUT

END

Best Answer

I think you meant to use sp_executesql:

EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT

And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement:

SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

That should be @TableNotExistsOUT.