Parameterized SQL Insert failing

Question

I have a c# winform app and I'm trying convert my insert statement into a parameterized sql stored procedure. When I ExecuteNonQuery, I get back -1 and I can't seem to find out why.

SQL Procedure:

CREATE PROCEDURE CreateReceiver
-- Add the parameters for the stored procedure here
@DBNAME sysname,
@SiteID varchar(25),
@Receiver varchar(25),
@StatusID int,
@ExpDelDate Date,
@CreateDate DateTime,
@CreateUserID varchar(25),
@CreateUserName varchar(25),
@ModDate DateTime,
@ModUserName varchar(25),
@ModUserID varchar(25),
@UserField5 varchar(25)
AS
BEGIN

DECLARE @sql NVARCHAR(MAX);

SET @sql = '
INSERT INTO @DBNAME (SiteId, ReceiverNumber, StatusId, ExpectedDeliveryDate, CreatedDate, CreatedUserID, 
CreatedUserName, ModifiedDate, ModifiedUserID, ModifiedUserName, UserField5) VALUES (@SiteID,@Receiver,@StatusID,
@ExpDelDate,@CreateDate,@CreateUserID,@CreateUserName,@ModDate,@ModUserID,@ModUserName,@UserField5)
';
SET @sql = REPLACE(@sql, '@DBNAME', @DBNAME);

exec sp_executesql @sql,
                   N'@SiteID varchar(25), @Receiver varchar(25), @StatusID int, @ExpDelDate Date, @CreateDate DateTime, @CreateUserID varchar(25),
                    @CreateUserName varchar(25), @ModDate DateTime, @ModUserID varchar(25), @ModUserName varchar(25), @UserField5 varchar(25)',
                   @SiteID=@SiteID, @Receiver=@Receiver, @StatusID=@StatusID, @ExpDelDate=@ExpDelDate, @CreateDate=@CreateDate, 
                   @CreateUserID=@CreateUserID, @CreateUserName=@CreateUserName, @ModDate=@ModDate, @ModUserID=@ModUserID, 
                   @ModUserName=@ModUserName, @UserField5=@UserField5;
END
GO

Here is the c# code that calls the procedure:

internal static bool BuildReturnReceiver(string pReturnTrackingNumber, string pCustomer, string pSiteId, SqlArgs pSqlArgs)
    {
        var pwd = GetPwd();
        var sqlCred = new SqlCredential(Sqluser, pwd);
        var tCatalog = GetDB(pSqlArgs.sqlDB);

        var sqlConnection = new SqlConnection
        {
            ConnectionString = $"Data Source={SqlServer};Initial Catalog=TSC-Telaid;",
            Credential = sqlCred
        };

        var dbName = $"[{tCatalog}].dbo.[{pSqlArgs.sqlTable}]";

        var tExpDelDate = CalculateDays();
        var name = GetUser();

        var sqlCommand = new SqlCommand
        {
            Connection = sqlConnection,
            CommandType = CommandType.StoredProcedure,
            CommandText = "CreateReceiver",
            Parameters = { new SqlParameter("@DBNAME", dbName), new SqlParameter("@SiteID", pSiteId),
                new SqlParameter("@Receiver", pSqlArgs.sqlFilterValue), new SqlParameter("@StatusID", 56), new SqlParameter("@ExpDelDate", tExpDelDate),
                new SqlParameter("@CreateDate", DateTime.Now), new SqlParameter("@CreateUserID", WindowsIdentity.GetCurrent().Name),
                new SqlParameter("@CreateUserName", name), new SqlParameter("@ModDate", DateTime.Now), new SqlParameter("@ModUserID", WindowsIdentity.GetCurrent().Name),
                new SqlParameter("@ModUserName", name), new SqlParameter("@UserField5", pSqlArgs.sqlUpdateValue)},
        };

        try
        {
            sqlConnection.Open();
            return sqlCommand.ExecuteNonQuery().Equals(1);
        }
        catch (SqlException ex)
        {
            MessageBox.Show(@"Error: " + ex.Message, @"Exception", MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation);
            return false;
        }
        finally
        {
            sqlConnection.Close();
        }

    }

--EDIT--

Corrected SQL Procedure:

Alter PROCEDURE CreateReceiver
-- Add the parameters for the stored procedure here
@DBNAME sysname,
@SiteID varchar(MAX),
@Receiver varchar(MAX),
@StatusID int,
@ExpDelDate Date,
@CreateDate DateTime,
@CreateUserID varchar(MAX),
@CreateUserName varchar(MAX),
@ModDate DateTime,
@ModUserName varchar(MAX),
@ModUserID varchar(MAX),
@UserField5 varchar(MAX)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
INSERT INTO ' + @DBNAME + '(SiteId, ReceiverNumber, StatusId, ExpectedDeliveryDate, CreatedDate, CreatedUserID, 
CreatedUserName, ModifiedDate, ModifiedUserID, ModifiedUserName, UserField5) VALUES (@SiteID,@Receiver,@StatusID,
@ExpDelDate,@CreateDate,@CreateUserID,@CreateUserName,@ModDate,@ModUserID,@ModUserName,@UserField5)
';
--SET @sql = REPLACE(@sql, '@DBNAME', @DBNAME);

exec sp_executesql @sql,
                   N'@SiteID varchar(MAX), @Receiver varchar(MAX), @StatusID int, @ExpDelDate Date, @CreateDate DateTime, @CreateUserID varchar(MAX),
                    @CreateUserName varchar(MAX), @ModDate DateTime, @ModUserID varchar(MAX), @ModUserName varchar(MAX), @UserField5 varchar(MAX)',
                   @SiteID=@SiteID, @Receiver=@Receiver, @StatusID=@StatusID, @ExpDelDate=@ExpDelDate, @CreateDate=@CreateDate, 
                   @CreateUserID=@CreateUserID, @CreateUserName=@CreateUserName, @ModDate=@ModDate, @ModUserID=@ModUserID, 
                   @ModUserName=@ModUserName, @UserField5=@UserField5;
END
GO

Answer 1

You can't pass the table name as a parameter (and this case it is named @DBNAME).

You need to make that part of your query. Notice I also added the N at the beginning of your string. This is to avoid any pitfalls from conversion from ASCII to UNICODE.

SET @sql = N'
INSERT INTO ' + @DBNAME + '(....'

--EDIT--

Removed QUOTENAME thanks to SqlZim who pointed out that you are passing the Database.schema.TableName as a single piece of data. QUOTENAME around that isn't going to work out.