CODEWITHSUNDEEP
sql-server
stakowerflol
stakowerflol

Reputation: 1079

SQL Server procedure with 2 arguments - why doesn't work?

I have done everything like in tutorial:

CREATE PROCEDURE dbo.checkDiff 
     @table1 NVARCHAR(50), 
     @table2 NVARCHAR(50)
AS
    SELECT * 
    FROM @table1 
    EXCEPT 
    SELECT * 
    FROM @table2
    UNION ALL
    SELECT * 
    FROM @table2 
    EXCEPT
    SELECT * 
    FROM @table1
go

But I get some errors:

Msg 1087, Level 16 State 1, Procedure checkDiff, Line 3 [Batch Start Line 25]
Must declare the table variable "@table1".

(same error, repeated several times)

Upvotes: 0

Views: 38

Answers (1)

Sage
Sage

Reputation: 4937

This Procedure should work for you. The reason it does not work is due to the comments left above. 

CREATE PROCEDURE dbo.checkDiff (
    @table1 nvarchar(50),
    @table2 nvarchar(50)
)

AS

Declare @SQL nvarchar(max)

SET @SQL = 'select * from ' + QUOTENAME(@table1) + ' except select * from ' + QUOTENAME(@table2) + '
    union all
    select * from ' + QUOTENAME(@table2) + ' except select * from ' + QUOTENAME(@table1)

    -- PRINT @SQL
    exec sp_executesql @SQL
go

Do take note though, this is a really bad way to compare data in two tables. Each of the two tables need to have the exact same number of columns for this to work correctly otherwise the union will fail.

Upvotes: 3

Related Questions