I have a procedure that does the same set of 3 Update Queries 4 times.  The 
only difference in the 3 queries is whether the status is 1, 2 or 3.

So I can combine each of the 3 into 1 query (Where Status Between 1 and 3) 
and end up with 4 queries.

These 4 are essentially the same except one of the JOINS is a different 
table or view.

I was thinking of combining them into one dynamic expression and having only 
one select statement where the INNER JOIN changes depending which of the 4 
loops I am doing.

In each case, I am still doing 4 statements.

I assume the 4 statements would be better then 4 dynamic sql calls?

I was thinking in the dynamic sql it would have to recompile each time.

Thanks,

Tom 

I am not sure I understand how the static statements differ from the dynamic  SQL calls. Maybe post the SQL code.

If you use sp_executesql with parameter the query plan is cached and can be reused. And recompilation is not necessarily 
a bad thing. In some cases the cost of recompile is insignificant compared to using cached inefficient plan.

See Erland's article on dynamic search:
http://www.sommarskog.se/dyn-search-2005.html

-- 
Plamen Ratchev
http://www.SQLStudio.com

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:n9CdnSSU3aRqlCDWnZ2dnUVZ_sWqnZ2d@speakeasy.net...
>I am not sure I understand how the static statements differ from the 
>dynamic  SQL calls. Maybe post the SQL code.
>

I could either do something like this where the INNER JOINS changes to 
Table1, Table2, Table3 in 4 separate queries.

UPDATE TableA
SET TableA.Description = B.Description
FROM ##TableGT B
INNER JOIN vwCustomer vw
    ON vwAView.ExternalID = B.ID AND
           vw.CatID = B.ID
WHERE B.session = @session AND
               B.batchID = @batchTimeStamp AND
               B.ID BETWEEN @StartRow AND @EndRow

Or change it to something like:

SET @Ktr = 1
WHILE @Ktr <5

    SELECT @Sql = 'UPDATE TableA
                                SET TableA.Description = B.Description
                                FROM ##TableGT B
                                INNER JOIN ' + CASE WHEN @Ktr = 1 THEN 
'vwCustomer'
                                                                       ELSE 
@Ktr = 2 THEN 'Table1'
                                                                       ELSE 
@Ktr = 3 THEN 'Table2'
                                                                       ELSE 
@Ktr = 4 THEN 'Table3' END + ' vw
                                          ON vwAView.ExternalID = B.ID AND
                                                 vw.CatID = B.ID
                                 WHERE B.session = @session AND
                                                B.batchID = @batchTimeStamp 
AND
                                                B.ID BETWEEN @StartRow AND 
@EndRow'

    EXECUTE sp_executesql @Sql,...

    SET @Ktr = @Ktr + 1

BEGIN
END

Thanks,

Tom
> If you use sp_executesql with parameter the query plan is cached and can 
> be reused. And recompilation is not necessarily a bad thing. In some cases 
> the cost of recompile is insignificant compared to using cached 
> inefficient plan.
>
> See Erland's article on dynamic search:
> http://www.sommarskog.se/dyn-search-2005.html
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 

For this may be better to go with dynamic SQL. You can try using IF conditional flow to execute different queries, or 
even create separate stored procedures for each case. Again, in Erland's article you will find good discussion on the 
different approaches to handle this.

-- 
Plamen Ratchev
http://www.SQLStudio.com

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:n9CdnSeU3aQ8jCDWnZ2dnUVZ_sU3AAAA@speakeasy.net...
> For this may be better to go with dynamic SQL. You can try using IF 
> conditional flow to execute different queries, or even create separate 
> stored procedures for each case. Again, in Erland's article you will find 
> good discussion on the different approaches to handle this.
>
OK.

I'll take a look at it.

What about the CASE statement, wouldn't that work?

I figured the dynamic SQL would be better as well because if you make 
changes you have to make them in 4 places. I was mainly concerned with the 
performance since they were paging to prevent timeouts ( which I think 
should be preventable in any case).

Thanks,

Tom

> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 

Yes, the CASE expressions will work, you just build each query in the loop and execute. But instead of that why not 
simply run four separate queries? Looking in more detail at your code now, there is really no reason to build 
dynamically the queries. You do not save anything by writing less code, using four update queries is better. My first 
response after you posted the code was incorrect - there is no need to use IF statements or separate stored procedures 
because you want to execute all 4 queries.

-- 
Plamen Ratchev
http://www.SQLStudio.com

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:n9CdnSaU3aQLpSDWnZ2dnUVZ_sWqnZ2d@speakeasy.net...
> Yes, the CASE expressions will work, you just build each query in the loop 
> and execute. But instead of that why not simply run four separate queries? 
> Looking in more detail at your code now, there is really no reason to 
> build dynamically the queries. You do not save anything by writing less 
> code, using four update queries is better. My first response after you 
> posted the code was incorrect - there is no need to use IF statements or 
> separate stored procedures because you want to execute all 4 queries.
>
That was one of the options I as looking at.

I thought maybe it might be better to do the same query as dynamic more as a 
maintenance issue, not so much that it was less code.  If you make a change 
to one, you have to make sure you make the change to all 4.  But I was 
concerned with whether the optimizer would have to re-calculate the plan 
each time it ran it.

If that isn't the case, it may be better, as you say, to do the 4 queries 
separately.

Thanks,

Tom

> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 

Again, when you use sp_executesql with parameterized query SQL Server will place the plan in cache and reuse it. But I 
think dynamic SQL can create more maintenance issues as it is not clear and easy to understand. Using four queries will 
be simple and trivial to maintain, plus avoids all of the pitfalls of dynamic SQL.

-- 
Plamen Ratchev
http://www.SQLStudio.com

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:zqednZzKyd5xoiPWnZ2dnUVZ_gYAAAAA@speakeasy.net...
> Again, when you use sp_executesql with parameterized query SQL Server will 
> place the plan in cache and reuse it. But I think dynamic SQL can create 
> more maintenance issues as it is not clear and easy to understand. Using 
> four queries will be simple and trivial to maintain, plus avoids all of 
> the pitfalls of dynamic SQL.
>
I agree.

Reading Erlands article helped to clarify the issues.

Thanks,

Tom

> -- 
> Plamen Ratchev
> http://www.SQLStudio.com