THANK YOU!!!!!! I have been fighting with this stored procedure for about 3 days now. I was sure this was going to kill the last bit of motivation I still had left to optimize this beast.
I had 4 parameters going in to the Stored procedure. In the query I had declared and set them at the top so I could easy copy/paste when I finished.
I prefixed all the variables coming in to the stored procedure with an o declared the variables in the stored procedure and set them equal to the o prefixed variables and the stored procedure ran as expected.
again THANK YOU. I'll have to try to remember this in case I ever run into it again. For now I'm off to fix the o variables to make sense.
Lynn Pettis (9/24/2007)
Without seeing the code for the stored procedure, it is a guess. It could be parameter sniffing at work. Are you using the variables declared in the header of the sproc in the query? If so, declare local variables inside the procedure, set those variables to the values you are sending in, and use the local variables in your query.
😎