Solved

Learning Sql Stroed Procedures by example

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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