Solved

problem with creating simple stored procedure

Posted on 2012-03-14
2
308 Views
Last Modified: 2012-03-14
I have created stored procedure like below.

CREATE PROCEDURE sp_login
   @uname  varchar(20)
   @pword  varchar(20)
   
AS
Select * From tbl_login
Where username = @uname and password1=@pword
Go

Open in new window


while execution it is giving errors as

Incorrect syntax near '@pword'.

Must declare the scalar variable "@uname".

can't I declare two variables as varchar?
0
Comment
Question by:lucky20
2 Comments
 
LVL 9

Accepted Solution

by:
mimran18 earned 250 total points
ID: 37723401
One comma is missing only, try this...

CREATE PROCEDURE sp_login
   @uname  varchar(20),
   @pword  varchar(20)
   
AS
Select * From tbl_login
Where username = @uname and password1=@pword
Go
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 37723444
Off topic - naming

Try to learn naming your objects with their schema name and don't start procedures with 'sp_'. It won't break anything but it helps the db-engine to faster identify the exacte object (and you won't have the chance to have same name as a system stored procedure you didn't know about , sp_help, sp_who, sp_addlogin, sp_droplogin .....)
More info at http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

CREATE PROCEDURE dbo.usp_login
   @uname  varchar(20),
   @pword  varchar(20)
   
AS
Select * 
From dbo.tbl_login
Where username = @uname and password1=@pword
Go

Open in new window

And use as
EXEC  dbo.usp_login 'John', '12ab'
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

18 Experts available now in Live!

Get 1:1 Help Now