如何将空值更新为指定值 [英] how to update null value to specified value

问题描述

朋友们，

嗨朋友
我有一个包含5列的表.前三列的插入值和4和5被设置为null.

这是我的存储过程

Hi friends,

Hi friends
I have a table which contain 5 columns .first three column insert with value and 4 and 5 were set to null.

This is my stored procedure

alter procedure sp_excelforgeneralholidays(@filname nvarchar(max),@getdate datetime,@adminid int)
 as 
 declare @datavar nvarchar(max)
 declare @sql varchar(1000)
 --set @getdate=coalesce(@getdate,Getdate())
 set @datavar  = ''Excel 8.0;Database='' + @filname
 
 set nocount on
  begin
  --create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
  set @sql =''insert into General_holydays_details(Date,Day,Reason) SELECT * FROM OPENROWSET(''''Microsoft.jet.OLEDB.4.0'''',''''''+@datavar+'';HDR=YES'''',''''SELECT Date,Day,Reason FROM [Sheet1$]'''')''
 
 
   exec (@sql)
   update General_holydays_details set createddatetime= @getdate where createddatetime=null
   update General_holydays_details set adminid=@adminid where adminid=null
  
 end

但是当我执行此存储过程时，仅更新excel文件，并且createddatetime和adminid保留为空值.
但是我通过编码将createddatetime值和adminid传递给了此存储过程.
有什么问题，请找出并帮我

but when i executing this stored procedure, only excel file is updated and createddatetime and adminid remain null value.
but i pass createddatetime value and adminid to this stored procedure from my coding.
What is the problem please find out and help me

推荐答案

'''')'' exec(@sql) 更新General_holydays_details设置createddatetime = @getdate，其中createddatetime = null 更新General_holydays_details设置adminid = @ adminid，其中adminid = null 结束

'''')'' exec (@sql) update General_holydays_details set createddatetime= @getdate where createddatetime=null update General_holydays_details set adminid=@adminid where adminid=null end

但是当我执行此存储过程时，仅更新excel文件，并且createddatetime和adminid保留为空值.
但是我通过编码将createddatetime值和adminid传递给了此存储过程.
有什么问题，请找出并帮助我

but when i executing this stored procedure, only excel file is updated and createddatetime and adminid remain null value.
but i pass createddatetime value and adminid to this stored procedure from my coding.
What is the problem please find out and help me

空值永远不会等于(或不等于)任何东西，因此您无法将其与普通运算符进行比较.

在这些情况下，SQL语言具有特殊的运算符IS NULL和IS NOT NULL.

因此您的程序应如下所示:

Null is never equal (or unequal) to anything so you cannot compare it with normal operators.

SQL language has special operators IS NULL and IS NOT NULL for these cases.

So your procedure should look like:

...
update General_holydays_details set createddatetime= @getdate where createddatetime is null
update General_holydays_details set adminid=@adminid where adminid is null
...

有关更多信息，请参考 IS [NOT] NULL [空(SQL) [

For more information, refer to IS [NOT] NULL[^] and Null (SQL)[^]

这篇关于如何将空值更新为指定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！