Solved

Case Statement SQL

Posted on 2011-03-18
3
292 Views
Last Modified: 2012-08-13
I want to check a parameter's length of text with a case statement, what is wrong with this?

CASE @pname WHEN (dbo.length(@pname) > 0) THEN ('arc.contact_name LIKE ''' + @pname + '%'' AND ') ELSE '' END

getting an error about the >
Incorrect syntax near '>'.

Thanks.
0
Comment
Question by:melegant99
  • 2
3 Comments
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35164403
No need of dbo.length instead use Len((@pname)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35164412
this should do:
CASE WHEN (dbo.length(@pname) > 0) THEN ('arc.contact_name LIKE ''' + @pname + '%'' AND ') ELSE '' END

Open in new window

0
 
LVL 10

Expert Comment

by:Asim Nazir
ID: 35164458
You can also go this way:

IF(Len(@pname) > 0)
            select 'arc.contact_name LIKE ''' + @pname + '%'' AND '
      ELSE
            select ''
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

680 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