problem with stored procedure with insert

Question

When I run "INSERT INTO tableName (columnName) VALUES ('value to be inserted') it works like a charm. If I create a stored procedure that does exactly that, passing the value as a parameter and run it, It seems to be executed, but nothing is inserted in the table.

The query works alone, not inside a stored procedure, so I'm sure there is no error in it.

Answer 1

Hi @itay4

You use a stored procedure to pass parameters, do you want to implement dynamic insertion?

I did a test of dynamic insertion before.

DECLARE @sSQL nvarchar(400)
DECLARE @TableName nvarchar(max)
DECLARE @ColName nvarchar(max)
DECLARE @Insert nvarchar(max)
set @TableName = 'test'
set @ColName = 'A'
set @Insert = 'j24h'
set @sSQL = N'INSERT INTO ' + @TableName + '(' + @ColName + ')' + ' VALUES (' +  QUOTENAME(@Insert,'''') + ');'
print @sSQL;
Exec sp_executesql @sSQL,N'@TableName nvarchar(max),@ColName nvarchar(max),@Insert nvarchar(max)',@TableName,@ColName,@Insert;

If you need to write into stored procedures, you can refer to this official document, which contains an example of dynamic insertion.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16

If what you need is not to implement dynamic insertion, but to debug your code, you need to expose your code.

Best regards,

Percy Tang

Answer 2

The query works alone, not inside a stored procedure

It works in Stored Procedure the same way as a "stand-alone" (we call it ad hoc) query.

Difficult to say, why it won't work for you, because you didn't provided any detailed information, nor a line of your SP code.

Answer 3

Here's the code:

# create a table
"CREATE TABLE Example (
    ID int,
    Name varchar(255)
);"

# insert a record
"INSERT INTO Example (ID, Name) VALUES (111, 'ROY')"

# a sanity check to see the new record in the table (works as expected)
"SELECT * from Example"

# create a stored procedure which contains the same insert command  
"CREATE PROCEDURE PROCEDURE_TEST AS INSERT INTO Example (ID, Name) VALUES (222, 'DEAN')"

# execute the stored procedure
"EXEC PROCEDURE_TEST"

# another check to see if the second record was added to the table
"SELECT * from Example"

----- It doesn't work, the second record doesn't get inserted -------

Answer 4

You are using the tag SQL Server and this code is not written in Transact-SQL which is the language SQL Server uses. Comments in T-SQL are written with the sign -- and not #. In addition queries do not come inside a quotation marksץ

it seems like this code come from your application for example but does not present any application code but pieces of code.

I will assume that this is just a mistake in the way you present the code here. If this is not the case, then you should infom us which database you use, since this is not code for SQL Server.

Note! In the next time please provide the exact code you run, using a code block in the editor, so the code will looks like in your SSMS and will not be changed by the editor of the forum.

Assuming that this is your real code, then this code does not have any issue!

USE tempdb
GO

DROP TABLE IF EXISTS Example
GO

-- create a table
CREATE TABLE Example (
    ID int,
    Name varchar(255)
);
GO

-- insert a record
INSERT INTO Example (ID, Name) VALUES (111, 'ROY')
GO

--a sanity check to see the new record in the table (works as expected)
SELECT * from Example
GO

-- create a stored procedure which contains the same insert command  
CREATE PROCEDURE PROCEDURE_TEST AS 
	INSERT INTO Example (ID, Name) VALUES (222, 'DEAN')
GO

-- execute the stored procedure
EXEC PROCEDURE_TEST
GO

-- another check to see if the second record was added to the table
SELECT * from Example
GO

If you have an issue, then you need to present how to reproduce the issue and provide the exact FULL RELEVANT CODE.

Using this SP on this exact table, should not raise any issue.