Re: perl & SQL best practices

[Jenda]

in reply to perl & SQL best practices

Dynamic SQL is dynamic SQL no matter where you construct it. There are times when it is the way to go, but you should try to keep them to a minumum. A (possibly generated) structure of

IF (@Whatever is not NULL) 
  IF (@Other is not NULL)
    SELECT ...
    WHERE @Whatever = Whatever and @Other = Other
  ELSE
    SELECT ..
    WHERE @Whatever = Whatever
  ...

Tends to perform better if used often. Just make sure it doesn't get way too big. And keep in mind that if there is no index on a column (@Whatever is NULL or @Whatever = Whatever) is enough, no need to test that variable in an IF and use separate selects.

[ksublondie]

Just make sure it doesn't get way too big.

"Big" as in results, lines of code, too many if/else/then, or were you referring to something else?

[Jenda]

Lines of code. The code has to be compiled the first time you use the stored procedure that encloses the structure and if it gets way too long, the compilation will take too much time.