LD16 LD16 - 6 months ago 11
SQL Question

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

Answer

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.