CODEWITHSUNDEEP
sqlsql-serverstored-procedures
LD16
LD16

Reputation: 95

SQL Server stored procedures and counts

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

Upvotes: 0

Views: 1029

Answers (4)

reza.cse08
reza.cse08

Reputation: 6178

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

Upvotes: 0

sapi
sapi

Reputation: 244

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

Upvotes: 0

Nguyễn Văn Quang
Nguyễn Văn Quang

Reputation: 123

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

Upvotes: 0

Paul Andrew
Paul Andrew

Reputation: 3253

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.

Upvotes: 1

Related Questions