Scalar Variable when using a stored procedure

Spider · March 11th, 2009, 03:46 PM

Could someone please help me!
I am trying to pull information out of my database using a stored procedure.

Code:

ALTER PROCEDURE GetParticipantbyParticipantId
AS
 
SELECT Participant.UserId, Participant.FirstName, 
Participant.LastName, aspnet_Users.UserName FROM 
Participant INNER JOIN aspnet_Users ON Participant.
UserId = aspnet_Users.UserId WHERE aspnet_Users.UserName =@UserName
RETURN

The error message I am getting is "Must declare the scalar variable @UserName"

Could anyone advise me where and how I can declare this variable.

Basically, I want it to read it from the current logged in user - ie the result which is stored in "asp:LoginName#LoginName1"

I am pretty new to using procedures. I have been able to achieve a
result using the following WHERE clause in a sql statement stored in the .aspx page:

Code:

WHERE (aspnet_Users.UserName LIKE '%' + @UserName + '%')"

The following is what is stored in the code behind:

Code:

ProtectedSub SqlDataSource2_Selecting(ByVal sender AsObject, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) _
Handles SqlDataSource2.Selecting
Dim userName AsString = Context.User.Identity.Name
e.Command.Parameters("@UserName").Value = userName
EndSub
ProtectedSub SqlDataSource1_Selecting(ByVal sender AsObject, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) _
Handles SqlDataSource1.Selecting
Dim userName AsString = Context.User.Identity.Name
e.Command.Parameters("@UserName").Value = userName
EndSub

When I try to plug this into another page, I cannot get it to work. I need to be able to access user data from a number of pages, so I thought a procedure would be a standard way to do this. Could anyone give me some (simple) advice to get this working. Thanks in advance.

philip_cole · March 11th, 2009, 04:24 PM

Hi Spider
You're very close.

As you have already worked out, when you run a plain SQL statement which uses parameters, such as

Code:

WHERE (aspnet_Users.UserName LIKE '%' + @UserName + '%')"

sql server essentially replaces the places where @UserName appears with the value you specify.

Stored procedures work slightly differently in that the parameter is passed as, well, a parameter to the stored procedure, which will itself do the replacement, in exactly the same sort of way you pass parameters to a method in VB. There is a fairly good step by step, if slightly long-winded, introduction to stored procedures and input variables at http://www.sql-server-performance.co...basics_p3.aspx

When the ASP.NET page calls the stored procedure, it only needs to send the name of the procedure and the values to give the parameters. So it actually runs something like

Code:

EXEC GetParticipantbyParticipantId param1Value, param2Value

So the question is how to tell the procedure what parameters to expect. To do this, you add them in a list after the name of the procedure and before the AS bit. So your one would look something like:

Code:

ALTER PROCEDURE GetParticipantbyParticipantId (
  @UserName VARCHAR(20)
) AS
 
SELECT Participant.UserId, Participant.FirstName, 
Participant.LastName, aspnet_Users.UserName FROM 
Participant INNER JOIN aspnet_Users ON Participant.
UserId = aspnet_Users.UserId WHERE aspnet_Users.UserName =@UserName
RETURN

Note that putting brackets round the list is optional, but I find it helps readability.

HTH
Phil