July 1, 2011 at 1:21 pm
I have 4 parameters in the report e.g. id,emp_name,emp_dept,emp_loc with a textbox for each of them..
now the report should be generated using the search string created by values entered into these four textboxes using wild cards..
e.g.
if user enters emp_id as 1 then select * from employee where id like (%1%)
if user enters emp_dept as 'sales' then select * from employee where emp_dept like (%'sales'%)
now user can enter emp_name and emp_dept or any other combination or also all the four attributes..so search string needs to be configured accordingly..can somebody please suggest me how to do this..pls
really need ur help guys.. 🙁
July 1, 2011 at 1:53 pm
Here is something quick and dirty. Hopefully it will give you some ideas on how to go after it.
PRINT 'Declare variable table'
DECLARE @employee TABLE
(id int
,emp_name varchar(20)
,emp_dept varchar(20)
,emp_loc varchar(20)
)
PRINT 'Insert values in the table'
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (1,'John', 'IT', 'Main');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (2,'Jill', 'HR', 'Main');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (3,'Jack', 'Branch', 'Cleveland');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (4,'Jan', 'Branch', 'Miami');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (5,'Jake', 'IT', 'Main');
PRINT 'Test the load'
SELECT * FROM @employee
PRINT 'Declare Search Variabls'
DECLARE @id int, @empName varchar(20), @empDept varchar(20), @empLoc varchar(20)
PRINT 'Search by id'
SET @id = 1
SET @empName = NULL
SET @empDept = NULL
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE ((DATALENGTH(@id) > 0) AND id = @id)
OR ((DATALENGTH(@empName) > 0) AND emp_name = @empName)
OR ((DATALENGTH(@empDept) > 0) AND emp_dept = @empDept)
OR ((DATALENGTH(@empLoc) > 0) AND emp_loc = @empLoc)
PRINT 'Search by emp_name'
SET @id = NULL
SET @empName = 'Jill'
SET @empDept = NULL
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE ((DATALENGTH(@id) > 0) AND id = @id)
OR ((DATALENGTH(@empName) > 0) AND emp_name = @empName)
OR ((DATALENGTH(@empDept) > 0) AND emp_dept = @empDept)
OR ((DATALENGTH(@empLoc) > 0) AND emp_loc = @empLoc)
PRINT 'Search by emp_dept'
SET @id = NULL
SET @empName = NULL
SET @empDept = 'Branch'
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE ((DATALENGTH(@id) > 0) AND id = @id)
OR ((DATALENGTH(@empName) > 0) AND emp_name = @empName)
OR ((DATALENGTH(@empDept) > 0) AND emp_dept = @empDept)
OR ((DATALENGTH(@empLoc) > 0) AND emp_loc = @empLoc)
PRINT 'Search by emp_loc'
SET @id = NULL
SET @empName = NULL
SET @empDept = NULL
SET @empLoc = 'Main'
SELECT *
FROM @employee
WHERE ((DATALENGTH(@id) > 0) AND id = @id)
OR ((DATALENGTH(@empName) > 0) AND emp_name = @empName)
OR ((DATALENGTH(@empDept) > 0) AND emp_dept = @empDept)
OR ((DATALENGTH(@empLoc) > 0) AND emp_loc = @empLoc)
PRINT 'Search by emp_name or emp_loc'
SET @id = NULL
SET @empName = 'Jill'
SET @empDept = NULL
SET @empLoc = 'Main'
SELECT *
FROM @employee
WHERE ((DATALENGTH(@id) > 0) AND id = @id)
OR ((DATALENGTH(@empName) > 0) AND emp_name = @empName)
OR ((DATALENGTH(@empDept) > 0) AND emp_dept = @empDept)
OR ((DATALENGTH(@empLoc) > 0) AND emp_loc = @empLoc)
July 1, 2011 at 1:59 pm
I'm sorry that was a bad example because I don't need to test for the datalength of the field.
This works also.
PRINT 'Declare variable table'
DECLARE @employee TABLE
(id int
,emp_name varchar(20)
,emp_dept varchar(20)
,emp_loc varchar(20)
)
PRINT 'Insert values in the table'
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (1,'John', 'IT', 'Main');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (2,'Jill', 'HR', 'Main');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (3,'Jack', 'Branch', 'Cleveland');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (4,'Jan', 'Branch', 'Miami');
INSERT INTO @employee (id,emp_name,emp_dept,emp_loc) values (5,'Jake', 'IT', 'Main');
PRINT 'Test the load'
SELECT * FROM @employee
PRINT 'Declare Search Variabls'
DECLARE @id int, @empName varchar(20), @empDept varchar(20), @empLoc varchar(20)
PRINT 'Search by id'
SET @id = 1
SET @empName = NULL
SET @empDept = NULL
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE (id = @id)
OR (emp_name = @empName)
OR (emp_dept = @empDept)
OR (emp_loc = @empLoc)
PRINT 'Search by emp_name'
SET @id = NULL
SET @empName = 'Jill'
SET @empDept = NULL
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE (id = @id)
OR (emp_name = @empName)
OR (emp_dept = @empDept)
OR (emp_loc = @empLoc)
PRINT 'Search by emp_dept'
SET @id = NULL
SET @empName = NULL
SET @empDept = 'Branch'
SET @empLoc = NULL
SELECT *
FROM @employee
WHERE (id = @id)
OR (emp_name = @empName)
OR (emp_dept = @empDept)
OR (emp_loc = @empLoc)
PRINT 'Search by emp_loc'
SET @id = NULL
SET @empName = NULL
SET @empDept = NULL
SET @empLoc = 'Main'
SELECT *
FROM @employee
WHERE (id = @id)
OR (emp_name = @empName)
OR (emp_dept = @empDept)
OR (emp_loc = @empLoc)
PRINT 'Search by emp_name or emp_loc'
SET @id = NULL
SET @empName = 'Jill'
SET @empDept = NULL
SET @empLoc = 'Main'
SELECT *
FROM @employee
WHERE (id = @id)
OR (emp_name = @empName)
OR (emp_dept = @empDept)
OR (emp_loc = @empLoc)
July 1, 2011 at 2:02 pm
i've always done this with dynamic SQL; it makes for better execution plans.
my "search function makes all searches with "starts with" for text fields, and exact matches for numbers.
so searching by name does LIKE 'Jane%' NOT '%Jane%' to take advantage of indexes...
then the end users were trainded to know if they wanted it to contain Jane, they have to type in the first percent sign: data entry of %Jan to find trojan for example
you can do it client side a lot easier, but the dynamic SQL can be SQL side as well.
Lowell
July 1, 2011 at 2:07 pm
July 1, 2011 at 4:27 pm
Pay close attention to the dangers of SQL injection attacks. Done carelessly, your query can create a huge security risk to the DB.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 1, 2011 at 9:09 pm
Thank you so much guys.
Unfortunately, I am not allowed to use EXECUTE or SP_EXECUTESQL statements on the db. So, is there any other way?
July 1, 2011 at 9:19 pm
rockstar283 (7/1/2011)
Thank you so much guys.Unfortunately, I am not allowed to use EXECUTE or SP_EXECUTESQL statements on the db. So, is there any other way?
Just curious, but did you ask why you are prohibited from using dynamic sql to solve this problem?
July 1, 2011 at 10:21 pm
I am currently workin as a TEMP in my company..and only permanent guys can execute a proc 🙁
July 2, 2011 at 6:14 am
rockstar283 (7/1/2011)
I am currently workin as a TEMP in my company..and only permanent guys can execute a proc 🙁
Bummer. The article is still relevant. Please see the section titled "Recompile". Adding the option to your query will cause it to be more expensive than would otherwise be the case without the option. The trade-off to get a proper execution plan every time the query is run will be well worth it in the long run. If there is a rule about temps not using query options let us know. There is one other option that will help you but it requires writing a lot of code to guarantee a proper execution plan in cases like this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2011 at 6:54 am
opc.three (7/2/2011)
Bummer. The article is still relevant. Please see the section titled "Recompile". Adding the option to your query will cause it to be more expensive than would otherwise be the case without the option. The trade-off to get a proper execution plan every time the query is run will be well worth it in the long run.
Only 2008 SP2+
In lower versions the recompile to fix this kind of query was either broken or removed (for fixing)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2011 at 7:55 am
GilaMonster (7/2/2011)
opc.three (7/2/2011)
Bummer. The article is still relevant. Please see the section titled "Recompile". Adding the option to your query will cause it to be more expensive than would otherwise be the case without the option. The trade-off to get a proper execution plan every time the query is run will be well worth it in the long run.Only 2008 SP2+
In lower versions the recompile to fix this kind of query was either broken or removed (for fixing)
R2 RTM?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2011 at 8:05 am
Think it works in R2. Not 100% sure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2011 at 10:36 am
Guys..after lots of hrs of convincing mgt..I got the permission to exec stored procedures..so using Dynamic SQL now 🙂
Thanks a lot to everyone for their help..I appreciate that 🙂
July 2, 2011 at 10:43 am
Excellent. Take a careful read through my blog post (referenced above) to make sure you're not opening yourself to SQL Injection vulnerabilities. There are ways to do this with dynamic SQL that do not have any injection vulnerabilities.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply