Hmm

You are using "read only routing" and setting the application intent parameter.

The best solution appears to be to change your application which is something you don't want to do. The parts of the application that are "read only" would have a different connection string with the "application intent" parameter.

I guess the other way is to do what you are doing and lookup each sproc first against the listener and then depending on the sproc being "read only" you would either keep the connection or change it to "applicationintent=readonly". It's kind of messy.

The last way, which is probably the ugliest is let the sproc fail. Meaning always connect with "read only" and sql server will throw you an error if you try to do a DML operation. Then you would connect normally to the primary via the listener.

The solution that we have implemented where I work is to change the sections of the application that perform "read only" operations to connect with "applicationintent=readonly" and direct to the secondary. It's work but probably the best solution.

Steve