Frequent SP recompiles in proc with many temp tables

I noticed a specific stored proc was getting recompiled quite often, so I decided to try to optimize it a bit.

In the proc, there are 9 and sometimes 10 temp tables created; the tenth table is created conditionally:

IF @.param IS NOT NULL
BEGIN
CREATE TABLE #tbl (col1 VARCHAR(21))
CREATE UNIQUE INDEX XIDX_tmp_tbl_pk ON #tbl (col1)
INSERT #tbl (col1) SELECT @.param
END

All the create table statements are at the top of the proc, except this conditional one, which follows the last unconditional table create statement.

Each table gets at least one Index. Some are Unique, some are compund, some are on a single column. All the Indexes are created as above, using CREATE INDEX statements. None are created as primary keys.

Here's the question:
What effect on the overall performance, and specifically the recompiles, would it have if I first created the temp tables, then created all the indexes, before the rest of the work done in the proc?

I'm hesitant to mess with this badboy without some idea that it will help the performance.

TIA.

Hrmmmm... too much information? 20 views and not one comment?

How about if I get more generic: What are some "best practices" to prevent SP recompiles?

fRe: Multi-value parameters

Now do you know how to take multi-value parameter array values and pass to Sql Stored Proc for summing ?

Your multi value parameter gets passed to your stored proc as a comma delimited list of values, so you can use the IN keyword in your WHERE clause, like this:

Code Snippet

SELECT SUM(prices)

FROM Products

WHERE ProductID IN (@.myMultiValuedParameter)

|||

i'm trying to do this but i'm getting an error when the report compiles saying "Conversion failed when converting the nvarchar value '4998,5043' to data type int."

I'm passing in a multivalue parameter called "empno" of value "4998, 5043" and my sql for it is:

SELECT... FROM ... WHERE empno IN (@.empno)

Any ideas?

|||I have not tried it yet personally, but I have read from other posts that Stored procs do not take more than one parameter from Reporting Services...Is this true...?