if object_id('a') is not null drop table a go create table a(姓名 varchar(10),性别 varchar(10)) insert a select '张三','男' insert a select '李四','男' insert a select '王五','女' goif object_id('sp') is not null drop proc sp go create proc sp as set nocount on if object_id('b') is null create table b(姓名 varchar(10),性别 bit) insert b select 姓名,case 性别 when '男' then 0 else 1 end from a t where not exists(select 1 from b where t.姓名=姓名 and case t.性别 when '男' then 0 else 1 end=性别) goexec spselect * from b /* 姓名 性别 ---------- ----- 张三 0 李四 0 王五 1(3 行受影响) */
case 性别 when ‘男’ then ‘0’ when ‘女’ then ‘1’end
if object_id('p1') is not null drop proc p1 go create proc p1 as begin insert into t2 select 姓名,性别=case 性别 when '男' then 0 else 1 end from t1 end
我是这样写的:select @select = 'insert into [' + @tableName +'] select userName ,case userSex when 男 then 0 else 1 end from userA ' 为什么 执行存储过程时提示:消息 207,级别 16,状态 1,第 1 行 列名 '男' 无效。 什么原因?
select @select = 'insert into [' + @tableName +'] select userName ,case userSex when ''男'' then 0 else 1 end from userA ' "男"没加引号
这样: select @select = 'insert into [' + @tableName +'] select userName ,case userSex when ''男'' then 0 else 1 end from userA '
drop table a
go
create table a(姓名 varchar(10),性别 varchar(10))
insert a select '张三','男'
insert a select '李四','男'
insert a select '王五','女'
goif object_id('sp') is not null
drop proc sp
go
create proc sp
as
set nocount on
if object_id('b') is null
create table b(姓名 varchar(10),性别 bit)
insert b
select 姓名,case 性别 when '男' then 0 else 1 end from a t
where not exists(select 1 from b where t.姓名=姓名 and case t.性别 when '男' then 0 else 1 end=性别)
goexec spselect * from b
/*
姓名 性别
---------- -----
张三 0
李四 0
王五 1(3 行受影响)
*/
case 性别 when ‘男’ then ‘0’ when ‘女’ then ‘1’end
drop proc p1
go
create proc p1
as
begin
insert into t2
select 姓名,性别=case 性别 when '男' then 0 else 1 end
from t1
end
为什么 执行存储过程时提示:消息 207,级别 16,状态 1,第 1 行
列名 '男' 无效。
什么原因?
select @select = 'insert into [' + @tableName +'] select userName ,case userSex when ''男'' then 0 else 1 end from userA '