how to call same logic using different parameter in if then else stored procedur

Question

Post reply

how to call same logic using different parameter in if then else stored procedur

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 19, 2019 at 3:50 pm

#3681484

ALTER PROCEDURE [dbo].[employee]
@isemployee int
AS
IF (@isemployee = 0)
BEGIN
DECLARE @Email Varchar (100)
DECLARE @Name Varchar (100)
SELECT @Email = text from dbo.emailtable WHERE email = 'XXX'
SELECT @Name = name from dbo.nametag where refer = 'HJJ'
SELECT
@Name as NAME,
@Email as EMAIL,
'' as Test1,
'' as Test2
END
ELSE
BEGIN
DECLARE @Email Varchar (100)
DECLARE @Name Varchar (100)
SELECT @Email = text from dbo.emailtable WHERE email = 'XXX'
SELECT @Name = name from dbo.nametag where refer = 'HJJ'
SELECT
@Name as NAME,
@Email as EMAIL,
'' as Test1,
'' as Test2
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Thanks for the SQL, but what's the problem here? How can we help you?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Could be something like this

ALTER PROCEDURE [dbo].[employee]
@isemployee int
AS
BEGIN
DECLARE @Email Varchar (100)
DECLARE @Name Varchar (100)

IF (@isemployee = 0)
BEGIN
SELECT @Email = text from dbo.emailtable WHERE email = 'XXX'
SELECT @Name = name from dbo.nametag where refer = 'HJJ'
END
ELSE
BEGIN
SELECT @Email = text from dbo.emailtable WHERE email = 'XXX'
SELECT @Name = name from dbo.nametag where refer = 'HJJ'
END

SELECT
@Name as NAME,
@Email as EMAIL,
'' as Test1,
'' as Test2
END

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

drew.allen SSC Guru Points: 76985 More actions · Answer 3

You do realize that the IF and the ELSE section are EXACTLY THE SAME, right? One definition of insanity is doing the same thing and expecting different results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

We have no idea what any of the tables involved in this procedure look like. Since the procedure does something, its name should be a verb but instead you have a singular noun. This means this thing can even be a table! A table would have a plural or collective name because it represents a set.

Since SQL is a declarative language, we hate local variables, flags, loops and flow control. Those of the properties of procedural languages. You are still basically writing Fortran. You might also want to look at the length of an email and WHERE there is no such thing as a generic "name"; it has to be the name of something in in particular. I'll guess it's the employee and not his dog. Putting the a fix "_TBL" or "_table" is a design flaw called a tibble. The table also cannot have a column called "text"; what kind of attribute is that?

We also don't use flags in RDBMS, so your is_employee doesn't belong here at all. It looks like Chinese characters in the middle of an English text. The if-then-else control structure you're using has both branches identical. You can drop one of them. Then why did you return constant strings? With only what we have given us I would have expected something more like this:

CREATE VIEW Foobar (emp_email, emp_name)

AS

SELECT M.*, T.*

FROM (SELECT emp_email FROM something_emails WHERE email = 'XXX') AS M,

(SELECT emp_name FROM dbo.name_tags WHERE refer = 'HJJ') AS T;

Please post DDL and follow ANSI/ISO standards when asking for help.