Can i do something like below, let me know IF @parameter=1 BEGIN ...query... END IF @parameter=2 Need the correct syntax if it is possible. It's OLE DB connection. Not a Stored Proc. just a sql query
Add a comment
|
3 Answers
DECLARE @param AS INT = ?;
IF @param = 1
BEGIN
SELECT 1 AS Y;
END
ELSE IF @param = 2
BEGIN
SELECT 2 AS Y;
END
There are two question marks in your query and probably you were passing only one variable. I have seen code where developers pass the same value twice (or multiple) times. This is inefficient. A better way is to receive the passed parameters in SSIS variables. Advantages: 1. You need to pass one value only once. 2. More importantly, if you change the order in which the passed parameters are used in the sql, you do not need to change their order on the user-interface of Execute SQL Task Editor//Parameters. This is what Andy Leonard has suggested later in his response.
Comments
You can. Assuming you are referring to an Execute SQL Task, the parameters in an Execute SQL Task using an OLE DB connection utilize question marks (?) as parameter placeholders. You map the placeholders to SSIS variables on the Parameter Mapping page of the Execute SQL Task. In the SQLStatement property you would enter:
If (?=1) begin ... {some T-SQL here} ... end If (?=2) begin ... {some T-SQL here} ... end
That's one way to accomplish what I think you are asking.
Another way is to create an Execute SQL Task to read the value of @parameter from the database into an SSIS variable. Then you can build two Execute SQL Tasks - one with each option for T-SQL as the SQLStatement property - and use expressions on precedent constraints to determine which Execute SQL Task to execute.
Hope this helps, :{>
1 Comment
user1810575
If (?=1) begin ... {some T-SQL here} ... end If (?=2) begin ... {some T-SQL here} ... end Above sql works?? I get an error "unable to Parse". Shoot me the correct syntax
You cannot use Execute SQL Task to run Transact-SQL statements.
For setting conditional SQL Statement based on what you are trying to achieve.
In Execute SQL Task editor
In general tab, leave the SQLStatement blank.
In parameter mapping tab, add parameter and map User::Parameter variable to Parameter Name 0.
In Expression tab, set the SQLStatementSource to
(DT_NUMERIC, 18, 0) @[User::Parameter]==1 ? ...query 1... : ...query 2...
