SQL Server stored procedures and counts

Question

Hi I'm relatively new to SQL. I'm trying to create a stored procedure to take a count of recrods with some criteria. If the count <> 1 then I want it to Print a value so that I can pass it back to my application in VB6.

What I'm trying to do is find if an employee already has access to a certain application, if they do then do something.......

select sum(clientid) as NumClients 
from TblEmployee 
where clientid = '1709' 
      and ApplicationID='Excel'

Then I'd like to check the count but i'm kind of lost on the synthax, something along the lines of

if NumClients <> 1
    begin
        Print '1'
        go
    end

`SUM`ming the ClientId field seems odd to me. Would it not be better to `COUNT` them? — Adam V, Jun 01 '16 at 13:19
VB cannot see anything returned by [PRINT](https://msdn.microsoft.com/en-us/library/ms176047.aspx). Instead your [sp can return](https://msdn.microsoft.com/en-us/library/ms188655.aspx) an output parameter, code or table. — David Rushton, Jun 01 '16 at 13:43

score 1 · Accepted Answer · answered Jun 01 '16 at 13:17

1

Do something like this:

CREATE PROCEDURE [dbo].[usp_EmpCheck]
    (
    @ClientID INT,
    @App VARCHAR(50),
    @CheckResult BIT OUTPUT
    )
AS
BEGIN

    IF (
        SELECT
            SUM(clientid) 
        FROM
            TblEmployee 
        WHERE
            clientid = @ClientID 
            AND ApplicationID = @CheckResult
        ) <> 1
        BEGIN
            SET @CheckResult = 1
        END
    ELSE
        BEGIN
            SET @CheckResult = 0
        END
END

Then get your VB to act on the value of the procedure OUTPUT parameter.

answered Jun 01 '16 at 13:17

Paul Andrew

3,233
2
17
37

Am I able to so something along the lines of ELSEIF instead of just ELSE, say I'm checking for 'Excel' then I want to do ELSE IF ='WORD' the same way your are doing it, set @chkResult = 2 – LD16 Jun 01 '16 at 13:27
Yes, IF (condition1) ELSE IF (next condition) ELSE (something) is valid. Check out this question: http://stackoverflow.com/questions/15085990/can-you-have-if-then-else-logic-in-sql – Paul Andrew Jun 01 '16 at 13:29

reza.cse08 · Answer 2 · 2016-06-01T14:06:57.257

0

try this.

ALTER PROCEDURE TestSP
(
    @clientid  INT,
    @ApplicationID  INT,
    @result BIT OUTPUT
)
AS
BEGIN

   DECLARE @count INT;

   SELECT @count= CASE   WHEN COUNT(clientid )>=1 THEN 1
                         ELSE 0
                         END
   FROM TblEmployee 
   WHERE clientid = @clientid
        AND ApplicationID = @ApplicationID

   SET @result = @count

END
GO

edited Jun 01 '16 at 14:06

answered Jun 01 '16 at 13:16

reza.cse08

5,938
48
39

score 0 · Answer 3 · answered Jun 01 '16 at 13:25

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   type = 'P'
                    AND name = 'sp_Count' )
    DROP PROCEDURE [dbo].[sp_Count];
GO 

CREATE PROCEDURE [dbo].[sp_Count]
AS
    DECLARE @Sum INT;
    SELECT  @Sum = SUM(CompanyID) FROM   dbo.Company;

    PRINT @Sum;

    IF ( @Sum > 1 )
        BEGIN

            PRINT 'More than one';
        END;
    ELSE
        BEGIN
            PRINT 'zero';
        END;
        ---- sp_Count

score 0 · Answer 4 · edited Jun 01 '16 at 13:57

SQL Code:

 CREATE PROCEDURE [dbo].[check_number]
     (
     @ClientID INT,
     @App VARCHAR(50)
     )
 AS

 select case number
            WHEN 1 then 'not what you want'
            ELSE 'what you want'
        END as result
 FROM 
 (SELECT count(1) as number
  FROM   TblEmployee 
  WHERE  clientid = @ClientID 
    AND ApplicationID = @CheckResult ) as results

your vb6 code might look like :

 Set cmd = New ADODB.Command
  cmd.ActiveConnection = con
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "check_number

  cmd.Parameters.Append cmd.CreateParameter_
    ("ClientID", adint, adParamInput, yourClientIdParamValue)
  cmd.Parameters.Append cmd.CreateParameter_
    ("App", adVarChar, adParamInput, 50, yourAppValue)

  Set rs = cmd.Execute

  If Not rs.EOF Then
   if rs.Fields(0) = 'what you want' then
    'do what you want !
   End if
  End If

  Set cmd.ActiveConnection = Nothing

 End Sub

I'm not sure my syntax is 100% correct for the vb6

SQL Server stored procedures and counts

4 Answers4