CODEWITHSUNDEEP
sql-serverstored-procedures
user4063679
user4063679

Reputation:

How do I make dynamic SQL to insert 'date' variable?

I have this stored procedure created in SQL Server 2014 as

create procedure ProcAddDate
    @tableName varchar(10),
    @inpDate date
as
    declare @sqlInsString varchar(max)

    set @sqlInsString = 'insert into '+ @tableName + ' values('
    set @sqlInsString = @sqlInsString + @inpDate + ')'

    execute(@sqlInsString)
go

However, when I attempt to execute this statement, SQL Server throws up an error

Msg 402, Level 16, State 1, Procedure ProcAddDate, Line 7
The data types varchar(max) and date are incompatible in the add operator.

How do I create a stored procedure which would let me add a date to a table, by taking in a value of type 'date' ?

Upvotes: 2

Views: 2578

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

If you have to do this (it's usually a sign of a broken data model if you're parametrizing table names), use sp_executesql so that you can pass the data across, unmolested, with the correct type (rather than forcing it into and then back out of a string):

create procedure ProcAddDate
@tableName sysname,
@inpDate date
as
    declare @sqlInsString nvarchar(max);
    set @sqlInsString = N'insert into '+ @tableName + ' values(@inpDate)';
    declare @parms nvarchar(max);
    set @parms = N'@inpDate date';
    execute sp_executesql @sqlInsString,@parms,@inpDate;
go

(I've also made a few other stylistic changes to the above)

Upvotes: 7

Related Questions