Your
Exec
statements execute in a separate context. None of the local variables declared in the procedure will be available to those statements.
You need to use
sp_executesql[
^] and use the local variable as an
OUTPUT
parameter:
declare @Count int
declare @CountSql nvarchar(max);
SET @countSql = N'select @Count = COUNT(*) from ' + @ActualTableName + N' where ' + @ActualColumnName + N' = @ItemString;';
EXEC sp_executesql @countSql,
N'@Count int OUTPUT, @ItemString nvarchar(max)',
@Count = @Count OUTPUT,
@ItemString = @ItemString;
You'll also need to use this technique for the actual
INSERT
to avoid a SQL Injection vulnerability:
declare @InsertSql nvarchar(max);
SET @InsertSql = N'insert into ' + @ActualTableName + N' (' + @ActualColumnName + N') values (@ItemString)';
EXEC sp_executesql @InsertSql,
N'@ItemString nvarchar(max)',
@ItemString = @ItemString;
Finally, note that
select MAX(ID) from ...
is almost always the wrong way to get the ID of the record you've just inserted. Unless you use the most restrictive transaction isolation level, another user could insert a record between your
INSERT
statement and your
SELECT
statement, giving you the wrong ID.
If your tables use an identity column, you should use the
SCOPE_IDENTITY[
^] function instead.