IF EXISTS - 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

General SQL Server Forums

New to SQL Server Programming

IF EXISTS

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-20 : 11:38:15

There's an error in this SQL statement somewhere, but I can't seem to find it. Here's what I'm trying to do:

I have a list of states, and each state is assigned an ID
For each state, I have some data
Given a state name, find the state's ID
If the state ID exists, output the data for that state
If the state ID is not in the database, output the data for all states

Here's my tables:

--------------------------
States
--------------------------
ID | Name
--------------------------

--------------------------
Data
--------------------------
StateID | StateData
--------------------------

Here's my SQL:

ALTER PROCEDURE sproc_GetStateData
(
@State varchar
)
AS
DECLARE @StateID int

IF EXISTS(SELECT @StateID = ID FROM States WHERE Name = @State)
BEGIN
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 11:44:28

i think you need only this:-

ALTER PROCEDURE sproc_GetStateData 
( 
@State varchar
)
AS
DECLARE @StateID int
SELECT @StateID = ID FROM States WHERE Name = @State

SELECT S.StateData
FROM Data AS D INNER JOIN States AS S
ON StateID = ID
WHERE (S.StateID = @StateID OR @StateID IS NULL)
GO

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-20 : 11:47:41

Try :
ALTER PROCEDURE sproc_GetStateData
(
@State varchar
)
AS
DECLARE @StateID int
SET @StateID =(SELECT ID FROM States WHERE Name = @State)
IF ISNULL(@StateID ,0) > 0
BEGIN
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-20 : 12:21:01

visakh, yours didn't work.

Jack, yours didn't work either. It would just output the data for all the states no matter what was entered.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 12:23:48

Why have you not given a length for varchar parmeter? can you give it a length & then try?

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-20 : 12:26:30

Wow, thanks visakh, that worked. I didn't think that would have been a problem.

So jack, your code does work after all.

Thanks everybody!

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-20 : 12:29:07

CREATE PROC sproc_GetStateData
(
@State VARCHAR(50)
)
AS
BEGIN
DECLARE @StateID INT

SELECT @StateID = (SELECT ID FROM States WHERE NAME = @STATE)

IF @StateID IS NOT NULL
BEGIN
SELECT D.StateData
FROM Data AS D
INNER JOIN States AS S ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
BEGIN
SELECT D.StateData
FROM Data AS D
INNER JOIN States AS S ON D.StateID = S.ID
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources