Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Learning Sql Stroed Procedures by example

Posted on 2010-09-24
5
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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