?
Solved

TSQL - Stored Procedure - help with Conditionals

Posted on 2011-03-16
14
Medium Priority
?
412 Views
Last Modified: 2012-08-13
Experts, I'm trying to alter a stored procedure which optionally accepts to parameters (a filter, and column to filter).

I'm not sure how to write the conditional logic. In the application, I would write the query as:

SELECT *
FROM
WHERE this = 'that'
if X is not null and y is not null then
      AND x LIKE '%y%'

However, I'm not sure what the syntax is in tsl for a stored procedure.

Additionally, I can either pass or not pass the arguments to the stored procedure based on a value check, if that helps.

Correct syntax would also be greatly appreciated. Thanks!
0
Comment
Question by:coldchillin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 900 total points
ID: 35152660

you would use dynamic sql
declare @sql varchar(max)

set @sql = 'SELECT * FROM TABLEX WHERE this = ''that' + ''
if @X is not null and @Y is not null 
  set @sql = @sql + 'AND ' + @X + ' LIKE ''%' + @Y + '%'''
  
exec(@sql)

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35152677
Oops, my quotations are not right


declare @sql varchar(max)

set @sql = 'SELECT * FROM TABLEX WHERE this = ''that'' '
if @X is not null and @Y is not null 
  set @sql = @sql + 'AND ' + @X + ' LIKE ''%' + @Y + '%'''
  
exec(@sql)

Open in new window

0
 

Expert Comment

by:robgarner777
ID: 35152690
You can use a couple of techniques where @X and @Y are declared variables

select *
from tableName
where
   fieldName =
   CASE
       WHEN @X IS NOT NULL AND @Y IS NOT NULL THEN 'value2'
       WHEN @X IS NULL AND @Y IS NOT NULL THEN 'value2'
       ELSE 'value3'
   END;

select *
from tableName
where
   (
   @X IS NULL
   OR
    fieldName = @X
   OR
    fieldName like '%' + @x + '%'
   )
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 1

Author Comment

by:coldchillin
ID: 35152754
The condition is whether to query all, or to add a where clause:

I tried both, and had issues with both....Here's what I'm doing:


With table_paged AS (
 SELECT * FROM tableA
-- if @column is not null and @filter is not null
WHERE @column LIKE @filter
)

SELECT * FROM table_paged
/* from this row to that row */

0
 
LVL 1

Author Comment

by:coldchillin
ID: 35152766
let me also add this - @column is the column, and @filter is the filter to sort by:

So it would pass

@column = 'firstname'
@filter = 'bob'

it seems dynamic sql might be a better option...
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35152785
@ewangoya

I'm having a problem here:

WITH table_paged AS
(
      exec(@sql)
)

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35152809

Why exactly are you using a WITH statement?

Can you show your stored procedure code
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35152862
I'm using a common table expression to accomplish a record paging.

Here's what I'm hung up on now, exposing the variable as the column name in the where clause

@column = 'firstname'
@filter = 'bob'

This works:
SELECT * FROM WHERE firstname LIKE '%' + @filter + '%'

But this doesn't
SELECT * FROM WHERE @column LIKE '%' + @filter + '%'
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35152873
Sample below
DECLARE	
	@FirstRow INT,
	@LastRow INT,
	@PageSize INT,
	@PageNumber INT,
	@columnfilter varchar(20),
	@filter varchar(20)

SET	@PageSize = 20;
SET	@PageNumber =3;
SET @columnfilter = 'Dept';
SET @filter = 'Mor';
SET	@FirstRow = ( @PageNumber - 1) * @PageSize + 1;
SET	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH tblPaged  AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY State, Dept, TruckType) AS RowNumber
	FROM tableA
	WHERE Dept LIKE '%' + @filter + '%'
)

SELECT *
FROM tblPaged
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35152881
you have to try like this.
declare @column varchar(100),@filter varchar(100)
declare @sql nvarchar(2000)
select @column = 'firstname',@filter = 'bob'
select @sql = 'select * from your_table where ' + @column + ' like ''%' + @filter + '%'''  
exec (@sql)

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 1100 total points
ID: 35152902
In your case, you can try like this.
DECLARE	
	@FirstRow INT,
	@LastRow INT,
	@PageSize INT,
	@PageNumber INT,
	@columnfilter varchar(20),
	@filter varchar(20)

SET	@PageSize = 20;
SET	@PageNumber =3;
SET @columnfilter = 'Dept';
SET @filter = 'Mor';
SET	@FirstRow = ( @PageNumber - 1) * @PageSize + 1;
SET	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

declare @sql nvarchar(max)
set @sql = 
';WITH tblPaged  AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY State, Dept, TruckType) AS RowNumber
	FROM tableA
	WHERE ' + @columnfilter + ' LIKE ''%' + @filter + '%''
)

SELECT *
FROM tblPaged
WHERE	RowNumber BETWEEN ' + convert(varchar,@FirstRow) + ' AND ' + convert(varchar,@LastRow) + 
' ORDER BY RowNumber ASC;'
exec(@sql)

Open in new window

0
 
LVL 1

Author Comment

by:coldchillin
ID: 35153012
@Sharath 123

Bingo!

Here is the full version including dynamic where clause. Do spaces, carriage returns, etc. have any impact on overhead?
DECLARE	
	@FirstRow INT,
	@LastRow INT,
	@PageSize INT = 40,
	@PageNumber INT = 1,
	@columnfilter varchar(20) = 'Dept',
	@filter varchar(20) = 'Morg',
	@sql nvarchar(max) = null

SET	@FirstRow = ( @PageNumber - 1) * @PageSize + 1;
SET	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
SET @sql = 
';WITH tblPaged  AS (
	SELECT *, ROW_NUMBER() OVER (ORDER BY State, Dept, TruckType) AS RowNumber
	FROM tableA';
if @columnfilter is not null and @filter is not null	
set @sql = @sql + ' WHERE ' + @columnfilter + ' LIKE ''%' + @filter + '%''';
set @sql = @sql + ') 
SELECT *
FROM tblPaged
WHERE	RowNumber BETWEEN ' + convert(varchar,@FirstRow) + ' AND ' + convert(varchar,@LastRow) + 
'ORDER BY RowNumber ASC;'

exec(@sql)

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35153051

Whenever I take a break, someone will answer the questions LOL

Spaces and carriage returns have no impact nor do they add overhead
0
 
LVL 1

Author Closing Comment

by:coldchillin
ID: 35161125
I'm saving my own comment as part of the solution as it is the final implementation including the conditional logic to display the where clause. [no points]
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question