SQL Server error "Operand type clash: varchar is incompatible with int "

Question

I have made below stored procedure for taking the backup of a database

CREATE PROCEDURE [dbo].[usp_databasebackup]  
    @DatabaseName NVARCHAR(200),
    @Path NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlQuery  VARCHAR(5000)  

    BEGIN
        SET @sqlQuery = ' BACKUP DATABASE ['+@DatabaseName+'] TO DISK = N''D:\@DatabaseName.bak'' 
                          WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N''@DatabaseName-Full Database Backup'', 
                          SKIP, NOREWIND, NOUNLOAD, STATS =''10'' '

        EXEC (@sqlQuery)
    END
END
GO

When I execute the stored procedure with a database name and path as parameters, I get this error:

Operand type clash: varchar is incompatible with int

WARNING This is wide open to injection attacks! '...[' + @SomeString + ']...' is not injection safe! Whenever you inject dynamic objects always properly quote them with QUOTENAME. N''D:\@DatabaseName.bak'' is also not going to work; SQL isn't a script language, so it won't replace the value of @DatabaseName (which is undefined) with the value of @DatabaseName. Have a look here at how to inject values into a dynamic statement. — Larnu
Just a guess, but doesn't STATS = expect an integer instead of a string? What happens when you manually "fill in the blanks" in your backup statement and try to execute it? — Heinzi
You are backup up to disk location "D:\@databasename.bak". Stats should not be quoted. You don't even use @path. You don't even need DSQL for this, backup database allows you to pass parameters for database and path. — Stu
Side Note: @DatabaseName should be defined as a sysname, a synonym for nvarchar(128) NOT NULL, which is the data type SQL Server uses for all object names. — Larnu

Stu Stu · Accepted Answer · 2021-05-18T08:14:52

You can backup a database without using Dynamic SQL.

You just need to use the backup command like this

backup database @DatabaseName
to disk=@DiskName
with name=@Name,
compression, format, init, skip, norewind, nounload, stats=10

@DatabaseName should be sysname

You need to prepare / concatenate names and paths prior to using variables in the backup command - ie, @DiskName would be a concatenation of a path and filename with a ".bak" suffix.

SQL Server error "Operand type clash: varchar is incompatible with int "

1 Answers