通过存储过程从数据库中检索值

Question

我有一个数据库，其中包含名为'crigsStaffActivity'的表。我成功地检索到了值并将其显示出来。但结果不符合我的期望。

创建带值的表的脚本

CREATE TABLE [dbo].[crigsStaffActivity]
(
    [projectRef] [varchar](200) NOT NULL,
    [activityLine] [int] NOT NULL,
    [cost] [real] NOT NULL,
    [staffCost] [varchar](500) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[crigsStaffActivity] ([projectRef], [activityLine], [cost], [staffCost]) 
VALUES (N'MRC/CRIGS/330', 1, 1, N'ProjectLeader'),
       (N'MRC/CRIGS/330', 2, 2, N'Research Collaborators'),
       (N'MRC/CRIGS/330', 1, 1, N'Project Collaborators'),
       (N'MRC/CRIGS/330', 2, 2, N'Project Collaborators'),
       (N'MRC/CRIGS/330', 2, 3, N'Research/Project Assistant');

ALTER TABLE [dbo].[crigsStaffActivity] 
    ADD CONSTRAINT [DF__crigsStaff__cost__5535A963] DEFAULT ((0)) FOR [cost]
GO

表格截图：

我的存储过程：

ALTER PROCEDURE [dbo].[getCrigsStaffActivity] 
    @ProjectRef AS VARCHAR(1000),
    @staffCost AS varchar(1000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @val AS VARCHAR(2000);
    SET @val='';

    DECLARE @temp AS VARCHAR(2000);
    DECLARE @activityLine AS VARCHAR(2000);
    DECLARE @cost AS VARCHAR(2000);
    DECLARE @ref as CURSOR;   

    SET @ref = CURSOR FOR
        SELECT activityLine, cost
        FROM crigsStaffActivity
        WHERE projectRef = @ProjectRef
          AND staffCost = @staffCost

    OPEN @ref;

    FETCH NEXT FROM @ref INTO @activityLine, @cost;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM @ref INTO @activityLine, @cost;
        SET @val = CONCAT(@activityLine,',',@cost);
    END

    CLOSE @ref;
    DEALLOCATE @ref;

    SELECT @val AS ref;
END

执行存储过程后，我得到了以下结果：

ref
----
2,2

问题在于对于项目合作者，我有两个重复的值，因此我需要获得如下结果：

ref 
-----
1,1
2,2

我的问题是如何实现上述提到的结果。

这个存储过程的更好结果可能是：

ref
-----------------------------
1,1,ProjectLeader
2,2,Research collaborators
1,1,Project collaborators
2,2,Project collaborators
2,3,Research/Project Assistant

- Jay Soorjun

---

但是结果并不符合我的期望。毫无疑问，但是你没有解释你想要做什么。你的问题缺少一个问题。 - Gordon Linoff

看起来你正在使用 Microsoft SQL Server 作为你的数据库。存储过程不是标准 SQL，而是专有的。每个数据库提供商都使用不同的语法。因此，我建议你在问题中添加一个标签，指示你正在使用哪个数据库。话虽如此，从你发布的代码来看，似乎你想让你的存储过程返回一个结果集。我没有使用 Microsoft SQL Server 存储过程的经验，所以无法向你展示如何做到这一点，但我相信互联网搜索会为你提供一个合适的答案。搜索“SQL Server 存储过程返回结果集”。 - Abra

当我执行存储过程时，我得到了这个结果：使用什么参数值来执行存储过程？ - Tab Alleman

Answer 1

如果我理解正确，你不需要使用光标。

ALTER PROCEDURE [dbo].[getCrigsStaffActivity] 
    @ProjectRef AS VARCHAR(1000),
    @staffCost AS varchar(1000)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT CONCAT(activityLine,',', cost,',', staffCost) as ref
        FROM crigsStaffActivity
        WHERE projectRef = @ProjectRef
          AND staffCost = @staffCost


END

- Serkan Arslan

Answer 2

请尝试这个过程。

USE [***]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getCrigsStaffActivity] 
@ProjectRef as VARCHAR(1000),
@staffCost as varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
Declare @val as varchar(2000);
set @val='';
Declare @temp as Varchar(2000);
Declare @activityLine as Varchar(2000);
Declare @cost as Varchar(2000);
DECLARE @ref as CURSOR;

SET @ref = CURSOR FOR
SELECT DISTINCT activityLine, cost
FROM crigsStaffActivity
where projectRef = @ProjectRef
and staffCost = @staffCost

OPEN @ref;

FETCH NEXT FROM @ref INTO @activityLine, @cost;

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @ref INTO @activityLine, @cost;
set @val = CONCAT(@activityLine,',',@cost);
END

CLOSE @ref;
DEALLOCATE @ref;

select @val as ref;
END

- Hemang A