SQL Server stored procedure date

Question

I have a procedure which add new data in a table.

The name of the table is in the paramaters.

The error is

The data types varchar(max) and date are incompatible in the add operator.

The date field must be type date.

PROCEDURE

USE [ProposalBuilderDev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[addLog] 
    -- Add the parameters for the stored procedure here
    @table varchar(max), 
    @date date,
    @version varchar(max),
    @process varchar(max),
    @level varchar(max),
    @message varchar(max),
    @stacktrace varchar(max),
    @user varchar(max),
    @environmentID varchar(max),
    @UUID varchar(max),
    @UDID varchar(max),
    @transactionID int,
    @new_identity int OUTPUT

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = 'INSERT INTO dbo.' + @table + '([Timestamp], [Version],Process,[Level],[Message],StackTrace,[User],EnvironmentID,UUID,UDID,TransactionID) VALUES (' + @date + ',' + @version + ',' + @process + ','+ @level + ','+ @message + ','+ @stacktrace + ','+ @user + ','+ @environmentID + ','+ @UUID + ','+ @UDID + ',' + @transactionID + ')'
    EXEC @sql

    SET @new_identity = SCOPE_IDENTITY();
END

I found this because the name of the table is in the parameter. If you have another way to do it, I can change — Weedoze
all your tables have the same column specification and datatype? — ughai

Evaldas Buinauskas Evaldas Buinauskas · Accepted Answer · 2015-05-04T09:17:14

Pleas try to use CAST()

... + CAST(@date AS VARCHAR(MAX) + ...

Also, using sp_executesql would be a better and safer approach.

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =  ' INSERT INTO dbo.' + @Table;
SET @SQL += '   ([Timestamp], [Version],Process,[Level],[Message],StackTrace,[User],EnvironmentID,UUID,UDID,TransactionID)';
SET @SQL += ' VALUES';
SET @SQL += '   (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10);';

EXECUTE sp_executesql
      @SQL
    , N'@p0 DATE, @p1 VARCHAR(MAX), @p2 VARCHAR(MAX), @p3 VARCHAR(MAX), @p4 VARCHAR(MAX), @p5 VARCHAR(MAX), @p6 VARCHAR(MAX), @p7 VARCHAR(MAX), @p8 VARCHAR(MAX), @p9 VARCHAR(MAX), @p10 VARCHAR(MAX)'
    , @p0 = @date
    , @p1 = @version
    , @p2 = @process
    , @p3 = @level
    , @p4 = @message
    , @p5 = @stacktrace
    , @p6 = @user
    , @p7 = @environmentID
    , @p8 = @UUID
    , @p9 = @UDID
    , @p10 = @transactionID;

Just specify correct column data types in 3rd line.

SQL Server stored procedure date

1 Answers