Conditional Stored Proc

[igrummett]

Hi, I am new to stored procedures and need some help is defining an SP where 3 variables are passed to a SP and a derived value is passed back.

The table name is Resource_Auth with the following attributes:-
Resource ID
password
resource_Active
The following variables are passed: -
Resource ID
Password
Resource_Active

Output
User_Authorised

Stored procedure needs take the variables and if
the password matches that on the database for the resource ID and the Resource_Active is set to 'Y' it
passes User_Authorised back as ‘Y’ else is will pass back ‘N’

Is this possible within a SP.

Thanks

 

[mrdenny]

Yes this is completly possible within a stored procedure. You would need something along these lines.

CREATE PROCEDURE SomethingOrAnother
   @ResourceId nvarchar(10),
   @Password nvarchar(10),
   @Resource_Active char(1),
   @User_Authorised char(1)
AS
IF EXISTS (SELECT * FROM YourTable WHERE ResourceId = @ResourceId AND Password = @Password and @Resource_Active = 'Y')
BEGIN
    SET @User_Authorised = 'Y'
END
ELSE
BEGIN
    SET @User_Authorised = 'N'
END

Don't you want the database to decide if the resource is active instead of having the client (web site, applications, etc) provide that information as how will the client app know without checking in the database?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bborissov]

CREATE PROCEDURE MyTest(
@ResourceID int,
@Password varchar(8000)
@User_Authorised char(1) OUTPUT
)
AS
BEGIN

SELECT @User_Authorised = CASE WHEN Resource_Active = 'Y'
                               THEN 'Y'
                          ELSE 'N' END
       FROM  Resource_Auth
WHERE [Resource ID] = @ResourceID AND 
      [password]    = @password

SET @User_Authorised = ISNULL(@User_Authorised,'N')
END

not tested at all

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[igrummett]

Thats great - thanks I will give it a go.