Solved

TSQL - Stored Procedure - help with Conditionals

Posted on 2011-03-16
14
376 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:ewangoya
ewangoya earned 225 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:ewangoya
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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:ewangoya
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 40

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 40

Accepted Solution

by:
Sharath earned 275 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:ewangoya
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now