SQL Server:用所选数据从过程创建表

Question

我对SQL Server很陌生，一直在努力提高我的技能。今天我遇到了这个问题，它把我难住了。我可以返回我需要的产品，但我不确定如何使用过程中产品的日期/月/名称创建表。如果有人能帮助或引导我在正确的方向，我将不胜感激。

数据:

CREATE TABLE products 
(
id INTEGER NOT NULL PRIMARY KEY,
fruit VARCHAR(30) NOT NULL,
dateBought DATE NOT NULL
);
INSERT INTO products (id, fruit, dateBought) VALUES (0, 'Banana', '2021-07-01');
INSERT INTO products (id, fruit, dateBought) VALUES (1, 'Apple', '2021-06-23');
INSERT INTO products (id, fruit, dateBought) VALUES (2, 'Pear', '2021-01-11');
INSERT INTO products (id, fruit, dateBought) VALUES (3, 'Peach', '2021-08-01');
INSERT INTO products (id, fruit, dateBought) VALUES (4, 'Grape', '2021-08-02');

执行过程:

EXEC ProductsBought '2021-07-01'

预期输出:

day    month    name
----------------------
1      7        Banana
1      8        Peach 

存储过程:

CREATE PROCEDURE ProductsBought 
(@date DATE) 
AS
BEGIN
SELECT * 
FROM products 
WHERE dateBought >= @date 
AND dateBought <= DATEADD(MONTH, 1, @date);
END;

Answer 1

我假设您正在寻找要返回的结果集，而不是实际创建一个表——这是两件非常不同的事情。

获取日期的日期部分，在SQL Server中使用DATEPART。

在SSMS中执行如下命令:

-- Data mock-up.
DECLARE @products table (
id int NOT NULL PRIMARY KEY,
fruit varchar(30) NOT NULL,
dateBought date NOT NULL
);
INSERT INTO @products VALUES 
( 0, 'Banana', '2021-07-01' ),
( 1, 'Apple', '2021-06-23' ),
( 2, 'Pear', '2021-01-11' ),
( 3, 'Peach', '2021-08-01' ),
( 4, 'Grape', '2021-08-02' );
-- Date var.
DECLARE @date date = '07/01/2021';
-- Return resultset.
SELECT
DATEPART ( day, dateBought ) AS [day],
DATEPART ( month, dateBought ) AS [month],
fruit
FROM @products 
WHERE 
dateBought BETWEEN @date AND DATEADD( month, 1, @date );

返回

+-----+-------+--------+
| day | month | fruit  |
+-----+-------+--------+
|   1 |     7 | Banana |
|   1 |     8 | Peach  |
+-----+-------+--------+

Answer 2

您可以使用下面的过程来获取您正在寻找的内容:

Create PROCEDURE ProductsBought (@date DATE) AS
BEGIN
SELECT day(datebought)day,month(datebought)Month,fruit name FROM products WHERE dateBought >= @date AND dateBought <= DATEADD(month,1, @date);
END;

输出:

| day | month | name   |
----- ------- -------- 
|   1 |     7 | Banana |
|   1 |     8 | Peach  |