Link to home
Start Free TrialLog in
Avatar of Focker513
Focker513Flag for United States of America

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.
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

Open in new window


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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Focker513

ASKER

What a PIA! Okay thanks.