How do I write a stored procedure that get table name and a record anf insert record into the table

Question

0.00/5 (No votes)

See more:

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 9-Nov-22 2:49am

Houman Farokhi

Updated 9-Nov-22 3:52am

Add a Solution

2 solutions

Solution 1

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!

Posted 9-Nov-22 3:51am

OriginalGriff

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. :)

Add a Solution

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

Richard Deeming · Accepted Answer · 2022-11-09T03:52:00

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.

How do I write a stored procedure that get table name and a record anf insert record into the table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How do I write a stored procedure that get table name and a record anf insert record into the table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us