Improve company productivity with a Business Account.Sign Up

x
?
Solved

TSQL - Stored Procedure - help with Conditionals

Posted on 2011-03-16
14
Medium Priority
?
435 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
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

606 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