{"id":2861,"date":"2012-05-19T17:12:27","date_gmt":"2012-05-19T11:42:27","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=2861"},"modified":"2012-05-19T17:12:27","modified_gmt":"2012-05-19T11:42:27","slug":"pagination-and-switch-case-in-where-clause-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/pagination-and-switch-case-in-where-clause-sql-server\/","title":{"rendered":"Pagination and Switch Case in Where clause – SQL Server"},"content":{"rendered":"

After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i\u00a0caught\u00a0in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword<\/span> available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…<\/p>\n

I am considering that there will be table named “Employee” in which i want to search by employees firstName<\/span> with pagesize<\/span> and offset<\/span> attribute.<\/p>\n

Table Structure for example:<\/strong><\/span><\/p>\n

\"SQL<\/a>
SQL Server Employee Table<\/figcaption><\/figure>\n

Create Stored Procedure:<\/strong><\/span><\/p>\n

Below stored procedure demonstrates pagination<\/strong> as well as switch case<\/strong> in where clause.<\/p>\n

\n-- Author\t: Jitendra Zaa\n-- Date\t\t: 19 May 2012\n-- Summary\t: Search Employee on the basis of fName and lName and if the lName is blank then set\n--\t\t\t: default lName to search is 'Zaa'. Also there shpuld be support for pagination\nCREATE PROCEDURE dbo.SearchEmployee\n(\n\t@fName varchar(50), -- First Name to Search\n\t@lName varchar(50), -- Last Name to search, and if its blank then search for 'Zaa'\n\t@offset INT,\t\t-- Starting rowNumber of record\n\t@pageSize INT\t\t-- Number of records in Row\n)\nAS\n\tBEGIN\n\n\t\tSELECT * FROM\n\t\t\t(SELECT ROW_NUMBER() OVER(ORDER BY EmpId) AS row, --Generate Row Number\n\t\t\tEmpId,\n\t\t\tFirstName,\n\t\t\tLastName\n\t\tFROM\n\t\t\tEmployee ) as e\n\t\tWHERE\n\t\t\te.FirstName LIKE '%'+@fName+'%'\n\t\t\tAND\n\t\t\te.LastName LIKE\n\t\t\tCASE\n\t\t\t\tWHEN @lName <> '' THEN '%'+@lName+'%'\n\t\t\t\tELSE 'Zaa'\n\t\t\tEND\n\t\t\tAND\n\t\t\t\te.row BETWEEN @offSet AND (@offSet + @pageSize) --Pagination Logic\n\tEND\n<\/pre>\n
As you can see in above example, i have used “over” clause to generate the row Number. You can read more on over clause from here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Example of using pagination and switch case in where clause in SQL server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13,14],"tags":[333,292],"class_list":["post-2861","post","type-post","status-publish","format-standard","hentry","category-sql","category-sqlserver","tag-sql","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1006,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/case-statement-in-where-clause-sql-server-conditional-where-clause\/","url_meta":{"origin":2861,"position":0},"title":"Case statement in Where Clause – SQL Server – Conditional Where clause","author":"Jitendra","date":"September 9, 2010","format":false,"excerpt":"Using Case statement in Where Clause in SQl Server. This script is used to create the conditional where clause","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"Case statement in Where Clause","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/Case-statement-in-Where-Clause.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":454,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/drop-stored-procedure-if-exist-in-sql-server\/","url_meta":{"origin":2861,"position":1},"title":"Drop Stored Procedure if exist in SQL Server","author":"Jitendra","date":"June 30, 2010","format":false,"excerpt":"How to drop the stored procedure in SQL Server if exist","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":2861,"position":2},"title":"SQL Server – Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5824,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-create-folders-using-t-sql-ole-automation\/","url_meta":{"origin":2861,"position":3},"title":"SQL Server \u2013 Create Folders using T-SQL & OLE automation","author":"Jitendra","date":"November 24, 2016","format":false,"excerpt":"Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server \u2013 Create Folders using T-SQL & OLE automation","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":5873,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","url_meta":{"origin":2861,"position":4},"title":"Export Documents saved as Blob \/ Binary from SQL Server","author":"Jitendra","date":"December 24, 2016","format":false,"excerpt":"T-SQL Scripts to Export Blob or Binary data stored in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Export Blob as File","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":504,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/drop-function-if-exist-sql-server\/","url_meta":{"origin":2861,"position":5},"title":"Drop Function if exist – SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Drop the user define function from database if it exists.","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/2861","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=2861"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/2861\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=2861"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=2861"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=2861"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}