Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL - Stored Procedure - help with Conditionals

Posted on 2011-03-16
14
Medium Priority
?
428 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

564 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