Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TSQL - Stored Procedure - help with Conditionals

Posted on 2011-03-16
14
Medium Priority
?
424 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 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