Problem with SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with SP

Author

Topic

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 08:18:39

hi all,
iam using some simple SP ion my C# code..
for checking the whether user is exist in the database or not like as follows..

DROP PROCEDURE CHECKUSER1
GO
CREATE PROCEDURE CHECKUSER1
@UNAME VARCHAR,
@PASWORD VARCHAR,
@RES INT
AS
BEGIN
SELECT @RES=COUNT(*) FROM SAMPLE WHERE UNAME=@UNAME And pass=@PASWORD;

END

BUT THHIS SP ALWAYS SET THE 0 VALUE TO THE @RES PARAMETER EVEN THAT PERTICULAR USER EXIST IN THE DATABASE.

CAN YOU TELL ME THE WHERE THE PROBLEM WAS PERSISTS..
THANKS
RAMANA123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 08:23:59

Make sure you passed valid user name and pass word

After this select
SELECT @RES=COUNT(*) FROM SAMPLE WHERE UNAME=@UNAME And pass=@PASWORD;
Use this also
SELECT @RES

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 08:30:39

or possibly

RETURN @RES

but that is not "good practice"

Kristen

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 08:45:38

hi i am tried out with the
select @res
but it wont work

this is SP can we use that return in SP.
thanks
rams

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:00:09

Yes you can use Return provided you use OutPut parameter

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 09:02:52

hi madhivanan,
i am trying to execute the SP like below then also it gives 0..
wh y..pls

declare @UNAME VARCHAR,
@PASWORD VARCHAR,
@RES INT

--AS
--BEGIN
set @UNAME='sa'
set @PASWORD='sa'
SELECT @RES=COUNT(*) FROM SAMPLE WHERE UNAME=@UNAME And pass=@PASWORD;
print @res

i think problem with the select statement ..isnt it??
thakns
rams

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:05:44

Post the table structure and give us some sample data

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 09:05:57

I don't think you should have that ";" after ... And pass=@PASWORD;

But I don't think that will fix it!

What does

SELECT [Password] = ']' + pass + '[', *
FROM SAMPLE
WHERE UNAME='TheNameHere'

return for the password - what you are expecting?

Kristen

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 09:15:29

hi madhivanan,
my table structure is

uname pass
ramana ramana
sa sa
abs abc

like that..
suppose user enter sa & sa as uname and password..
then in ,my SP i am checking how many users are there or whether exists or not..in that select statement

but that SP set to @RES as 0.when i am passing sa& sa it should be count is 1 right.
that what i am expecting.
based on that i will do some actions.

thakns

rams123

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 09:19:30

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:26:24

Run this

Declare @t table(uname varchar(30), pass varchar(20))
insert into @t values('sa','sa')
insert into @t values('abs','abc')
Declare @count int
Declare @uname varchar(30)
Declare @pass varchar(20)
set @uname='sa'
set @pass='sa'

Select @count=count(*) from @t where uname=@uname and pass=@pass
select @count

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 09:29:04

hi
its giving 1 as output.
thanks
rams

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:29:58

Apply this to your table and see what it returns

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-17 : 09:37:36

but i want to write Stored Procedure..

cbeganesh
Posting Yak Master

105 Posts

Posted - 2005-08-17 : 10:05:11

thev ariable @res should be after begin

DROP PROCEDURE CHECKUSER1
GO
CREATE PROCEDURE CHECKUSER1
@UNAME VARCHAR,
@PASWORD VARCHAR
AS
BEGIN
declare
@RES INT

SELECT @RES=COUNT(*) FROM SAMPLE WHERE UNAME=@UNAME And pass=@PASWORD;

END

Kristen
Test

22859 Posts

Posted - 2005-08-17 : 10:35:55

Your VARCHAR parameters need a defined length

e.g.

CREATE PROCEDURE CHECKUSER1
@UNAME VARCHAR(20),
@PASWORD VARCHAR(20),
@RES INT
...

Kristen

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-18 : 05:17:02

hi thanks

finally it was resolved..

my small mistke was i didnt mention the length of the paramerters of the StoredProcddures.
thats it..
thanks alot for your replies,...
rams123

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-18 : 05:46:34

You forgot to define @res as output parameter.

DROP PROCEDURE CHECKUSER1
GO
CREATE PROCEDURE CHECKUSER1
@UNAME VARCHAR(20),
@PASWORD VARCHAR(20),
@RES INT output 
AS
BEGIN
SELECT @RES=COUNT(*) FROM SAMPLE WHERE UNAME=@UNAME And pass=@PASWORD; 

END

Test with:

declare @exists int
exec checkUser1 @uName = <test user name>, @pasword = <test password>, @Res = @exists output
select @exists

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 05:55:09

~~select @res~~
select @exists

Kristen

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-18 : 06:26:36

My bad. Thanks, Kirsten. I've edited original post to fix the error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources