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
I am interested in writing a stored procedure that would run a select query based on the input in the text fields
- 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 @EmployeeID
Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL
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.
Have a great day.
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 .
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.
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
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, '%')
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.