[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic sql table insertion with table name as variable

Posted on 2014-08-08
2
Medium Priority
?
179 Views
Last Modified: 2014-08-08
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!
0
Comment
Question by:Focker513
2 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 40249691
you can only concatenate (var)char values. so variables not (var)char has to be casted:

select cast(@intValue as varchar) + 'aaa'
0
 

Author Comment

by:Focker513
ID: 40249696
What a PIA! Okay thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question