Geek Answers Handbook

Use statement in select statement using case sql server2008

I create a select statement to compare between a man and a woman and replace some values. I have two tables.

Table 1:

  • Male (True or false)
  • Female (True or false)

Table 2:

  • Gender values ​​('M', 'F', 'B')

I want to create a select statement from table 2 to table 1. My query is as follows,

select 'insert into table1(Male,female)values('+ Isnull(cast(case Gender when 'M' or 'B' Then '''True''' else '''false''' end as varchar),'NULL')+','+ Isnull(cast(case Gender when 'F' or'B' Then '''True''' else '''false''' End as varchar),'NUll')+')' from Table2

But I get an error or statement not using here. How to use the operator to select both values. Does anyone suggest me ...

+4
source share
3 answers

You can try the following:

 select 'insert into table1(Male,female)values('+ Isnull(cast(case when Gender = 'M' or Gender = 'B' Then '''True''' else '''false''' end as varchar),'NULL')+','+ Isnull(cast(case when Gender = 'F' OR Gender = 'B' Then '''True''' else '''false''' End as varchar),'NUll')+')' from Table2

or like this:

 select 'insert into table1(Male,female)values('+ Isnull(cast(case Gender when 'M' Then '''True''' when 'B' Then '''True''' else '''false''' end as varchar),'NULL')+','+ Isnull(cast(case Gender when 'F' Then '''True''' when 'B' Then '''True''' else '''false''' End as varchar),'NUll')+')' from Table2
+2
source

To use dynamic SQL, you need to declare a variable. It will also help you with code testing.

 DECLARE @dml nvarchar(max) = N'' select @dml += 'insert into table1(Male,female)values('+ Isnull(cast(case when Gender IN('M', 'B') Then '''True''' else '''false''' end as varchar),'NULL')+','+ Isnull(cast(case when Gender IN('F', 'B') Then '''True''' else '''false''' end as varchar),'NUll')+')' + CHAR(13) + CHAR(10) from Table2 PRINT @dml
+2
source 
 select 'insert into table1(Male,female)values('+ Isnull(cast(case Gender when 'M' or Gender when 'B' Then '''True''' else '''false''' end as varchar),'NULL')+','+ Isnull(cast(case Gender when 'F' or Gender when 'B' Then '''True''' else '''false''' End as varchar),'NUll')+')' from Table2

try this query.

Hope this helps.

+1
source

More articles:


All Articles