如何从SQL Server返回空JSON数组

I have a database with some random data like:

我有一个数据库，其中包含一些随机数据：

Book:

书：

    Id uniqueidentifier,
    title nvarchar(255),
    author nvarchar(255),
    genre nvarchar (255),
    deleted datetime

I have many different books in my Book table. When I want to delete a book, I dont really delete it from database, but just set datetime for deleted, when I delete it.

我的书桌上有很多不同的书。当我想删除一本书时，我真的不会从数据库中删除它，只是在删除它时设置删除的日期时间。

Here is a case that bothers me.

这是一个困扰我的案例。

If user tries to look (search) for a book he deleted (which doesn't exist anymore), it needs to return empty array

如果用户试图查找（搜索）他删除的书（不再存在），则需要返回空数组

Here is what I've done, but it doesn't work:

这是我所做的，但它不起作用：

CREATE PROCEDURE dbo.Book_GetById
    @id uniqueidentifier
AS
BEGIN
    DECLARE @_deleted DATETIME
    SET @_deleted = NULL

    SELECT @_deleted  = Deleted 
    FROM Book 
    WHERE Id = @id

    IF (@_deleted IS NOT NULL) 
    BEGIN
        SELECT JSON_QUERY('[]')
        FOR JSON PATH

This is not the entire procedure, just the part where I want to return an empty JSON array by checking if deleted is not null, it means that there is a datetime set for that row - book deleted (if it is null, it is not deleted).

这不是整个过程，只是我想通过检查delete是否为空来返回空JSON数组的部分，这意味着为该行设置了一个日期时间 - 书被删除（如果它是null，则不是删除）。

PS1: if there is any other better way than empty array, id like to hear about it too!

PS1：如果还有其他比空数组更好的方法，id也喜欢听到它！

PS2: I need to "return" data in my unit testing to see if book is really deleted by checking Assert.IsNotNull(book.Deleted);

PS2：我需要在单元测试中“返回”数据，通过检查Assert.IsNotNull（book.Deleted）来查看是否真的删除了book。

When I run my debugger in unit testing and when I step over, my var books says null, which I think isn't really right I guess

当我在单元测试中运行我的调试器并且当我跳过时，我的var书称为null，我认为这是不对的我猜

1 个解决方案

#1

Since JSON is returned as NVARCHAR output, why not do it like this:

由于JSON作为NVARCHAR输出返回，为什么不这样做：

IF (@_deleted IS NOT NULL) 
BEGIN
    SELECT '[]'
END ELSE ....