sql server programming: In a web based application I have a dropdown that gets populated based
on what state the user selects. I am trying to rewrite the procedure
so that it will select from one table if the state = VA and another
table if it is not VA
This is what I am working on, but cannot seem to get the syntax
correct. The vendor of the application has not been very helpful.

CREATE PROCEDURE prcGetCountyListDD_rev (@State as varchar(10)) AS

---if state is VA then need to pull from tblCityCO the old table with
all the address information for VA counties

CASE when (@State='VA') then
'County'=Select tblCityCo.CC_NAME
FROM tblCityCo
Order by CC_NAME
end
-- if not VA and not null then pull from ZipList5 with limited info
for all US counties

CASE when (@State <>'VA') AND (NOT(@State IS NULL)) then
'County'=select distinct tblZiplist5.County
from Ziplist5
where St = @State
End

Order by County

GO

Thank you.

TimK (kimrey@gmail.com) writes:
[quoted text, click to view]

Shouldn't @State be char(2)?

[quoted text, click to view]

There is no CASE statement in T-SQL, only a CASE expression and that
does not seem to be useful here.

IF @state = 'VA'
BEGIN
SELECT County = CC_NAME
FROM tblCityCo
ORDER BY CC_NAME
END
ELSE
BEGIN
SELECT DISTINCT County
FROM Ziplist5
WHERE St = @state
ORDER BY County
END

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at