December 24, 2015 at 9:13 am
Hi,
I want to write a stored procedure with if condition
i have two tables
Login
-------
uname|pword|active
-----------------------------
Lekshmi|baiju |0
Jain | rkjain | 1
ram | ram123 | 1
Busmaster
uname | busname
-------------------
jain | B1
Jain | B2
ram | R1
my requirement is first check uname and password
if it exists return 1 else 0.
if uname and password exists check active =0 or 1
if active=0 returns 2 and active=1 return 3
next condition is if active=1 check that uname exists in busmaster
it exists there then return 4 else 3
i tried following query
create procedure [dbo].[Sp_Login]
@uname nvarchar(20),
@pword nvarchar(20),
@chkrecords int out
as
declare
@count int,@count1 int,@count2 int
select @count=count(*) from Login where uname=@uname and pword=@pword
select @count1=count(*) from Login where uname=@uname and pword=@pword and active=1
select @count2=count(*) from Busmaster where uname=@uname
if (@count=0)
begin
set @chkrecords=0
end
else
if(@count1=0)
begin
set @chkrecords=2
end
else
set @chkrecords=1
print @chkrecords
how to solve this
Regards
Baiju
December 24, 2015 at 9:48 am
baiju krishnan (12/24/2015)
Hi,I want to write a stored procedure with if condition
i have two tables
Login
-------
uname|pword|active
-----------------------------
Lekshmi|baiju |0
Jain | rkjain | 1
ram | ram123 | 1
Busmaster
uname | busname
-------------------
jain | B1
Jain | B2
ram | R1
my requirement is first check uname and password
if it exists return 1 else 0.
if uname and password exists check active =0 or 1
if active=0 returns 2 and active=1 return 3
next condition is if active=1 check that uname exists in busmaster
it exists there then return 4 else 3
i tried following query
create procedure [dbo].[Sp_Login]@uname nvarchar(20),
@pword nvarchar(20),
@chkrecords int out
as
declare
@count int,@count1 int,@count2 int
select @count=count(*) from Login where uname=@uname and pword=@pword
select @count1=count(*) from Login where uname=@uname and pword=@pword and active=1
select @count2=count(*) from Busmaster where uname=@uname
if (@count=0)
begin
set @chkrecords=0
end
else
if(@count1=0)
begin
set @chkrecords=2
end
else
set @chkrecords=1
print @chkrecords
how to solve this
Regards
Baiju
I am sad to say there are about 19 things wrong with this code. 🙂 Well, not quite, but it is an extensive list!
1) NEVER name a sproc with sp_ unless you are creating a system sproc such as the AWESOME sp_whoisactive.
2) You are using a fat 4-byte integer to store values that are either 0, 1 or 2. That is a tinyint, which takes just 4 bytes. Not so critical here since it is a variable, but I bet you do the same thing with you TABLES, and that is a no-no. Always use the SMALLEST data type that will store what you need.
3) Speaking of which, do you REALLY need Nvarchar fields? Can't tall you the number of times I see those used when there will NEVER EVER be the need for 2-byte languages.
4) Lets assume you really DO need to COUNT rows (you don't - more on that below). You hit the same table TWICE to COUNT stuff there, when it can be done in a single pass:
SELECT @Count = COUNT(*),
@Count2 = SUM(CASE WHEN Active = 1 THEN 1 ELSE 0 END)
from Login
where uname=@uname
and pword=@pword
LEARN TO USE THIS CONSTRUCT (CASE inside SUM). AMAZINGLY powerful!!
5) Now for the REALLY good stuff: NEVER do a COUNT when you just need to check EXISTENCE of one or more rows, and also the obverse - NOT EXISTS (although not as big a win).
IF NOT EXISTS (select * from Login where uname=@uname and pword=@pword)
BEGIN
SET @chkrecords=0
END
ELSE
BEGIN
IF NOT EXISTS (select * from Login where uname=@uname and pword=@pword and active=1)
BEGIN
SET @chkrecords=1
END
ELSE
BEGIN
IF NOT EXISTS (select * from Busmaster where uname=@uname )
BEGIN
SET @chkrecords=2
END
END
END
NOTE: my BEGIN/ENDs could be off above.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 24, 2015 at 9:49 am
Quick question, are you storing passwords in plain text?
😎
December 24, 2015 at 10:09 am
Your code doesn't match your description. Here's code that matches your description.
For efficiency, I've limited it to a single SELECT from the login table.
CREATE PROCEDURE [dbo].[Sp_Login]
@uname nvarchar(20),
@pword nvarchar(20),
@chkrecords int out
AS
set nocount on;
return (
select
case
when l.active is null then 0
when l.active = 0 then 2
when l.active = 1 then
case when exists(select 1 from busmaster b where b.uname = @uname)
then 4
else 3
end
end
from (
select max(active) as active
from (
select active
from login
where uname = @uname
union all
select null
) as derived
) as l
)
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 24, 2015 at 1:23 pm
Eirikur Eiriksson (12/24/2015)
Quick question, are you storing passwords in plain text?😎
Looks that way. So a bunch of T-SQL problems that Kevin picked up and a critical, major security flaw.
Passwords should NEVER be stored plain text. Use a hashing function and hash multiple times. The bcrypt algorithm (C# and others) is great. A single MD5 is useless. Please make sure this security vulnerability is addressed as soon as possible. The large number of data breeches in the last year should show why.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 25, 2015 at 5:22 am
when i tried to execute your code following error shown
Msg 8117, Level 16, State 1, Procedure Sp_Login, Line 19
Operand data type bit is invalid for max operator.
December 25, 2015 at 8:58 am
baiju krishnan (12/25/2015)
when i tried to execute your code following error shownMsg 8117, Level 16, State 1, Procedure Sp_Login, Line 19
Operand data type bit is invalid for max operator.
I see what the problem is, but have to ask if you do as well. If not, check out the MAX function in Books Online.
Also, did you see what Kevin, Eirikur and Gail posted? There's some important stuff in there - very important.
December 25, 2015 at 12:35 pm
baiju krishnan (12/25/2015)
when i tried to execute your code following error shownMsg 8117, Level 16, State 1, Procedure Sp_Login, Line 19
Operand data type bit is invalid for max operator.
Then we'll need to cast "active" to a tinyint:
...
select max(cast(active as tinyint)) as active
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply