How to code this SP - 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

SQL Server 2000 Forums

SQL Server Development (2000)

How to code this SP

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-01-30 : 16:49:10

I have this SP:
CREATE PROCEDURE dbo.SelectCardGroupToo
@txtUserId nvarchar(12),
@txtCompNum nvarchar(10),
@txtAcctNum nvarchar(16)
AS

DECLARE @ID int

-- check if account number for an internet number exists
SELECT @ID = IdentityCol FROM UserGroups
WHERE txtInternetNum = @txtCompNum AND txtAccountNum = @txtAcctNum

-- if user's acct num is in the the list, grab all cardholders
IF @ID Is Not NULL
SELECT DISTINCT
UserGroups.txtAccountNum, Account.txtCompanyNum, Account.txtName,
Account.txtAdd2
FROM ApprovalGroup INNER JOIN
UserGroups ON
ApprovalGroup.txtInternetNum = UserGroups.txtInternetNum AND
ApprovalGroup.intViewGroup = UserGroups.lngGroupNum
INNER JOIN Account ON
UserGroups.txtAccountNum = Account.txtAccountNum AND
UserGroups.txtInternetNum = Account.txtInternetCompNum
WHERE (ApprovalGroup.txtUserID = @txtUserId) AND
(ApprovalGroup.txtInternetNum = @txtCompNum)
ORDER BY Account.txtName

-- if not in the list,
ELSE
--don't know this part
--I want to do the above long select query and I also want to do another query and combine the two recordsets together

Thanks for any help.

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-30 : 17:06:20

Hard to say without knowing the query you want to combine it with.

If they have the same number of columns and the columns are the same, you can do a UNION. But, you should post the other query.

-Chad

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-30 : 17:18:47

Peter,

It is very hard to give advise when there is no background..

If you could provide some "CREATE TABLE" and "INSERT" statements so we can get a feel for the problem...and perhaps the intended result set...

What do you mean by "combine the two recordsets together"???

Anyway, I can give you a couple of tips for this SP.....

1) It seems you are only using the @ID local variable for a existence check...(See Below)
2) You can use table "Aliases" to shorten your code (See Below)

Here is the revised code with the above 2 points demonstrated...


CREATE PROCEDURE dbo.SelectCardGroupToo
@txtUserId nvarchar(12),
@txtCompNum nvarchar(10),
@txtAcctNum nvarchar(16)
AS

--Existence check
IF EXISTS (SELECT 1 FROM UserGroups WHERE txtInternetNum = @txtCompNum AND txtAccountNum = @txtAcctNum)
BEGIN
--Notice the use of the "AS" Keyword to provide an Alias
SELECT DISTINCT UG.txtAccountNum, A.txtCompanyNum, A.txtName,A.txtAdd2
FROM ApprovalGroup AS AG 
INNER JOIN UserGroups AS UG ON AG.txtInternetNum = UG.txtInternetNum AND AG.intViewGroup = UG.lngGroupNum 
INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum AND UG.txtInternetNum = A.txtInternetCompNum 
WHERE AG.txtUserID = @txtUserId AND AG.txtInternetNum = @txtCompNum
ORDER BY A.txtName
END

ELSE
	Print 'Not found'

DavidM

Tomorrow is the same day as Today was the day before.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-01-30 : 17:27:52

Thanks for the help, guys.
Here's my revised SP:
CREATE PROCEDURE dbo.SelectCardGroupToo
@txtUserId nvarchar(12),
@txtCompNum nvarchar(10),
@txtAcctNum nvarchar(16)
AS

DECLARE @ID int

-- check if account number for an internet number exists
SELECT @ID = IdentityCol FROM UserGroups
WHERE txtInternetNum = @txtCompNum AND txtAccountNum = @txtAcctNum

-- if user's acct num is in the the list, grab all cardholders
IF @ID Is NULL
(SELECT DISTINCT
UserGroups.txtAccountNum, Account.txtCompanyNum, Account.txtName,
Account.txtAdd2
FROM ApprovalGroup INNER JOIN
UserGroups ON
ApprovalGroup.txtInternetNum = UserGroups.txtInternetNum AND
ApprovalGroup.intViewGroup = UserGroups.lngGroupNum
INNER JOIN Account ON
UserGroups.txtAccountNum = Account.txtAccountNum AND
UserGroups.txtInternetNum = Account.txtInternetCompNum
WHERE (ApprovalGroup.txtUserID = @txtUserId) AND
(ApprovalGroup.txtInternetNum = @txtCompNum) )
UNION
(SELECT txtAccountNum, txtCompanyNum, txtName, txtAdd2
FROM Account
WHERE txtAccountNum = @txtAcctNum)
ORDER BY Account.txtName
Else
SELECT DISTINCT
UserGroups.txtAccountNum, Account.txtCompanyNum, Account.txtName,
Account.txtAdd2
FROM ApprovalGroup INNER JOIN
UserGroups ON
ApprovalGroup.txtInternetNum = UserGroups.txtInternetNum AND
ApprovalGroup.intViewGroup = UserGroups.lngGroupNum
INNER JOIN Account ON
UserGroups.txtAccountNum = Account.txtAccountNum AND
UserGroups.txtInternetNum = Account.txtInternetCompNum
WHERE (ApprovalGroup.txtUserID = @txtUserId) AND
(ApprovalGroup.txtInternetNum = @txtCompNum)
ORDER BY Account.txtName

Is there something wrong with the code because when I run this from my ASP and if @id is null, then it should return two records (i.e, it ran the if statement and not the else). But apparently with the recordset I'm getting on my asp page, it's the else that is being executed.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-30 : 17:35:47

Peter,

Us the existence check as outlined...

But I think you mean IS NOT NULL....

DavidM

Tomorrow is the same day as Today was the day before.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-01-30 : 17:35:54

I forgot to mention taht when I ran the query with the union part on query analyzer I get the correct recordset.

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-30 : 18:01:02

Actually,

The 'AS' is not even required. Some like it for readability, but I usually just use:
SELECT *
FROM table t inner join othertable o
ON t.id=o.id

Peter,

Use the existence check David suggests rather than the whole @ID thing.

-Chad

PeterG
Posting Yak Master

156 Posts

Posted - 2002-01-30 : 18:06:26

Thanks for the all the help. I changed the existense check to what was suggested and I got the correct recordset I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources