Geoff Sweet Geoff Sweet - 6 months ago 14
SQL Question

Cant perform multiple queries in a stored procedure without errors

I'm building a web portal in ASP.NET that allows us to track some security events that happen in our environment that are fairly proprietary to our platform. The data is really straight forward. We collect it and log it in a pretty basic table:

CREATE TABLE [dbo].[tblBonks](
[bonkID] [int] IDENTITY(1,1) NOT NULL,
[bonkVictim] [nvarchar](50) NOT NULL,
[bonkMachineID] [nvarchar](max) NULL,
[bonkUser] [nvarchar](50) NOT NULL,
[bonkTime] [datetime] NOT NULL,
CONSTRAINT [PK_tblBonks] PRIMARY KEY CLUSTERED


I have a stored procedure that my code calls to gather 24hr, 7day, and 1month stats. Depending I setup a single variable, @Scoreboard, that I can pass a string to to cause the stored procedure to generate different scorboard stats. This all seems pretty simple. Here is the stored procedure as it exists now:

CREATE PROCEDURE sp_BuildScoreboard
-- Add the parameters for the stored procedure here
@ScoreBoard nvarchar(25),
@Day nvarchar(5) OUTPUT,
@Week nvarchar(5) OUTPUT,
@Month nvarchar(5) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF (@ScoreBoard = 'VICTIM')
BEGIN
set @Day = (select TOP(1) bonkVictim, count(bonkVictim) as bonknumber
from tblBonks
where
bonkTime > DATEADD(DAY, -1, SYSDATETIME())
and
bonkVictim != bonkUser
group by bonkVictim
ORDER BY bonknumber desc)

/* 7 days */
set @Week = (select TOP(1) bonkVictim, count(bonkVictim) as bonknumber
from tblBonks
where
bonkTime > DATEADD(WEEK, -1, SYSDATETIME())
and
bonkVictim != bonkUser
group by bonkVictim
ORDER BY bonknumber desc)

/* 1 month */
set @Month = (select TOP(1) bonkVictim, count(bonkVictim) as bonknumber
from tblBonks
where
bonkTime > DATEADD(MONTH, -1, SYSDATETIME())
and
bonkVictim != bonkUser
group by bonkVictim
ORDER BY bonknumber desc)
END
/*ELSE IF (@ScoreBoard = 'LEADER')
BEGIN
END
ELSE IF (@ScoreBoard = 'MACHINE')
BEGIN
END*/
END
GO


However I have learned that this doesn't work for some reason. These should be three independent queries that return their own value to the output variables. However I'm left with this error:

Msg 116, Level 16, State 1, Procedure sp_BuildScoreboard, Line 38

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Procedure sp_BuildScoreboard, Line 41
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Procedure sp_BuildScoreboard, Line 42
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


I'm super weak in MS-SQL. Searching for those errors tends to take me into some other issues that aren't completely related to mine. What am I doing wrong?

Answer

First, get in the habit of putting semicolons at the end of each statement. That's not the problem (in this case). Instead, write syntactically correct statements:

       set @Day = (select TOP(1) bonkVictim
                   from tblBonks
                   where bonkTime > DATEADD(DAY, -1, SYSDATETIME()) and
                          bonkVictim <> bonkUser
                    group by bonkVictim
                    ORDER BY count(bonkVictim) desc
                   );

Note that the subquery is a scalar subquery. It can return only one column and at most one row. Two columns should generate an error.