Focker513
asked on
Dynamic sql table insertion with table name as variable
Greetings experts,
I think I am having a syntax issue with string concatenation on a stored procedure using dyn sql.
I receive a compilation error "The data types nvarchar and date are incompatible in the add operator." So, I am assuming my concatenation operations have incorrect syntax.
Thanks!
I think I am having a syntax issue with string concatenation on a stored procedure using dyn sql.
CREATE PROCEDURE [dbo].[INSERT_RECS] (@tableName nvarchar(50), @pFilterCol nvarchar(5), @pFROMDATE date, @pTODATE date)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX);
if(@tableName like 'TABLE1%')
BEGIN
set @SQL = 'insert into [' + @tableName + '] (Col1, Col2, Col3, T_DATE) (select Col1, Col2, Col3, T_DATE from
dbo.VIEW1 where CONVERT (DATE, T_DATE) between ' + @pFROMDATE +' and ' + @pTODATE +' AND FILTER_COL = ' + @pFilterCol;
END
else if(@tableName like 'TABLE_2%')
BEGIN
set @SQL = 'different insert here';
END
exec sp_executesql @SQL;
END
I receive a compilation error "The data types nvarchar and date are incompatible in the add operator." So, I am assuming my concatenation operations have incorrect syntax.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER