t-sql 2012 not using parameters correctly

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql 2012 not using parameters correctly

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-12-05 : 13:22:16

I want the user to be able to select various customers based upon if the first name, last name, or middle names are selected. The idea is for the user to select one, two, and/or three of the parameters and get the results they want. The following sql works but I am wondering if there is another way to write the sql below and get the same results where the code is more specific?

SELECT [lastName]
,[firstName]
,[middleName]
,[suffix]
,a.[userid]
from [TEST].[dbo].[Identity]
where ([lastName] like '%' + @lname + '%' and
[firstName] like'%' + @fname + '%' and
[middleName] like'%' + @mname + '%')
or ([lastName] like '%' + @lname + '%' and
[firstName] like'%' + @fname + '%' and
@mname is null)
or ([lastName] like '%' + @lname + '%' and
[middleName] like'%' + @mname + '%' and
@fname is null)
or ([firstName] like '%' + @fname + '%' and
[middleName] like'%' + @mname + '%' and
@lname is null)
or ([lastName] like '%' + @lname + '%' and
@fname is null and
@mname is null)
or (@lname is null and
[firstName] like'%' + @fname + '%' and
@mname is null)
or (@lname is null and
@fname is null and
[middleName] like'%' + @mname + '%')
or (@fname is null and @lname is null and @mname is null)
order by [lastName], [firstName], [middleName]

I am asking this question since the query does not work in a .net web application. I thought if the code could be rewritten, then maybe the sql would work for the .net application

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-05 : 13:45:25

If the code works, then wrap it into a stored procedure and call the stored procedure in your .net app. If you ever need to make changes, updating a stored procedure can be done on-the-fly, whereas that's not possible if the query is inside the app. There are many other benefits to stored procedures too, such as performance and security.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-12-05 : 14:23:34

This sql does not work in the .net application. Thus can you tell me a different way to rewrite the sql.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-05 : 14:30:40

Like I said, wrap it into a stored proc. Here's an example:

create proc someprocname
(@var1 int, @var2 char(5), @var3 varchar20, @var4 datetime)
as

set nocount on

select column1, column2, column3
from sometable
where column4 = @var1 and column5 = @var2 and column12 = @var3 and column25 > @var4;
go

And then in your app, you would call someprocname instead of running the query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-12-05 : 16:32:52

SQL works fine now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources