Inserting Record from one table into another Dynamic SQL

Question

All, Trying to make this code work.

This is a part of stored procedure called within another stored procedure; and inserts records from one table into another, but I get this error

Conversion failed when converting date and/or time from character string.

** Main Procedure **
EXEC [dbo].[usr_INSERTRECORD] ABC, @TableName, @TransDate, @Symbol
ALTER PROCEDURE [dbo].[usr_INSERTRECORD]
 -- Add the parameters for the stored procedure here
 @SourceTable SYSNAME,
 @TableName SYSNAME,
 @TransDate Date,
 @Symbol nvarchar(50)
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 @DATEVARCHAR nvarchar(4000);
 SET @DATEVARCHAR = CONVERT(NVARCHAR, @TransDate, 103);
 DECLARE @SQLCommand NVARCHAR(MAX) = 
 N'INSERT INTO ' + QUOTENAME(@TableName) + ' SELECT * FROM ' + + QUOTENAME(@SourceTable) 
 + ' WHERE (TRANSDATE = ' + '''' + @DATEVARCHAR + '''' +') ' + 'AND (SYMBOL = ' + '''' + @SYMBOL + '''' +')';
 EXECUTE [dbo].[sp_executesql] 
 @sqlCommand;
END

Answer 1

Use parameters instead of inserting the values in the string. You are already using sp_executesql, so this is just using it better:

DECLARE @SQLCommand NVARCHAR(MAX) = 
 N'INSERT INTO ' + QUOTENAME(@TableName) + ' SELECT * FROM ' + QUOTENAME(@SourceTable) + '
 WHERE TRANSDATE = @TransDate AND SYMBOL = @SYMBOL' ;
EXECUTE [dbo].[sp_executesql] @sqlCommand,
 N'@TransDate date, @Symbol varchar(50)',
 @TransDate = @TransDate, @Symbol = @Symbol;

Also, when using VARCHAR() in SQL, always include a length. The default length depends on the context, and leaving it out is an easy way to make a mistake.

Presumably, the cause of your error is in executing the generated SQL code. Format 103 is "dd/mm/yyyy", which may or may not convert back to a date correctly. If you really want to represent a date as a string, I recommend always using the ISO standard formats of YYYY-MM-DD or YYYYMMDD.

• Lukasz Szozda: WHERE TRANSDATE = @TransDate AND SYMBOL = @SYMBOL Remove ( or add closing one

Answer 2

That worked. Thank you Gordon. Here is the final code

ALTER PROCEDURE [dbo].[usr_INSERTRECORD]
 -- Add the parameters for the stored procedure here
 @SourceTable SYSNAME,
 @TableName SYSNAME,
 @TransDate Date,
 @Symbol nvarchar(50)
 --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
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 @DATEVARCHAR nvarchar(4000);
 SET @DATEVARCHAR = CONVERT(NVARCHAR, @TransDate, 103);
 DECLARE @SQLCommand NVARCHAR(MAX) = 
 N'INSERT INTO ' + QUOTENAME(@TableName) + ' SELECT * FROM ' + + QUOTENAME(@SourceTable) 
 + ' WHERE (TRANSDATE = @TransDate AND SYMBOL = @SYMBOL)';
EXECUTE [dbo].[sp_executesql] @sqlCommand,
 N'@TransDate date, @Symbol nvarchar(50)',
 @TransDate = @TransDate, @Symbol = @Symbol;
END