I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date
I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @.FirstName and LastName like @.LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @.EmployeeIDselect *
from Employee
where (FirstName like @.FirstName and LastName like @.LastName)
or
(EmployeeID like @.EmployeeID)|||Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL :cool:
This query would sure work for the example I posted, but I was just wondering if I can give user more flexibility and allow him to enter lets say [partial first name (some string with wildcard characters) and partial ID] or [partial first name, last name and ID] or some such wierd combination. The query should adapt to the input and return result accordingly.
What is the best way to go about doing this, again thanks for any help I can get.
Thank you.
Have a great day.|||Yes.
You will need to add more complexity to your WHERE clause to accomplish the logic you want.
You will also need to use the LIKE operator if you want to allow wildcards.
You will also need to expect this query not to run very fast, if you include lots of logical operators and LIKE comparisons in your criteria...|||Since this is in a stored procedure you might use dynamic SQL to create the query on the fly and then execute it.
eg.
create x @.empid int, ...
declare @.sql varchar(200)
set @.sql = 'select * from Employee where '
if empid is not null set @.sql = @.sql & 'EmployeeID =' & @.empid
... etc etc
exec (@.sql)
...|||Thanks ejustuss and blindman, you guys were a tremendous help.
Since I am not very comfortable writing stored procedures this was my solution to the problem .. nothing ingenious but hey as long it works thats all I care about :D.
So I wrote this insanely strict stored procedure which would except user to input all the parameters (first name, last name, Employee ID, Date ... everything) and wrote a query something similar to this:
select * from Employee where FirstName like @.FirstName and LastName like @.LastName and EmployeeID like @.EmployeeID and ....
Since my stored procedure is not giving any flexibility to the user, I added flexibility on the client side in the web form code by replacing all fields left blank by the user with wildcard '%'. So if a user wanted to search by employee's first name, he would just enter the first name leaving other field blanks. These blank fields will be replaced with % and passed to the stored procedure.|||Not sure how effecient this is :
SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL('%' + @.EmployeeID + '%', EmployeeID)
AND FirstName LIKE ISNULL('%' + @.FirstName + '%', FirstName)
AND LAstName LIKE ISNULL('%' + @.LastName + '%', LastName)
AND DATEDIFF(Day, ISNULL(@.Date, Date), Date) = 0;|||Hey afx thanks for posting your version of the solution. I am sorry but I really did not understand the code you posted. Though your input was definetely useful since using ISNULL is by far a way better more efficient option :cool:
This is how I would use ISNULL
SELECT * FROM Employee
WHERE EmployeeID LIKE ISNULL( @.EmployeeID, '%')
AND FirstName LIKE ISNULL( @.FirstName, '%')
AND LAstName LIKE ISNULL( @.LastName, '%')
Thank you again for the input and effort :) :)|||Hi all,
I need help on building query statement. below is my table called inventory.
idx fabidx coloridx qty isReserved
1 1 1 15 Y
2 1 2 20
3 1 1 10
4 1 1 25 Y
5 1 2 23 Y
6 1 3 26
This is the output that i'm expecting.
fabidx coloridx qty isReserved
1 1 50 2
1 2 43 1
1 3 26 0
I need to get all distinct fabidx and coloridx, i need to get the sum of the distinct fabidx and coloridx, and i need to get the count of "Y" in the isReserved column of the distinct fabidx and coloridx.
Please help. Thanks.|||A simple aggregation query. Look up aggregation function in Books Online, and then post this as a new thread if you still need help.
