Click here to Skip to main content
15,939,065 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 28-Sep-15 3:58am
v2
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

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'
 
Share this answer
 
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
 
Share this answer
 
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)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900