Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Did you mean to call sp_executesql rather than sp_execute? –  FreshPhilOfSO Jul 31 '12 at 13:58
    
The variable @TableNotExistsOut is spelled incorrectly within the SQL text. –  Jon Seigel Jul 31 '12 at 13:58
1  
Dont I feel sheepish. Thank you. –  Lumpy Jul 31 '12 at 14:05
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
you guys pegged it. Thanks. –  Lumpy Jul 31 '12 at 14:05
    
No problem, glad to help. –  Thomas Stringer Jul 31 '12 at 14:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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