May 6, 2010 at 11:13 pm
Hi
i have query that have input parameters in the where condition of dynamic sql. the problem is, i have 10 input parameters from application.
when data was inserted any of the parameter, i need to keep them in where condition of a dynamic query with AND operation.
ex: input parameters for the query
userId
username
job
companyname
isactive
.
.
.
.
.
upto 10 parameters. when they enter data in any parameter i need to append the parameter in the where condition
sample Query i did:
declare @sql varchar(2000),@cmd varchar(2000)
set @sql='select * from jobspec where'''+@cmd
when userId data is entered, iam doing
set@cmd='userId='+@userid
when userId data and username data is entered
set @cmd='userId='+@userid +'''and username='''+@username
if i use like this i will get many number of combinations
is there any other solution for this scenario,please kindly help me
Thanks
Rock.
May 6, 2010 at 11:27 pm
rockingadmin (5/6/2010)
Hii have query that have input parameters in the where condition of dynamic sql. the problem is, i have 10 input parameters from application.
when data was inserted any of the parameter, i need to keep them in where condition of a dynamic query with AND operation.
ex: input parameters for the query
userId
username
job
companyname
isactive
.
.
.
.
.
upto 10 parameters. when they enter data in any parameter i need to append the parameter in the where condition
sample Query i did:
declare @sql varchar(2000),@cmd varchar(2000)
set @sql='select * from jobspec where'''+@cmd
when userId data is entered, iam doing
set@cmd='userId='+@userid
when userId data and username data is entered
set @cmd='userId='+@userid +'''and username='''+@username
if i use like this i will get many number of combinations
is there any other solution for this scenario,please kindly help me
Thanks
Rock.
Here is the start of what I have seen others do:
declare @sql varchar(max);
set @sql='select * from jobspec where 1 = 1';
if @userid is not null
set @sql = @sql + ' and userId = ' + @userid;
if @username is not null
set @sql = @sql + ' and username = ' + @username;
...
exec (@sql);
May 6, 2010 at 11:35 pm
This was removed by the editor as SPAM
May 6, 2010 at 11:40 pm
True, the method I showed is open for SQL Injection as is, but there are defensive means of handling it. Being late, and not having access to SQL Server at the moment, I can't do the necessary work and testing tobe sure I do it right.
I'm sure that someone a little more proficient in this can show you the way.
May 7, 2010 at 8:03 am
i tried with different scenario
ex:
declare @userid int,@username nvarchar(300),@companyname varchar(300),@job varchar(30)
select * from jobspec where userId=coalesce(@userid,userid) and username like coalesce(@username,username)+'%' and job=coalesce(@job,job),companyname like coalesce(companyname,@companyname)+'%'
.
.
.
.
it is working properly.But i have a problem, if drivername having any special characters. then if i tried to search with that name i am getting null values and if i tried to search with the userid of t he mentioned drivername of the same record i.e. who is having name with speacial characters then also i am getting null.
if i tried to search with user name as "test". i am getting values
if i tried to search with user name as "test #name# @ is name". iam getting null values.if i tried to search with userid for this record also i am gettting the same issue
please kindly suggest me on this
Thanks
Rock.
May 13, 2010 at 7:10 am
any guess..
May 14, 2010 at 6:33 am
rockingadmin (5/7/2010)
i tried with different scenarioex:
declare @userid int,@username nvarchar(300),@companyname varchar(300),@job varchar(30)
select * from jobspec where userId=coalesce(@userid,userid) and username like coalesce(@username,username)+'%' and job=coalesce(@job,job),companyname like coalesce(companyname,@companyname)+'%'
.
.
.
.
it is working properly.But i have a problem, if drivername having any special characters. then if i tried to search with that name i am getting null values and if i tried to search with the userid of t he mentioned drivername of the same record i.e. who is having name with speacial characters then also i am getting null.
if i tried to search with user name as "test". i am getting values
if i tried to search with user name as "test #name# @ is name". iam getting null values.if i tried to search with userid for this record also i am gettting the same issue
please kindly suggest me on this
Thanks
Rock.
First, to answer your question, unless you have a username that starts with 'test #name# @ is name' and has zero or some characters after (highly unlikely, I would think), then your query is correctly returning nothing.
Secondly, unless you have otherwise sanitized your input, from the information given you are opening yourself up to SQL injection, which was brought up in the responses above. Try using stewartc-708166's suggestion above to prevent that.
If you're not sure what the problem with SQL Injection is, please start by reading Gail Shaw's blog post here: http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/[/url]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 15, 2010 at 8:06 am
Hey 'Rock',
I'm not sure if you are the sort of person that reads links, but if you are, you'll find the following contain just about everything there is to know about building dynamic search conditions in SQL Server:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 18, 2010 at 5:24 am
Hi Paul,
its not the matter of reading the links. i already gone through those links. after that only i decided to use coalesce function. to my scenario i can only use
1. using if conditions:
if working with " if " condition, then i need to face lot of conditions.
2. using coalesce:
if working with " coalesce ", i am getting null values.
3. i can not use stored procedures and also using static if conditions.
Regards,
ROCK..
May 18, 2010 at 7:36 am
Rock, can you provide us with sample table structure, sample data, what you're trying and expected outcome? This will help us to understand your dilemma a little better. (See the first link in my signature for an article describing how to do that).
From what you're showing us so far, it looks like Stewartc's recommendation would work, but you're saying it is not. If we can play with your sample data we can probably help you figure this out.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 18, 2010 at 9:40 am
i found out the root cause of my problem. But need little help on this
ex:
declare @userid int,@username nvarchar(300),@companyname varchar(300),@job varchar(30)
select * from jobspec where userId=coalesce(@userid,userid) and username like coalesce(@username,username)+'%' and job=coalesce(@job,job),companyname like coalesce(companyname,@companyname)+'%'
it is not problem with the like operator or with a special characters
-->I am getting values if data is available for all values in the table
case 1:
if userId=123,username='test1',Job='dev',companyname='testcomp'
case 2:
if userId=1123,username='test12',Job=null,companyname=null
case 3:
If userId=123,username='test1',Job='',companyname=''
--> And I am not getting values for the belowscenarios
from the application, some field values are stored as null or '' for some fields in the table
for example if the data is available in the table with the below scenarios, i am getting null values
case 1:
if userid=1234,username='test',job=null,companyname=''
case 2:
if userid=12345,username=''test',job='',companyname=null
case 3:
if userid=123456,usernmae=null,job=null,companyname=''
please kindly suggest me
Thanks
Rock..
May 18, 2010 at 1:43 pm
it's because you're not correctly using coalesce(). You cannot simply find the first non-null value (which is what coalesce() or isnull() do) and make your column equal that, especially by using both your variable, and the column itself.
You're coming up with solutions that are only looking for data WHERE 1=1 and userid=userid and username=username and....etc..
Which is like looking for 1=1 and 2=2 and 3=3.
When you throw NULLs into the mix, you're trying to return job=NULL, which can't be determined.
You need to write your query in the manner that Stewartc suggested earlier in this thread in order to make it work.
step1 = build the string using dynamic SQL and the use of isnull() or coalesce() if you prefer to ONLY include the parameters in the WHERE if they are not null/empty string.
step2 = execute that constructed string in order to return the correct values.
Stewartc has basically written that for you already, you'll just have to complete what he started.
Give that a shot, and let us see what you have written if you can't get it working after that point.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 19, 2010 at 5:24 am
it works. Thank you very much
May 19, 2010 at 5:30 am
Thanks for the feedback, and thanks Stewartc for the solution.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 20, 2010 at 12:29 am
I thank each and every one for best supporting
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply