Solved

Learning Sql Stroed Procedures by example

Posted on 2010-09-24
5
255 Views
Last Modified: 2013-11-26
Hi,

I am trying to learn SqlServer stored procedures, and, for me, the best way to learn is by example.

I use the following query from an asp.net form to select a name and a password from SqlServer table (students). Will you give me the code for stored procedure that would do the same?

Thanks:

comm = New SqlCommand("Select userName,userPassword from Students where userName=@userName And userPassword=@userPassword", con)
        comm.Parameters.AddWithValue("@userName", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userName").Value = myUser
        comm.Parameters.AddWithValue("@userPassword", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userPassword").Value = myPassword

myUser and myPassword are two string variables carrying the name and password of a user trying to login.
0
Comment
Question by:adamtrask
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33754798
ASP.net code updated:

comm = New SqlCommand("your_example_proc", con)
comm.CommandType = CommandType.StoredProc
        comm.Parameters.AddWithValue("@userName", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userName").Value = myUser
        comm.Parameters.AddWithValue("@userPassword", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userPassword").Value = myPassword

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33754823
procedure code:
create procedure your_example_proc
 @userName nvarchar(100)
,@userPassword nvarchar(100)
as
begin
 Select userName,userPassword 
   from Students 
  where userName=@userName 
    And userPassword=@userPassword
end

Open in new window

0
 

Author Comment

by:adamtrask
ID: 33755278
Getting the following error:

Could not find stored procedure 'spUserPasswrod'.

I made a little change to the second line of the asp.net code :
 comm.CommandType = System.Data.CommandType.StoredProcedure

and gave the procedure the name spUserPasswrod.

When I executed the command on the server I got a success message at the end.
I don't know what is wrong
0
 

Author Comment

by:adamtrask
ID: 33755488
Thank you many, many times.....

It's working now.... the problem was I did not select the proper database when I created the procedure.

Thanks a lot
0
 

Author Closing Comment

by:adamtrask
ID: 33757003
Very helpful.... thanks again.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question