CREATE PROCEDURE [dbo].[PRO_CHK_COLUMNS_VALUES]
-- Add the parameters for the stored procedure here
@TableName nvarchar(50) ,
@column_name nvarchar(50) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declare the return variable here
DECLARE @list_column_name nvarchar(50)
DECLARE @sql nvarchar(1000)
DECLARE @count int
declare cur cursor for --定义游标
select name from sys.columns where object_id=OBJECT_ID(''+@TableName+'')
open cur--打开游标
fetch next from cur into @list_column_name --把提取操作的列数据放到局部变量中
while(@@fetch_status=0) --游标读取下一条数据是否成功
begin
SET @sql='select @count = COUNT(1) from '+@TableName +' where '+@list_column_name +' is null '
exec sp_executesql @sql,N'@count nvarchar(50) out ',@count out
if @count > 0
begin
SELECT @column_name=@list_column_name
break
end
fetch next from cur into @list_column_name --读取下一条数据记录放到局部变量中,变量的数目必须与游标选择列表中的列的数目一致
end
close cur--关闭游标
deallocate cur--删除游标
END
SQLSERVER存储过程查找数据表中某列存在空值
最新推荐文章于 2023-03-17 11:28:11 发布