April 8, 2011 at 5:30 am
I am using a variable in Execute SQL Task and i am getting an error message while trying to parse it.
THis is the SQL command that i am using in the Task.
if @action='INSERT'
Begin
exec sp_insert @Stable,@dtable
End
It says that i should declare the scalar variable.
If i try to use parameters with the following command, it still throws and error message stating that the syntax is incorrect
if ?='INSERT'
Begin
exec sp_insert ?,?
End
Please help me on this
April 10, 2011 at 1:20 pm
Did you try:
declare @action varchar(10);
set @action = ?
if @action = 'INSERT'
Russel Loski, MCSE Business Intelligence, Data Platform
April 10, 2011 at 9:55 pm
If you use @paramname then the variable must be declared by the name with the appropriate datatype using the same same @paramname, index pointers 0,1.. may not be used. On the other hand if ? is used as variables then positional identifiers must be used (starting from 0) to represent the variables.
April 11, 2011 at 10:46 am
I had a similar problem a while ago - a package that had been running successfully for ages suddenly began failing a a sql task very similar to this one. I narrowed the problem to the IF statement and the only way I could get it to work quickly was to use expressions in the control flow. I had to create two control flows from the previous task each using an expression to check the variable value, one for == and the other for !=, so I could take the if statement out of the query. This works fine but I now avoid using IFs in SQL tasks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply