How to write case Statements in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

DECLARE @SEARCH_STR NVARCHAR(4000)

Select @SEARCH_STR = ''


set @SEARCH_STR= 'column1,column2,column3,column5,column4
case when Column6=1 then 'One'
when Column6-2 the 'two'
end column7
where column8=10

in this statement iam unable to write case statement, suggest me how to write case in
string statements.

Posted 28-Sep-15 3:44am

Baroor

Updated 28-Sep-15 3:58am

Naveen.Sanagasetti

v2

Add a Solution

Comments

Wendelius 28-Sep-15 9:56am

Could you explain the requirements with example data

Baroor 28-Sep-15 10:04am

set @SEARCH_STR='SELECT a.invoice_id , a.InvoiceNo, a.InvoiceDate, DBO.GET_DATE(b.fromdate) as fromdate,DBO.GET_DATE(b.todate) as todate,
DBO.GET_CLIENT_NAME(CLIENTID) AS CLIENT_NAME,CLIENTID,
DBO.INV_GET_INVSTATUS (STATUS) AS STATUS,convert(varchar(12),dueDate,101) as duedate, netamount as totalamount, TotalPayment,
a.purchaseorder_id,ISNULL(DBO.GET_EMP_NAME(B.consultant_id),'''') as Emp_name,
b.Item_id,b.PO_LINE_NO , DBO.GET_TP_INV_DOC(a.invoice_id) as Doc_Pres,
'CASE WHEN @VENDOR_ID < -1 THEN dbo.GET_VENDOR_NAME(a.VENDORID)
WHEN @VENDOR_ID > 0 THEN dbo.GET_VENDOR_NAME(@VENDOR_ID)
END' vendorname

from TB_TP_INVOICE_MASTER as a left outer join TB_TP_INVOICE_ITEMS as b on a.invoice_id = b.invoice_id
where status <=2

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chetan Naithani · Answer 1 · 2015-09-28T04:10:00

Solution 1

You are missing quotes -

SQL

Select @SEARCH_STR = ''
 

set @SEARCH_STR= '
column1,column2,column3,column5,column4
case 
when Column6=1 then ''One''
when Column6=2 then ''two'' end column7
where column8=10'

Posted 28-Sep-15 4:10am

Chetan Naithani

Member 12016701 · Answer 2 · 2015-09-28T04:10:00

Not 100% sure what you mean in your question but i will provide the basic CASE syntax. Please note this is for SQL SERVER (MSSQL) and my be incorrect for any other version of SQL.

SQL SERVER :

SQL

CASE 
  WHEN Table.Column = 1 THEN 'Value 1'
  WHEN Table.Column = 2 THEN 'Value 2'
  WHEN Table.Column = 3 THEN 'Value 3'
  ELSE 'Any other Value'
END

Naveen.Sanagasetti · Answer 3 · 2015-09-28T04:15:00

Whatever you tried that is dynamic querying in SQL Server while write dynamic querying you should be very careful; I will share you the sample dynamic query, please refer this and implement the in your project.

SQL

create table #tab
(
   id varchar(100),
   firstname varchar(100),
   lastname varchar(100),
   addres varchar(1000)
)

insert into #tab
values(1,'naveen','s','ssddsdds'),(2,'hhhhh','s','ssddsdds'),(3,'karthick','h','sfdsf'),(4,'pawan','k','sdasdffs')

select id, firstname, lastname, addres, case when id = 1 then 'One' end from #tab 

Declare @search NVARCHAR(4000)

set @search='select id, firstname, lastname, addres, case when id = 1 then ''One'' end from #tab ' 
Print(@search)
exec(@search)

How to write case Statements in sql

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0