How to write if condition in tsql

1

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

2

create table #login(uname varchar(20),pword varchar(20),active bit)
insert into #login values ('Lekshmi','baiju',0)
insert into #login values ('Jain','rkjain',1)
insert into #login values ('ram','ram123',1)

create table #Busmaster(uname varchar(20),busname varchar(20))
insert into #Busmaster values ('Jain','B1')
insert into #Busmaster values ('Jain','B2')
insert into #Busmaster values ('ram','R1')

SELECT A.UNAME,B.UNAME,A.pword,A.ACTIVE,CASE WHEN A.UNAME IS NOT NULL AND A.PWORD IS NOT NULL THEN 1 ELSE 0 END FirstCondition,
CASE WHEN A.UNAME IS NOT NULL AND A.PWORD IS NOT NULL AND A.ACTIVE=0 THEN 2
WHEN A.UNAME IS NOT NULL AND A.PWORD IS NOT NULL AND A.ACTIVE=1 THEN 3 END SecondCondition,

 CASE  WHEN A.UNAME IS NOT NULL AND A.PWORD IS NOT NULL AND A.ACTIVE=1 AND A.UNAME=B.UNAME THEN 4
   WHEN A.UNAME IS NOT NULL AND A.PWORD IS NOT NULL AND A.ACTIVE=1 AND A.UNAME!=B.UNAME THEN 3  end ThirdCondition

FROM #login A
LEFT JOIN #Busmaster B
ON A.UNAME=B.UNAME

3

Thanks for your reply .i couldn't understand your code.
actually iam looking for a stored procedure

4

I want to write a stored procedure with if condition
I have two tables
<<

IF is not a search condition; it is a control flow statement. But SQL is a declarative language and has no control flow. On a scale from 1 to 10, what color is your favorite letter of the alphabet? This is how silly you are being.

Where is the DDL for these two tables? You did not post any! So we have to guess and type for you! why are you so rude? Did you read the part about Netiquette at the front of the forum?

The ASCII pictures you posted look like you never read a book on RDBMS. You do not know the ISO 11179 rules for data element names, and you seem to be using bit flag! We do not use bit flags in SQL; that was assembly language in the 1950s and 60s.

CREATE TABLE User_Passwords
(user_name VARCHAR(255) NOT NULL,
password_start_date DATE NOT NULL,
password_expiry_date DATE NOT NULL,
CHECK (password_start_date < passeword_expiry_date),
PRIMARY KEY (user_name, password_start_date),
password_txt VARCHAR(255) NOT NULL
);

INSERT INTO User_Passwords
VALUES
('Lekshmi', 'baiju ', '2015-01-01', '2016-12-31'),
('Jain', 'rkjain', '2015-01-01', '2016-12-31'),
('Ram', 'ram123', '2015-01-01', '2016-12-03');

The term "master" is not part of RDBMS. It is part of tape files and network databases. Also, both these non-tables need keys! Her are mny guesses. But where the DRI that would reference the user names?

CREATE TABLE Bus_Routes
(user_name VARCHAR(255) NOT NULL,
bus_route_name CHAR(2) NOT NULL,
PRIMARY KEY (user_name, bus_roue_name)

INSERT INTO Bus_Routes
VALUES
('Jain', 'B1'),
('Jain', 'B2'),
('Ram', 'R1');

Your requirements are for bad 1950's COBOL, not SQL. We do not do step-by-step computing in a declarative language. Do not use the “sp_” prefix on a procedure names; Google what it means in T-SQL.

CREATE PROCEDURE Something_login
(@in_user_name VARCHAR(255),
@in_password_txt VARCHAR(255))
SELECT P.user_name, B.bus_route_name
FROM User_Passwords AS P
LEFT OUTER JOIN
Bus_Routes AS B
ON @in_user_name = P.user_name
AND @in_user_name = B.user_name
AND @in_password_txt = P.password_txt
AND CAST(CURRENT_TIMESTAMP AS DATE)
BETWEEN P.password_start_date
AND P.password_expiry_date;

We hate local variables in declarative languages; we use the expressions that return values directly in the code. But, even given that, why do keep creating more and more redundant local variables? Why are 1 and 3, 0 and 2 totally different? Looks like the same non-RDBMS flags to me. next condition is IF active = 1 check that user_name exists in Bus_Routes it exists there then return 4 else 3<<

Also the PRINT statement is for debugging only. In RDBMS, we have a presentation layer; we pass a result set (set, NOT flags!) from the database layer to those presentation layers.

Instead, this will give you the active user name and his bus route (if any). No flags, but actual values. See the difference in the mindset?

5

Not sure where you get that tidbit from. If you must do flow control in SQL Server, IF is certainly one of the ways to do it.

I will agree, though, that the OP appears to be stuck in a RBAR solution and is using IF inappropriately where CASE would be more appropriate.

6

You never need to do flow control in theory. Ever get the proof that primitive recursive functions are computational equal to automata with a single stack? About 40+ years ago in a graduate computer science course? After all this time, I just remember that it means a functional/declarative language can do the same algorithms as a procedural one. Or "You can write everything in FORTRAN I or a Turing machine, but you would be crazy to try it!" :scream:

We added the SQL/PSM to the standards *(Andrew Eisenberg was the original author). but we separated it from the SQL parts. It is based on ADA (that was the cool language du jour at the time). T-SQL is the Sybase language; we have PL/SQL from Oracle; Informix 4GL (based on Algol 60); SQL/PSM from Mimer and IBM; etc.

7

Heh... "In theory, theory and practice are the same. In practice, they are not". :wink:

2 Likes