Click here to Skip to main content
15,882,376 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I wrote a stored procedure in SQL Server 2019 which takes the table name and a value as parameters, and inserts that value into the defined table.

But when I try to run it, I get this error:

Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@count".


SQL
ALTER PROCEDURE [dbo].[InsertIn]

    @ItemTag nvarchar(max),
    @ItemString nvarchar(max),
    @InsertedID int = -1 output,
    @InsertStatus bit = 0 output
AS
declare @ActualTableName nvarchar(max)
declare @ActualColumnName nvarchar(max)
    select @ActualTableName= QUOTENAME(table_name) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @ItemTag
    select  @ActualColumnName= QUOTENAME(COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @ItemTag and COLUMN_NAME = @ItemTag

set xact_abort on
BEGIN transaction
    SET NOCOUNT ON
    declare @Count int
    declare @CountSql nvarchar(max)
    select @countSql =  ' select @Count = COUNT(*) from ' + @ActualTableName + ' where '+ @ActualColumnName+ ' = @ItemString ;'
    exec(@countSql)
if @Count = 0
    begin
        declare @InsertSql nvarchar(max)
        declare @InsertedIDSql nvarchar(max)
        select @InsertSql ='insert into '+ @ActualTableName + '('+@ActualColumnName+') values('+@ItemString +')' 
        select @InsertedIDSql = 'select MAX(ID) from '+ @ActualTableName 

        set nocount on;
        exec(@InsertSql)
        exec(@InsertedIDSql)
        set @InsertStatus = 1
    end
else
    set @InsertStatus = 0
    commit transaction


What I have tried:

Ive try debuging in Visua Studion it is working fine but never insert any thing in table
Posted
Updated 9-Nov-22 3:52am

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:
SQL
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:
SQL
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.
 
Share this answer
 
Comments
Houman Farokhi 9-Nov-22 12:37pm    
Its dont return error but dosnt insert any thing i table
Richard Deeming 10-Nov-22 4:31am    
That would suggest the item you're inserting already exists.
At a guess - and without any access to your DB or the code that calls the SP that is all it can ever be - there are no rows that match your item string. You need to use a combination of the debugger (to find out exactly what you are passing) and SSMS (to find out what exactly the DB contains) to work out what is going on.

I'd start by seeing if the item string is enclosed in quotes when it is passed, because if it isn;t, then it's not going to be treated as a literal string when your SELECT is executed.

But ... that's a bad idea - it's very open to SQL injection, and that's not something you can fix in SQL code.
I'd strongly suggest that you don't try to make your accesses so generic - you put your DB at risk and make your code harder to read.

There are also better ways to do the "get the ID value" as well, like @@IDENTITY - using MAX assumes that no other inserts have been performed by any user, and in a multiuser system like SQL Server that is not guaranteed and a very good way to mess up your data and make it extremely hard to sort out even manually!
 
Share this answer
 
Comments
Richard Deeming 9-Nov-22 9:53am    
Not quite; he's executing statements in a different context, and expecting to be able to access the local variables from the outer context. It's nothing to do with the number of matching rows. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900