How does the question mark function in the SQL query?

Question

I'm attempting to edit an ETL package(SSIS) that queries a SQL table and outputs csv files for every StationID and I'm having trouble understanding how the question mark is being used in the query definition below. I understand ? is used a parameter but I don't understand how it's used in the date function below:

SELECT TimeSeriesIdentifier, StationID, ParameterID FROM dbo.EtlView WHERE
LastModified > DATEADD(hour, ?*-1, GETDATE()) 
AND StationID LIKE
CASE WHEN ? = 0 THEN
StationID
ELSE
?
END

score 5 · Answer 1 · answered Jul 22 '21 at 21:50

The parameterization available in SSIS is dependent upon the connection manager used.

OLE DB and ODBC based connection managers use ? as the variable place holder, whereas ADO.NET uses a named parameter, @myVariable.

OLE DB begins counting at 0 whereas ODBC used a 1 based counting system. They are both however ordinal based systems so in your CASE expression the two ? are for the same variable. But, you'll have to list that SSIS Variable twice in the parameter mapping dialog because it's ordinal based - i.e. (param, name) => @HoursBack, 0; @MyVar, 1; and @MyVar, 2;

A "dumb trick" I would employ if I had to deal with repeated ordinal based parameters or if I was troubleshooting packages is to make the supplied query use local variables in the query itself.

DECLARE
    @HoursBack int = ?
,   @MyVariable int = ?;

SELECT
    TimeSeriesIdentifier
,   StationID
,   ParameterID
FROM
    dbo.EtlView
WHERE
    LastModified > DATEADD(HOUR, @HoursBack * -1, GETDATE())
    AND StationID LIKE 
        CASE
            WHEN @MyVariable = 0 THEN StationID
            ELSE @MyVariable
        END;

Now I only have to map the SSIS Variable @MyVar once into my script as the "normal" TSQL parameterization takes over. The other benefit is that I can copy and paste that into a query tool and sub in the ?s with actual values to inspect the results directly from the source. This can be helpful if you're running into situations where the strong typing in SSIS prevents you from getting the results into a data viewer.

score 3 · Answer 2 · answered Jul 22 '21 at 19:19

3

SSIS is building a parameterized query.

You can get more information about this here (MySQL-specific): What is the question mark's significance in MySQL at "WHERE column = ?"?

Or you can get a more generally-applicable response here: What does a question mark represent in SQL queries?

answered Jul 22 '21 at 19:19

paneerakbari

680
1
4
17

Thanks! How are these indexed though? I mean, I don't believe it's the same parameter value being passed in every part of the query. – Mr. Geologist Jul 22 '21 at 20:54
It may vary by platform, but I typically see them indexed in order of appearance. – paneerakbari Jul 22 '21 at 21:28

score 3 · Answer 3 · answered Jul 23 '21 at 20:39

At a very "nuts and bolts" level, those are parameters being passed into the SQL statement by the package. With the Execute SQL task open, click on the tab that says Parameter Mapping. There will be a list of variables that are being sent into the query, and they are consumed in the order that they're listed.

Here's a logger for an archiving package I'm working on:

The query on the General tab just writes those five values to a table:

INSERT INTO dbo.ArchiveRowCounts (
  TableName,
  ServerName,
  ReportYear,
  BaseTblCnt,
  ArchiveTblCnt)
VALUES (?,?,?,?,?);

How does the question mark function in the SQL query?

3 Answers3