Sample

This sample project illustrates how the various SQLHelp tags come together to produce programmers reference help documentation for your stored procedures.

In this sample you will see a set of conceptual operations performed on the database. The procedures in this example are not intended to be run by anyone, but if the underlying schema were present, could nicely do so.

An interesting side use for SQLHelp is during the architecture of a project. During this phase of development, the procedures can be named, parameters defined, and using the ##OV tag, return values and result sets defined, even before the actual SQL is written. The net result is a decoupling of the database from the project critical path, and by using SQLHelp, developers can develop against a set of SQL interfaces, much like COM development.

Please feel free to run SQLHelp against this sample project to see how changes to the tags affect the actual output of the project.

In this project you can see a potential bug in the system. Looking at the output of SQLHelp, you can see that only spLogin is marked as transactional, but spLogout also modifies data. This is not a parsing error on the part of the tool, rather a situation where the tool has highlighted a potentially problematic piece of SQL.

© Pikauba SoftwareTM. All rights reserved.

Help compiled by SQLHelp, a Pikauba Software product. All rights reserved.


Sample - Modifies dataTitle

Modifies data Sample

Sample - Modifies data

The procedures listed all modify at least one row in at least one table.

dbo.spLogin
dbo.spLogout


dbo.spLogin

Example Source
Object type: Stored procedure

This procedure validates the user supplied credentials, and if valid, logs the user into the system.
When calling spLogin, it is imperative to note that the password is encrypted.

Syntax

dbo.spLogin ( UserName, Password )

Parameters

Variable | Data type | Required | Output | Description
UserName | varchar(255) | Yes | No | The login name to use for validation.
Password | varchar(255) | Yes | No | The encrypted password to use for validation

Properties

Modifies data | Transactional | Uses Cursors | Executes procedures
Yes | Yes | No | No

Return Values

Value | Description
0 | Success. The user is logged in.
-1 | User does not exist.
-3 | User account is disabled.

Output recordset

Column | Description
UserType | The type of account the user has.
Name | The users name (first name + last name).
LoginDate | The date of last login.
UserId | The users ID.
Homepage | The users default homepage.
Timeout | The inactivity timeout associated with the user.
LogoutDate | The users last logout date.
PasswordExpired | A flag indicating if the password is expired.

Applies to

Modifies data | Returns data | Transactional

See also

dbo.spLogout

Source is located in: spLogin.SQL
Note: Documentation was produced using overrides. Actual SQL may or may not match this documentation.


dbo.spLogout

Source
Object type: Stored procedure

Updates the last logout date for the user, and sets the logged in flag to false.

Syntax

dbo.spLogout ( UserId )

Parameters

Variable | Data type | Required | Output | Description
UserId | int | Yes | No | The id of the user to update

Properties

Modifies data | Transactional | Uses Cursors | Executes procedures
Yes | No | No | No

Return Values

Value | Description
0 | Success

Applies to

Modifies data

See also

dbo.spLogin

Source is located in: spLogout.sql


Sample - Returns dataTitle

Returns data Sample

Sample - Returns data

The procedures listed all return data by way of a result set or output parameters. If the return method includes result sets, at least one result set is listed.

dbo.spGetProductDetails
dbo.spGetUserDetails
dbo.spLogin


dbo.spGetProductDetails

Source
Object type: Stored procedure

Returns multiple record sets indicating details associated with the input product

Syntax

dbo.spGetProductDetails ( ProductId )

Parameters

Variable | Data type | Required | Output | Description
ProductId | int | Yes | No | The identifier of the product we wish to retrieve details about.

Properties

Modifies data | Transactional | Uses Cursors | Executes procedures
No | No | No | No

Return Values

Value | Description
0 | Success

Output recordset 1

The line item details associated with the requested product

Column | Description
Name | The display name of the product
Short Description
Price | The display price of the item
Image Path
Image Width
Image Height

Data source | Description
Product | Product

Output recordset 2

In order to display the correct stock status to our users, we need to determine the stock status of the item, and the users geographic location.

Column | Description
SupplierId | The Id of the supplier who claims this stock status. We only care about the Id at this point.
Quantity On Hand
Cost Price
Availability Date

Data source | Description
SupplierProduct | SupplierProduct

Applies to

Returns data

Source is located in: spGetProductDetails.sql


dbo.spGetUserDetails

Source
Object type: Stored procedure

This procedure returns a single row of user information based upon the UserName supplied. Note: If the UserName supplied is not in the system, an empty result set is the return.

Syntax

dbo.spGetUserDetails ( UserName )

Parameters

Variable | Data type | Required | Output | Description
UserName | varchar(255) | Yes | No | The login name to use for validation. If the input name is not found, spGetUserDetails will return an empty result set.

Properties

Modifies data | Transactional | Uses Cursors | Executes procedures
No | No | No | No

Return Values

Value | Description
0 | Success. The detailed information about the user is present.
-1 | Failure. No user matching the input UserName was found. An empty result result set is returned.

Output recordset

Column | Description
UserId | The unique identifier for the user. This is a system generated IDENTITY value.
Name | The concatenation of the users' first and last names.
PreviousLogin | The date of the users previous login, if any. May be NULL if the user has never logged in.
PreviousLogout | The date of the users previous logout, if any. May be NULL if the user has never logged out.
TimeOut | The timeout for the user in minutes. Zero indicates the user has no timeout enforced.
UserType | What type of user is this user. Valid values can be found in the enumUserType enumeration.
Homepage | The URL for this user's personalized homepage. For users with multiple rows in the system, this is the default page they have indicated on the personalized options web page.
IsDisabled | A flag indicating if this user has been disabled for some reason. Users who are disabled may not login.
PwChangedDate | The date of last password change. May be NULL indicating the password remains the original supplied during registration.
PwExpirationDays | Indicates the number of days remaining until the user must change their password.

Data source | Description
User | User
UserType | UserType

Applies to

Returns data | UserProfile

Source is located in: spGetUserDetails.SQL


Sample - TransactionalTitle

Transactional Sample

Sample - Transactional

The procedures listed all perform at least part of their operations within a BEGIN TRANSACTION - COMMIT TRANSACTION block.

dbo.spLogin


Sample - ExamplesTitle

Examples Sample

Sample - Examples

View all the procedure example code that makes up this project.

dbo.spLogin


dbo.spLogin - ExampleTitle

Examples Sample

dbo.spLogin - Example

Summary listing

This procedure validates the user supplied credentials, and if valid, logs the user into the system.
When calling spLogin, it is imperative to note that the password is encrypted.

The encryption process can yield characters that can not be easily typed in Query Analyzer, so when attempting to debug this procedure, via

EXEC spLogin 'TestUser', 'ABCD',

unless the password was encrypted, the login should be expected to fail.


Sample - UserProfileTitle

UserProfile Sample

Sample - UserProfile

Various procedures make up the user object of the web site, but only a few of them are directly involved in defining the profile of the user.

Our client has defined the profile to include simple information such as the user's first and last name, but the system should be able to be expanded to include more in the future. These procedures are involved in this logic.

dbo.spGetUserDetails


dbo.spGetProductDetails - Source Code

Source Code Sample

dbo.spGetProductDetails

Summary listing

CREATE PROCEDURE dbo.spGetProductDetails
(
@ProductId int
)
AS

--##BD Returns multiple record sets indicating details associated with the input product
--##PD ProductId ^^ The identifier of the product we wish to retrieve details about.
--##RD 0 Success
--##RSD1 The line item details associated with the requested product
--##RS1 Name ^^ The display name of the product
--##RS1 Short Description ^^ The basic description (teaser) associated with the product
--##RS1 Price ^^ The display price of the item
--##RS1 Image Path ^^ The file system path (or URL) associated with the image we will display
--##RS1 Image Width ^^ The width of the image (used to set the IMG tag properly)
--##RS1 Image Height ^^ The height of the image (used to set the IMG tag properly)
--##RSD2 In order to display the correct stock status to our users, we need to determine the stock status of the item, and the users geographic location.
--##RS2 SupplierId ^^ The Id of the supplier who claims this stock status. We only care about the Id at this point.
--##RS2 Quantity on Hand ^^ How many items the supplier claims to have. Note this number could be negative to indicate a pre-sold condition.
--##RS2 Cost Price ^^ How much it will cost us to get one of them.
--##RS2 Availability Date ^^ The date the supplier reasonably feels they might be able to have some stock on hand. Note that this value could be in the past if the quantity on hand is more than zero.

SET NOCOUNT ON

SELECT [Name],
[Short Description],
[Price],
[Image Path],
[Image Width],
[Image Height]
FROM [Product]
Where [ProductId] = @ProductId

SELECT 'SupplierId',
'Quantity On Hand',
'Cost Price',
'Availability Date'
FROM SupplierProduct
Where ProductId = @ProductId

RETURN 0


dbo.spGetUserDetails - Source Code

Source Code Sample

dbo.spGetUserDetails

Summary listing

CREATE procedure dbo.spGetUserDetails
(
@UserName varchar(255)
)
AS
--##BD This procedure returns a single row of user information based upon the UserName supplied.
--##BD <font color=red>Note</font>: If the UserName supplied is not in the system, an empty result set is the return.
--##PD UserName The login name to use for validation. If the input name is not found, spGetUserDetails will return an empty result set.
--##RD 0 Success. The detailed information about the user is present.
--##RD -1 Failure. No user matching the input UserName was found. An empty result result set is returned.
--##RS1 UserId The unique identifier for the user. This is a system generated IDENTITY value.
--##RS1 Name The concatenation of the users' first and last names.
--##RS1 PreviousLogin The date of the users previous login, if any. May be NULL if the user has never logged in.
--##RS1 PreviousLogout The date of the users previous logout, if any. May be NULL if the user has never logged out.
--##RS1 TimeOut The timeout for the user in minutes. Zero indicates the user has no timeout enforced.
--##RS1 UserType What type of user is this user. Valid values can be found in the enumUserType enumeration.
--##RS1 Homepage The URL for this user's personalized homepage. For users with multiple rows in the system, this is the default page they have indicated on the personalized options web page.
--##RS1 IsDisabled A flag indicating if this user has been disabled for some reason. Users who are disabled may not login.
--##RS1 PwChangedDate The date of last password change. May be NULL indicating the password remains the original supplied during registration.
--##RS1 PwExpirationDays Indicates the number of days remaining until the user must change their password.
--##AT UserProfile

SET NOCOUNT ON

SELECT UserId = [User].UserId,
Name = [User].FirstName + ' ' + [User].LastName,
PreviousLogin = [User].LastLoginDate,
PreviousLogout = [User].LastLogoutDate,
TimeOut = UserType.InactiveTimeout,
UserType = [User].UserTypeId,
Homepage = UserType.Homepage,
IsDisabled = [User].IsDisabled,
PwChangedDate = [User].PwChangedDate,
PwExpirationDays = UserType.PwExpirationDays
FROM [User],
UserType
WHERE [User].LoginId = @UserName
AND UserType.UserTypeId = [User].UserTypeId

If @@RowCount <> 0
RETURN 0
Else
RETURN -1


dbo.spLogin - Source Code

Source Code Sample

dbo.spLogin

Summary listing

CREATE procedure dbo.spLogin
(
@UserName varchar(255),
@Password varchar(255)
)
AS
--##BD This procedure validates the user supplied credentials, and if valid, logs the user into the system. <br>
--##BD When calling spLogin, it is imperative to note that the password is encrypted.
--##PD UserName The login name to use for validation.
--##PD Password The encrypted password to use for validation
--##RD 0 Success. The user is logged in.
--##RD -1 User does not exist.
--##RD -3 User account is disabled.
--##RS1 UserType The type of account the user has.
--##RS1 Name The users name (first name + last name).
--##RS1 LoginDate The date of last login.
--##RS1 UserId The users ID.
--##RS1 Homepage The users default homepage.
--##RS1 Timeout The inactivity timeout associated with the user.
--##RS1 LogoutDate The users last logout date.
--##RS1 PasswordExpired A flag indicating if the password is expired.
--##EX The encryption process can yield characters that can not be easily typed in Query Analyzer, so when attempting to debug this procedure, via <br><br><i style="color: red"> EXEC spLogin 'TestUser', 'ABCD'</i>, <br><br>unless the password was encrypted, the login should be expected to fail.
--##SA dbo.spLogout
--##OV

SET NOCOUNT ON

Declare @UserId [numeric](18, 0),
@SessionId varchar (64),
@TempCount int,
@Name varchar (90),
@PreviousLogin datetime,
@TimeOut int,
@UserType int,
@Homepage varchar(50),
@PreviousLogout datetime,
@PersonId int,
@IsDisabled bit,
@PwChangedDate datetime,
@PwExpirationDays int

SELECT @UserId = [User].UserId,
@Name = [User].FirstName + ' ' + [User].LastName,
@PreviousLogin = [User].LastLoginDate,
@PreviousLogout = [User].LastLogoutDate,
@TimeOut = UserType.InactiveTimeout,
@UserType = [User].UserTypeId,
@Homepage = UserType.Homepage,
@IsDisabled = [User].IsDisabled,
@PwChangedDate = [User].PwChangedDate,
@PwExpirationDays = UserType.PwExpirationDays
FROM [User],
UserType
WHERE [User].LoginId = @UserName
AND [User].Password = @Password
AND UserType.UserTypeId = [User].UserTypeId

BEGIN TRANSACTION

UPDATE [User]
SET LastLoginDate = getdate(),
LoggedIn = 1 -- 1 indicates true
WHERE UserId = @UserId

COMMIT

SELECT [UserType] = @UserType,
[Name] = @Name,
[LoginDate] = @PreviousLogin,
[UserId] = @UserId,
[Homepage] = @Homepage,
[Timeout] = @Timeout,
[LogoutDate] = @PreviousLogout

RETURN 0


dbo.spLogout - Source Code

Source Code Sample

dbo.spLogout

Summary listing

CREATE PROCEDURE dbo.spLogout
(
@UserId int
)
AS

--##BD Updates the last logout date for the user, and sets the logged in flag to false.
--##PD UserId The id of the user to update
--##RD 0 Success
--##SA dbo.spLogin

SET NOCOUNT ON

UPDATE [User]
SET LastLogoutDate = GetDate(),
LoggedIn = 0 -- 0 indicates false
WHERE UserId = @UserId

RETURN 0