An alternative for Dynamic SQL required

Hey guys I've something like this which is a reauirement. I dont want to use
a dynamic SQL
eg is
create proc proc1
@.ownername varchar(100),
@.IPaddress varchar(15)
as
select a,b,c from Table1 t1
join Table2 t2 on t1.col1=t2.col2
where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
+'%') OR
(t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
(t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
go
Hopefully you can see the situation here.
I have an app where I can select (either or fname or lname) and I can also
select (either or internal IP or external IP). The above proc will work for
either in both cases.
What I want to do in the stored proc is somehow check these conditions. I
dont want to use dynamic SQL. Can you suggest an alternative which will help
in performance when compard to Dynamic SQL? Thank you.One obvious performance issue is that you are performing a LIKE comparison
on a value that is left truncated. For example, if OwnerName is indexed,
then this is will result in an index scan:
where OwnerName LIKE 'John%'
However, the following would result in a non-indexed table scan:
where OwnerLastName LIKE '%Smith'
where OwnerLastName LIKE '%Smith%'
Also, read this document; specifically the paragraphs about "sargable"
comparison arguments.
http://www.microsoft.com/technet/pr...s/inside14.mspx
Actually, dynamic SQL may be the solution to your problem. If you could
construct your SQL on the application side or construct the SQL within the
procedure in a variable and execute using the T-SQL Exec function.
http://msdn.microsoft.com/library/d...br />
05ro.asp
You can use the Show Execution Plan feature of Query Analyzer to determine
if your query is properly utilizing an index.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:8FD1CC5B-A8F9-4080-A75C-65D85E331CF9@.microsoft.com...
> Hey guys I've something like this which is a reauirement. I dont want to
> use
> a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
> +'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
> go
>
> Hopefully you can see the situation here.
> I have an app where I can select (either or fname or lname) and I can also
> select (either or internal IP or external IP). The above proc will work
> for
> either in both cases.
> What I want to do in the stored proc is somehow check these conditions. I
> dont want to use dynamic SQL. Can you suggest an alternative which will
> help
> in performance when compard to Dynamic SQL? Thank you.|||On Fri, 17 Mar 2006 09:05:26 -0800, Tejas Parikh wrote:

>Hey guys I've something like this which is a reauirement. I dont want to us
e
>a dynamic SQL
>eg is
>create proc proc1
>@.ownername varchar(100),
>@.IPaddress varchar(15)
>as
>select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
>where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
>+'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
>go
>
>Hopefully you can see the situation here.
>I have an app where I can select (either or fname or lname) and I can also
>select (either or internal IP or external IP). The above proc will work for
>either in both cases.
>What I want to do in the stored proc is somehow check these conditions. I
>dont want to use dynamic SQL. Can you suggest an alternative which will hel
p
>in performance when compard to Dynamic SQL? Thank you.
Hi Tejas,
Lots of useful information for this type of problem can be found on
Erland's page: http://www.sommarskog.se/dyn-search.html.
Hugo Kornelis, SQL Server MVP|||Tejas Parikh (TejasParikh@.discussions.microsoft.com) writes:
> Hey guys I've something like this which is a reauirement. I dont want to
> use a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%' + ISNULL(@.ownername,
> A.OwnerFirstName) +'%') OR
> (t1.OwnerLastName LIKE '%' + ISNULL(@.ownername,
> A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.ExternalIP) + '%') OR
> (t1.InternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.InternalIP)+'%'))
> go
Well, as long as you have the % first in the LIKE operations, this is
going to table scan no matter what you do, so there would not be much
point with using dynamic SQL for better performance. Your current code
would work fine.
I would recommend that you leave it to the users to specify any initial %
if they need it. Then there is a at least a ghost of a chance for
indexes to be used. It's difficult to give detailed suggestions though,
as I don't know the tables, and I suspect that your real procedures have
more than these two parameters.
But my article, that Hugo also pointed you to, might give you some
ideas: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Alternative to dynamic sql?

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @.lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @.lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
JennyMaybe:

SELECT *
FROM TableX
WHERE idnr = @.lcIdNr OR @.lcIdNr IS NULL

This article explains some of the things you should consider before using
Dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

--
David Portas
----
Please reply only to the newsgroup
--|||SELECT *
FROM Foobar
WHERE idnr = COALESCE(@.lcIdNr, idnr);|||[posted and mailed, vnligen svara i nys]

Jenny (jenny@.megasol.se) writes:
> If this id is submitted then I will return only the posts with this
> idnr, but if this is not submitted, I will return all posts in table.
> As I can see I have two options
> 1. IF @.lcIdNr IS NOT NULL
> SELECT *
> FROM table
> WHERE idnr = @.lcIdNr
> ELSE
> SELECT *
> FROM table
> 2. Use dynamic SQL.
> The first example can work with just one parameter but with a couple
> of different input paramters this could be difficult, anyway this is
> not a good solution. The second example works fine but as I understand
> dynamic sql is not good from the optimizing point of view.

Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.

For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp