question

technette avatar image
technette asked

Stored Procedure with Session value as a parameter

How do I create stored procedure that passes in an ID from a session, If the ID matches, return true/ false if not?
procedurestored
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You might want to look up SUSER_SNAME system function.
1 Like 1 ·

1 Answer

·
Raj More avatar image
Raj More answered
Assuming you are checking the incoming Id against a column in a table, this should get you started: Create Procedure CheckForSession @SessionId VarChar (100) AS Select ISNULL ( SELECT Max (1) From SessionsTable Where SessionId = @SessionId ), 0) as SessionExists
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@technette It represents the column name of the result set of the stored procedure, which always returns one record. If the SessionsTable has the record satisfying the predicate then the sub-select will return 1 because max(1) = 1. If the table does not have the record for the specified SessionID then the result of the sub-select is null. Applying isnull to it guarantees that the procedure call will always return result in the form of one record one column (named SessionExists) with the possible values of either 1 or 0. This will make the stored procedure output consistent (almost like a boolean function):
exec CheckForSession 'bad_id';

-- result:
SessionExists
-------------
0

exec CheckForSession 'good_id';

-- result:
SessionExists
-------------
1
1 Like 1 ·
Raj, Thank you for responding. What does As "SessionExists" represent?
0 Likes 0 ·
This is so kool! For my scenario We have a Session 'UserNumber' set here. The same user number is in a database table. I want to check the Session 'UserNumber' against the table 'UserNumber' and UserDivision.
0 Likes 0 ·
Maybe I can simply use the data reader to check against the session variable that I retrieve: Dim UNUM As String = CType(Session.Item("UserNumber"), String) I created a stored procedure with a UserNumber as a parameter and returns a 1 if the conditions are met in the Where clause. ???
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.