|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Help - dynamically change where conditionI am trying to modify the where condition of this query based on certain
values that will be passed to this query. I get this error Incorrect syntax near '!'. select emp_id, emp_name from employee where emp_code = 'FIN' and case when ('889977' != '') then (emp_id != '889977') else (1=1) end *889977 is a string that will be inserted into this query dynamically by the application. I am afraid you can only do syntax like this:
select emp_id, emp_name from employee where emp_code = 'FIN' and emp_id != case when ('889977' != '') then ( '889977') end Show quote "Mike" <M***@discussions.microsoft.com> wrote in message news:C4AD144B-96C2-4E84-AB18-2B9B9443A357@microsoft.com... > I am trying to modify the where condition of this query based on certain > values that will be passed to this query. I get this error > > Incorrect syntax near '!'. > > > select emp_id, > emp_name > from employee > where emp_code = 'FIN' > and > case when ('889977' != '') then (emp_id != '889977') else (1=1) end > > > > *889977 is a string that will be inserted into this query dynamically by the > application. Mike ,how about
if @var <>'' begin select emp_id, emp_name from employee where emp_code = 'FIN' and emp_id = @var end Show quote "Mike" <M***@discussions.microsoft.com> wrote in message news:C4AD144B-96C2-4E84-AB18-2B9B9443A357@microsoft.com... >I am trying to modify the where condition of this query based on certain > values that will be passed to this query. I get this error > > Incorrect syntax near '!'. > > > select emp_id, > emp_name > from employee > where emp_code = 'FIN' > and > case when ('889977' != '') then (emp_id != '889977') else (1=1) end > > > > *889977 is a string that will be inserted into this query dynamically by > the > application. Pass the search string as a parameter to a procedure; AVOID dynamic
SQL!! SELECT emp_id, emp_name FROM Personnel -- use class or collective name for a table WHERE emp_code = 'FIN' AND @my_emp_id = COALESCE (@my_emp_id, emp_id); Pass a NULL when you want all of the employees. Never use an empty string for a missing value. It needs to a NULL or an explicit missing value code. Also that "!=" is not SQL; we use "<>" in Standard SQL -- that makes you look like a BASIC, C, C++, VB programmer. Yuch! > SELECT emp_id, emp_name Now consider this query, emp_id could well be a unique value, whereas > FROM Personnel -- use class or collective name for a table > WHERE emp_code = 'FIN' > AND @my_emp_id = COALESCE (@my_emp_id, emp_id); emp_code, there might only be 5 emp_codes. The plan the above query will get is terrible, even if you had an index on emp_code it will still take a good deal of processing. If you wrote it using IF ELSE you could get a good plan... IF @my_emp_id IS NULL SELECT emp_id, emp_name FROM Personnel -- use class or collective name for a table WHERE emp_code = 'FIN' ELSE SELECT emp_id, emp_name FROM Personnel -- use class or collective name for a table WHERE emp_code = 'FIN' AND emp_id = @my_emp_id If you pass @my_emp_id then, assuming an index on emp_id you would get an index seek and a very quick and performant query. I've seen from other posts you would not use IF ELSE, that seems inconsistent with your book; but just how would you get round this problem? Would you code multiple stored procedures? In this case there are only two branches, but what if there where 10 optional parameters - that is a lot of stored procedures all because you won't use IF ELSE. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137737287.697850.159150@z14g2000cwz.googlegroups.com... > Pass the search string as a parameter to a procedure; AVOID dynamic > SQL!! > > SELECT emp_id, emp_name > FROM Personnel -- use class or collective name for a table > WHERE emp_code = 'FIN' > AND @my_emp_id = COALESCE (@my_emp_id, emp_id); > > Pass a NULL when you want all of the employees. Never use an empty > string for a missing value. It needs to a NULL or an explicit missing > value code. > > Also that "!=" is not SQL; we use "<>" in Standard SQL -- that makes > you look like a BASIC, C, C++, VB programmer. Yuch! > Thanks Tony,
I am now using IF - ELSE. this solved my problem. Show quote "Tony Rogerson" wrote: > > SELECT emp_id, emp_name > > FROM Personnel -- use class or collective name for a table > > WHERE emp_code = 'FIN' > > AND @my_emp_id = COALESCE (@my_emp_id, emp_id); > > Now consider this query, emp_id could well be a unique value, whereas > emp_code, there might only be 5 emp_codes. > > The plan the above query will get is terrible, even if you had an index on > emp_code it will still take a good deal of processing. > > If you wrote it using IF ELSE you could get a good plan... > > IF @my_emp_id IS NULL > SELECT emp_id, emp_name > FROM Personnel -- use class or collective name for a table > WHERE emp_code = 'FIN' > > ELSE > SELECT emp_id, emp_name > FROM Personnel -- use class or collective name for a table > WHERE emp_code = 'FIN' > AND emp_id = @my_emp_id > > > If you pass @my_emp_id then, assuming an index on emp_id you would get an > index seek and a very quick and performant query. > > I've seen from other posts you would not use IF ELSE, that seems > inconsistent with your book; but just how would you get round this problem? > Would you code multiple stored procedures? In this case there are only two > branches, but what if there where 10 optional parameters - that is a lot of > stored procedures all because you won't use IF ELSE. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1137737287.697850.159150@z14g2000cwz.googlegroups.com... > > Pass the search string as a parameter to a procedure; AVOID dynamic > > SQL!! > > > > SELECT emp_id, emp_name > > FROM Personnel -- use class or collective name for a table > > WHERE emp_code = 'FIN' > > AND @my_emp_id = COALESCE (@my_emp_id, emp_id); > > > > Pass a NULL when you want all of the employees. Never use an empty > > string for a missing value. It needs to a NULL or an explicit missing > > value code. > > > > Also that "!=" is not SQL; we use "<>" in Standard SQL -- that makes > > you look like a BASIC, C, C++, VB programmer. Yuch! > > > > > if coalesce( @emp_id, '' ) = ''
select emp_id, emp_name from employee where emp_code = 'FIN' else select emp_id, emp_name from employee where emp_code = 'FIN' and emp_id <> @emp_id Show quote "Mike" <M***@discussions.microsoft.com> wrote in message news:C4AD144B-96C2-4E84-AB18-2B9B9443A357@microsoft.com... >I am trying to modify the where condition of this query based on certain > values that will be passed to this query. I get this error > > Incorrect syntax near '!'. > > > select emp_id, > emp_name > from employee > where emp_code = 'FIN' > and > case when ('889977' != '') then (emp_id != '889977') else (1=1) end > > > > *889977 is a string that will be inserted into this query dynamically by > the > application.  |
|||||||||||||||||||||||
Other interesting topics