Solved

Learning Sql Stroed Procedures by example

Posted on 2010-09-24
5
252 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Very helpful.... thanks again.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now