Hi
After finding out about the DisableBind=0 setting in an earlier question (https://community.appeon.com/index.php/qna/q-a/parametrized-query-execution) I've been experimenting with it for a while. (using SNC11 on SQL Server 2012)
One particular thing I noticed was that Powerbuilder doesn't seem to recognize reused parameters, instead treating each instance of the parameter as a new distinct value.
To illustrate, here's a simple Query with 1 parameter that's used twice:
SELECT count(1) AS cnt
FROM sys.tables t
JOIN sys.triggers tr ON tr.parent_id = t.object_id
WHERE year(t.create_date) = :year
AND year(tr.create_date) = :year
Here's the query that's sent to the database:
exec sp_executesql N'SELECT count(1) AS cnt
FROM sys.tables t
JOIN sys.triggers tr ON tr.parent_id = t.object_id
WHERE year(t.create_date) = @P1
AND year(tr.create_date) = @P2',N'@P1 float,@P2 float',2017,2017
Now since more parameters make a query more complex to analyze (thus increasing the risk of a bad execution plan to be generated), I'm wondering if this is the expected behavior or if there's another setting I'm missing.
Regards,
Markus
