March 3, 2004 at 4:38 pm
I am new to SQL and SQL server and hence this question
I have an ASP.Net application that passes a text(string) value toa webservice. The webservice queries a local database to find matches in one table based on the passed string.
for example I am looking for a person in the person table of the database based on the fact that the race is the passed string.
Now I want to query the database for the same person but based on two text strings passed..race and last name.
so instead of doing
select * from person where race="black"
how do i modify this code to search based on race="black" and lastname="williams"
keep in mind that either of the two strings passed may or may not be blank so so just using OR in the SQL statement will not work .
i.e. select * from person where race='+ svalue1 +' or lastname='+ svalue2 +' will return different results depending on what values of race or last name are passed (null or valid values for both)
hope my problem is clear. Any help will be appreciated.
Thanks
Vinit.
March 3, 2004 at 4:57 pm
This sounds like a dynamic SQL situation. What you may have to do is build the SQL statement on the fly and use an EXEC command to run it. If you pass the values to a procedure, you could set the procedure up to handle whatever kind of values you pass, construct a SQL statement that fits the requirements for that query, and then runs the statement. The code you'd use looks something like this:
-----------------------------------------------
CREATE PROCEDURE usp_person_details
@Race varchar(30) = null,
@lastname varchar(30) = null
AS
DIM @select varchar(4000) --This is to hold the D-SQL statement
@select = 'SELECT * FROM person_details '
IF (@race is not null) and (@lastname is not null)
@select = @select + 'WHERE '
IF @Race is not null
@select = @select + 'race = ''' + @Race + ''' '
IF (@race is not null) and (@lastname is not null)
@select = @select + 'or '
IF @lastname is not null
@select = @select + ' lastname = ''' + @lastname + ''''
EXEC(@lastname)
Ryan
March 3, 2004 at 9:06 pm
That would seem perfectly do-able (although I did not know how untill you told me..thanks!).
But theres on problem...I just talked about 2 fields that the user inputs...race and/or last name...in my application I have to have the user enter in about 8 fields like SSN or Drivers Lic.# etc etc...makes the whole dynamic sql situation kinda complicated if iunderstand what your saying above.
But thanks for the help
March 4, 2004 at 12:34 am
Hi,
A typical "best practice" advice is to avoid using dynamic SQL when it is possible to solve the problem by some other technique. If you really have to use it, another "best practice" advice is to use variables instead of hard-coded (concatenated) constants in the SQL string.
Another usefull approach may be to use the application to build the SQL string on the fly, which is not the same as using dynamic SQL, but does the same thing and at the end, has the same functionality.
I hope this will help.
Regards,
Goce Smilevski.
March 4, 2004 at 1:22 am
Would this work?
USE Northwind
GO
CREATE PROC proc_test
@Region varchar(30) = NULL
, @City varchar(30) = NULL
AS
SELECT
CustomerID
, CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
, Country
, Phone
, Fax
FROM Customers
WHERE Region = @Region
OR City = @City
GO
EXEC proc_test 'SP', 'Berlin'
GO
DROP PROC proc_test
GO
Although I agree that the use of dynamic SQL *should* be avoided whenever possible, due to some limitations of T-SQL one might be forced in this direction when it comes to complex queries with multiple search criterias. Mabye this will help any further
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 4, 2004 at 4:59 am
This is actually very easy to do without using Dynamic SQL - never the nicest option. All you need to use is the isnull function in your stored procedure
The isnull function has the identity
IsNull(value_to_test, replacement_to_use_if_null)
so for example we have a sp of
Create Procedure mySP @value1 varchar(100), @value2 varchar(100) as
select
*
from
MyTable
where
field1 like isnull(@value1, '%')
and
field2 like isnull(@value2, '%')
Please note that the pattern matching needs to changed for char fields to ensure it is the same length as the field you are matching to
e.g a Char(10) field needs the pattern '%%%%%%%%%%' to work properly
Truth is always Beauty but Beauty is not always Truth
March 4, 2004 at 8:09 am
I never would have thought to use LIKE ISNULL, but that's a stellar option - definately seems like a better option than the dynamic SQL that I posted earlier. Although dynamic SQL has it's advantages, I agree that's to be avoided unless absolutely necessary, since it does carry along with it a host of extra problems/considerations.
Congrats on the first post, reiss - good answer!
Ryan
March 4, 2004 at 4:53 pm
select
*
from
MyTable
where
(field1=@value1 or @value1 is null)
and
(field2=@value2 or @value2 is null)
That saves you from "pattern matching" for different types
March 4, 2004 at 9:33 pm
Thanks guys!!!... I got it going.
I am using the Like statement and checking for each field to see if its blank..if not I just add AND to the query dynamically before finally building the query..thanks a lot.
March 5, 2004 at 5:49 pm
Another trick is to use the same field twice...
CREATE PROCEDURE usp_person_details
@Race varchar(30) = null,
@lastname varchar(30) = null,
@anotherfield varchar(30) = null
AS
SELECT * FROM person_details
WHERE
race = COALESCE(@race, race)
AND
lastname = COALESCE(@lastname, lastname)
AND
anotherfield = COALESCE(@anotherfield, anotherfield)
etc.
Each field specified just matches to itself if it was not specified!
and this stored proc compiles (makes it faster)!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply