In looking at your stored procedure, I'm puzzled by a number of things:
Stylistically, I'd suggest that you put reserved words in lower case if you use upper case for indentifiers; it's a whole lot easier to read, IMHO. Personally, I prefer lower case identifiers--not that it makes much difference.
##Voucher_Tmp wouldn't appear to be a valid name for a temp table--did you mean #Voucher_Tmp?
When you say "it hangs" do you know what it's actually doing? Can you make another connection to the server (via a different session in a SQL tool) and run sp_who to see what's going on with the process? How big are the tables involved? Some of the things in your query can cause the optimizer to choose an inappropriate query plan (e.g. the use of MIN and MAX in one select statement's list, the use of the subquery when it appears it could be added as part of the join--although without a clear understanding of the data, that may not work given your use of FULL OUTER JOIN--but a full outer join can sure lead to some table scans).
So, here's what I'd suggest:
1. Use SET SHOWPLAN ON with SET NOEXEC ON and run the query with some test values to see the query plan.
Use DECLARE with the variable names to substitute for the declarations in the parameter list of the proc; use SELECT to assign values to your local variables. Are you scanning some big tables? Perhaps you need some indexes?
Use SET NOEXEC OFF followed by SET SHOWPLAN OFF when you're done.
2. When it's hung, make another connection (ideally log in as a different user so you won't confuse the two sessions) and run sp_who and see if you can see what's happening (a sleeping SELECT command perhaps--that could indicate some heavy table scanning).
To verify that the select into is running and not actually hung, you can examine the locks for the spid that's running the proc. The easiest way to do this, I find is this:
use <your database>
go
select object_name (id), page, class
from master..syslocks
where spid = <the spid running the proc>
and dbid = db_id('<your db name>')
/* this last condition will exclude the temp table which
could be confusing since it's name won't translate
properly via object_name as you're using a different DB */
go
Run the select repeatedly; if the page value changes, then it's definitely running, it just may take a good long while to finish! Also, if you only get one lock on a table, use the values of the class column in the Admin Guide or the Reference Manual to see if the lock in question is a table lock--unlikely in this situation.
BOL,
John J M Craig
Alpha-G Consulting, LLC
nsjmcraig@netscape.net
