SQL add filter only if a variable is not null

You can translate your requirement into : SELECT route_id [ROUTE_ID] FROM route_master(NOLOCK) WHERE route_ou = 2 AND (@l_s_query is null OR route_query = @l_s_query) AND lang_id = 1 OPTION (RECOMPILE) The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on … Read more

How to automatically generate unique id in SQL like UID12345678?

The only viable solution in my opinion is to use an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value a computed, persisted column to convert that numeric value to the value you need So try this: CREATE TABLE dbo.tblUsers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY … Read more

Oracle IN vs Exists difference?

simply put, EXISTS is usually used for checking whether rows that meet a criteria exist in another (or the same) table. your SQL using EXISTS would look like this: select * from emp e where exists(select * from emp e2 where e.empno = e2.empno and e2.ename in (‘smith’, ‘brown’, ‘john’, ‘johnson’)) so you can see … Read more

mysql unique number generation

While it seems somewhat awkward, this is what can be done to achieve the goal: SELECT FLOOR(10000 + RAND() * 89999) AS random_number FROM table WHERE random_number NOT IN (SELECT unique_id FROM table) LIMIT 1 Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present … Read more

Sql query for tree table

Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question. declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,’Category1′ insert @t select 2, 0, ‘Category2’ insert @t select 3, 1, ‘Category3’ … Read more

Execute multiple semi-colon separated query using mysql Prepared Statement

No, it is not possible. PREPARE / EXECUTE stmt can execute only one query at a time, many statements cannot be combined. See documentation: http://dev.mysql.com/doc/refman/5.0/en/prepare.html … a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Anyway, to simplify your code I would create … Read more

mySQL SELECT upcoming birthdays

To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days. SELECT * FROM persons WHERE DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); If … Read more

How to avoid OOM (Out of memory) error when retrieving all records from huge table?

With a little more information I can get a more helpful answer. If you are using MySQL: stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); from http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html: java.util.Properties info = new java.util.Properties(); info.put (“user”, “scott”); info.put (“password”,”tiger”); info.put (“defaultRowPrefetch”,”15″); getConnection (“jdbc:oracle:oci:@”,info);