September 29, 2008 at 10:41 am
I have a problem that's giving me a nightmare headache. In one part of a very long SQL server job, a stored procedure is kicked off that rebuilds a table to repopulate columns (it was much faster than an update). The procedure renames the current table, then inserts everything into a new table with the old name and left joins a sub-select, using an ISNULL statement to turn all unmatched rows into zeros. This left join adds two columns to the final table.
Here's the problem I'm getting. There are columns in the sub-select that should match up with columns in the original table (and therefore add correct data to the final table) but for some reason aren't joining properly - basically, they're being treated as NULLs and set to zero. When I copy and paste the sub-select into a new query window and run it, the columns are shown with the correct data. Furthermore, when I copy and paste the ENTIRE select into a new window and run it, it joins properly and populates the correct data like it should.
My question is, what could possibly cause a left-join to not join on those rows when the SP is run, but join on those rows when copied and pasted into a new query window? The selects are identical, the input variables (two dates) are identical, and the select gives the desired result when run manually.
September 30, 2008 at 12:12 am
1.Try with recompile option for your stored procedure.
2.Reassign all your parameters to local variables in your sp and use the local variables.
September 30, 2008 at 12:28 am
Left joins will work inside a procedure the same as they will work outside a proc. Your problem will be the query as a whole. check the query.
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply