SSIS根据参数执行SQL任务 [英] SSIS Execute SQL task based on parameter

问题描述

我可以做类似下面的事情吗，让我知道IF @parameter=1 BEGIN ...查询... END IF @parameter=2如果可能，需要正确的语法.这是 OLE DB 连接.不是存储过程.只是一个sql查询

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

推荐答案

DECLARE @param AS INT = ?;

IF @param  = 1
BEGIN
    SELECT 1 AS Y;
END
ELSE IF @param = 2
BEGIN
    SELECT 2 AS Y;
END

您的查询中有两个问号，可能您只传递了一个变量.我见过开发人员两次(或多次)传递相同值的代码.这是低效的.更好的方法是在 SSIS 变量中接收传递的参数.好处:1. 你只需要传递一个值一次.2、更重要的是，如果你改变了传递参数在sql中的使用顺序，你不需要在执行SQL任务编辑器//参数的用户界面上改变它们的顺序.这是安迪伦纳德后来在他的回应中提出的建议.

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.

这篇关于SSIS根据参数执行SQL任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！