Retrieve values from database through stored procedure

Question

I have this database with table 'crigsStaffActivity'. I managed to retrieve the values and display it. But the result is not showing as my expectation.

Script to create table with values

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

Screenshot of table:

My stored procedure:

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

When I execute the stored procedure, I get this result:

ref
----
2,2

The issue is that for project collaborators I have two repeating values, thus I need to get a result like this:

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

My question is how to achieve the result mentioned above.

A better result of this stored procedure could have been:

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

"But the result is not showing as my expectation." No doubt. But you have failed to explain what you are trying to do. Your question lacks a question. — Gordon Linoff
– Gordon Linoff, Commented Mar 13, 2019 at 10:53
It appears you are using Microsoft SQL Server as your database. Stored procedures are not standard SQL but rather proprietary. Each database provider uses different syntax. Hence I suggest you add a tag to your question indicating what database you are using. That said, from the code you posted it looks like you want your stored procedure to return a resultset. I have no experience with Microsoft SQL Server stored procedures so I can't show you how to do it, but I'm sure an Internet search will provide you with an appropriate answer. Search for "SQL Server stored procedure return resultset". — Abra
– Abra, Commented Mar 13, 2019 at 11:17
"When I execute the stored procedure, I get this result:" When you execute the stored procedure using what parameter values? — Tab Alleman
– Tab Alleman, Commented Mar 13, 2019 at 14:02

Serkan Arslan · Accepted Answer · 2019-03-13 12:07:19Z

1

If I got it correct. you don't need to use the cursor.

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

answered Mar 13, 2019 at 12:07

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hemang A · Accepted Answer · 2019-03-13 11:36:31Z

Please try this Procedure.

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

Collectives™ on Stack Overflow

Retrieve values from database through stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related