Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2001-12-13 : 13:56:49
|
| Which is faster for multvalued paramters? Put them into a temp table and join to it or use dynamic SQL with IN() predicates?(AdHoc Query)CREATE TABLE #Parameters (Parameter INT NOT NULL)INSERT INTO #Paramerts SELECT PK_ParameterID FROM Table WHERE PK_ParameterID IN ([List of Parameters])EXECUTE USP_MultiParameter @SingleValueParameter1=xxx(In Stored Procedure)SELECT ___FROM Table JOIN #ParametersWHERE ...vs.(AdHoc Query)EXECUTE USP_MultiParameter @SingleValueParameter1=xxx, @MultiParameterValue=(CommaSeparatedList)(In Stored Procedure)SET @SQL=SELECT ___SET @SQL=@SQL & FROM TableSET @SQL=@SQL & WHERE PK_Parameter_ID IN (1, 2, 3, 4) AND ...EXECUTE (@SQL)The Select statment is much more complicated with 3 instances of the multivalued parameter set in where clauses (1 each in 3 of the 4 subquery modules). I'm guessing the temp table is faster but just want to check to make sure as there is not much time for experimentationEdited by - GreatInca on 12/13/2001 17:07:24 |
|
|
|
|
|
Which is faster for multvalued paramters?