9

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
  • 4
    Did you mean to call sp_executesql rather than sp_execute? – Philᵀᴹ Jul 31 '12 at 13:58
  • The variable @TableNotExistsOut is spelled incorrectly within the SQL text. – Jon Seigel Jul 31 '12 at 13:58
11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.