Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Pass a parameter to an "Execute SQL task" that simply truncates a table

I have a "Execute SQL task" in IS 2005 and it is placed in a foreach-container. For each iteration I want it to truncate a different table. The tables to truncate is specified in a variable at runtime.

In SQLtask_general.jpg you can see the settings of the general-tab and in SQLtask-parameterMapping.jpg you can see the settings of my parameter mapping.

When i execute the task I get the following error:
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE @tablename" failed with the following error: "Incorrect syntax near '@tablename'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Does anybody know if/how it is possible to pass a parameter to my SQL-task?
SQLtask-general.jpg
SQLtask-parameterMapping.jpg
0
Butjii
Asked:
Butjii
  • 4
1 Solution
 
Daniel WilsonCommented:
Yeah, you generally can't use a table name as a variable in SQL.

You could do dynamic SQL ...


Declare @sql varchar(1000)
set @sql = 'Truncate Table ' + @tablename
exec (@sql)

Open in new window

0
 
ButjiiAuthor Commented:
Thank you!
0
 
ButjiiAuthor Commented:
I've read that dynamic SQL should be avoided due to bad performance. Does anybody have a view on this matter?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ButjiiAuthor Commented:
I've read that dynamic SQL should be avoided due to bad performance. Does anybody have a view on this matter?
0
 
ButjiiAuthor Commented:
I've read that dynamic SQL should be avoided due to bad performance. Does anybody have a view on this matter?
0
 
8080_DiverCommented:
Well, yes, to the extent possible, dynamic SQL should be avoided.  That being said, though, you have to weigh the cost of dynamic SQL against the cost of not truncating the tables.  
I suppose your other choice ould be to have a set of TRUNCATE commands that are called depending on whether or not the table name is in the passed parameter but that would be rather cumbersome.
Why not just put the TRUNCATE command into whatever stored procedure is doing the rest of the work?  You could even pass a flag that you default, for instance, to 'N' meaning (Don't truncate the table) [@TruncateTable CHAR(01) = 'N'] and either truncate or not based on the flag.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now