Convert Access query to SQL stored procedure

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

Convert Access query to SQL stored procedure

Author

Topic

jaglick
Starting Member

2 Posts

Posted - 2008-06-06 : 14:59:59

I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL:

SELECT DISTINCTROW EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo,
Accounts.*
FROM Accounts
INNER JOIN AccountCodes
ON Accounts.AccountID = AccountCodes.AccountID
WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24))
ORDER BY Accounts.LastName, Accounts.FirstName

My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this?

Jon

jaglick
Starting Member

2 Posts

Posted - 2008-06-09 : 11:40:56

I got some help from one of the guys at work. Here's what he came up with... Works beautifully!

SELECT CASE WHEN
(SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) > 0 THEN 'Yes' ELSE 'No' END AS FullTimeInfo, CASE WHEN
(SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) > 0 THEN 'Yes' ELSE 'No' END AS ShortTermInfo, CASE WHEN
(SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) > 0 THEN 'Yes' ELSE 'No' END AS GeneralInfo, dbo.Accounts.*
FROM dbo.Accounts INNER JOIN
dbo.AccountCodes ON dbo.Accounts.AccountID = dbo.AccountCodes.AccountID
WHERE (dbo.Accounts.SummitID = @SummitID) AND (dbo.AccountCodes.CodeID = 10 OR
dbo.AccountCodes.CodeID = 22 OR
dbo.AccountCodes.CodeID = 24)
ORDER BY dbo.Accounts.LastName, dbo.Accounts.FirstName

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources