SQL Help - Stored Procedure and Using a Subquery

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

Transact-SQL (2000)

SQL Help - Stored Procedure and Using a Subquery

Author

Topic

RAVSKINS
Starting Member

21 Posts

Posted - 2009-11-02 : 16:17:53

Totally overwhelmed with this project I'm working on right now, and the due date is breathing down my neck. Brief explanation, my customers want a drop down menu to show their specific Team Leads names at the top of the list. This will vary depending on the Job_Number prefix that they are associated with. For example, all Job_Numbers beginning with '32-' should display 'Donald Duck' as the first available name, whereas all '33-' Job_Numbers should display 'Goofy' as the first option. Right now, the SP just displays an alphabetical list of names. So my course of action was to modify the existing SP to display the names based on Job_Number, however, I think that my SQL syntax is jacked up. The following is the original SP, followed by my modified version:

* * * Original Stored Procedure * * *

CREATE PROCEDURE [dbo].[GetSendReqToCustUsers] @Segment_ID VarChar(36),
@Customer_ID VarChar(36),
@Level_Number int,
@Level_Type VarChar(50),
@SEMS_User_ID VarChar(36),
@DeptChecked int
AS
SELECT CONVERT(VarChar(36), dbo.SEMS_USER.SEMS_USER_ID) AS SEMS_User_ID, dbo.SEMS_USER.First_Name + ' ' + dbo.SEMS_USER.Last_Name AS Full_Name
FROM dbo.SubmitFromXrefSubmitTo INNER JOIN
dbo.SEMS_USER ON dbo.SubmitFromXrefSubmitTo.Level_Type_To_ID = dbo.SEMS_USER.Level_Type_id AND
dbo.SubmitFromXrefSubmitTo.Level_No_To_ID = dbo.SEMS_USER.Level_No_Id INNER JOIN
dbo.Level_No ON dbo.SubmitFromXrefSubmitTo.Level_No_From_ID = dbo.Level_No.Level_No_ID INNER JOIN
dbo.Level_Type ON dbo.SubmitFromXrefSubmitTo.Level_Type_From_ID = dbo.Level_Type.Level_Type_ID INNER JOIN
dbo.PersonnelS ON dbo.SEMS_USER.SEMS_USER_ID = dbo.PersonnelS.Id
WHERE (dbo.SubmitFromXrefSubmitTo.Segment_ID = CAST(@Segment_ID AS UniqueIdentifier)) AND (dbo.SubmitFromXrefSubmitTo.Customer_ID = CAST(@Customer_ID AS UniqueIdentifier))
AND (dbo.Level_No.Level_Number = @Level_Number) AND (dbo.Level_Type.Level_Type = @Level_Type) AND (NOT (dbo.SEMS_USER.Deleted = 1)) AND
(NOT (dbo.Level_Type.Deleted = 1)) AND (NOT (dbo.PersonnelS.IsTerminated = 1))
ORDER BY dbo.SEMS_USER.Last_Name, dbo.SEMS_USER.First_Name
GO

* * * Modified Stored Procedure * * *

CREATE PROCEDURE [dbo].[GetSendReqToCustUsers_TEST]
@Segment_ID VarChar(36),
@Customer_ID VarChar(36),
@Level_Number int,
@Level_Type VarChar(50),
@SEMS_User_ID VarChar(36),
@DeptChecked int
AS
IF SELECT *
FROM dbo.Request
WHERE dbo.Request.Job_Number LIKE '32-%'
BEGIN SELECT CONVERT(VarChar(36),
dbo.SEMS_USER.SEMS_USER_ID) AS SEMS_User_ID,
dbo.SEMS_USER.First_Name + ' ' + dbo.SEMS_USER.Last_Name AS Full_Name
FROM dbo.SubmitFromXrefSubmitTo INNER JOIN
dbo.SEMS_USER ON dbo.SubmitFromXrefSubmitTo.Level_Type_To_ID = dbo.SEMS_USER.Level_Type_id AND
dbo.SubmitFromXrefSubmitTo.Level_No_To_ID = dbo.SEMS_USER.Level_No_Id INNER JOIN
dbo.Level_No ON dbo.SubmitFromXrefSubmitTo.Level_No_From_ID = dbo.Level_No.Level_No_ID INNER JOIN
dbo.Level_Type ON dbo.SubmitFromXrefSubmitTo.Level_Type_From_ID = dbo.Level_Type.Level_Type_ID INNER JOIN
dbo.PersonnelS ON dbo.SEMS_USER.SEMS_USER_ID = dbo.PersonnelS.Id INNER JOIN
dbo.Request ON dbo.SEMS_USER.SEMS_USER_ID = dbo.Request.Actual_Author_Id INNER JOIN --(Added DPM, 10/XX/09)
dbo.Next_Approval_Defaults ON dbo.PersonnelS.ID = dbo.Next_Approval_Defaults.Primary_Id --(Added DPM, 10/XX/09)
WHERE (dbo.SubmitFromXrefSubmitTo.Segment_ID = CAST(@Segment_ID AS UniqueIdentifier))
AND (dbo.SubmitFromXrefSubmitTo.Customer_ID = CAST(@Customer_ID AS UniqueIdentifier))
AND (dbo.Level_No.Level_Number = @Level_Number)
AND (dbo.Level_Type.Level_Type = @Level_Type)
AND (NOT (dbo.SEMS_USER.Deleted = 1))
AND (NOT (dbo.Level_Type.Deleted = 1))
AND (NOT (dbo.PersonnelS.IsTerminated = 1))
ORDER BY dbo.SEMS_USER.Last_Name, dbo.SEMS_USER.First_Name
END
ELSE

IF (SELECT *
FROM dbo.Request
WHERE dbo.Request.Job_Number LIKE '33-%')
BEGIN SELECT CONVERT(VarChar(36), dbo.SEMS_USER.SEMS_USER_ID) AS SEMS_User_ID, dbo.SEMS_USER.First_Name + ' ' + dbo.SEMS_USER.Last_Name AS Full_Name
FROM dbo.SubmitFromXrefSubmitTo INNER JOIN
dbo.SEMS_USER ON dbo.SubmitFromXrefSubmitTo.Level_Type_To_ID = dbo.SEMS_USER.Level_Type_id AND
dbo.SubmitFromXrefSubmitTo.Level_No_To_ID = dbo.SEMS_USER.Level_No_Id INNER JOIN
dbo.Level_No ON dbo.SubmitFromXrefSubmitTo.Level_No_From_ID = dbo.Level_No.Level_No_ID INNER JOIN
dbo.Level_Type ON dbo.SubmitFromXrefSubmitTo.Level_Type_From_ID = dbo.Level_Type.Level_Type_ID INNER JOIN
dbo.PersonnelS ON dbo.SEMS_USER.SEMS_USER_ID = dbo.PersonnelS.Id INNER JOIN
dbo.Request ON dbo.SEMS_USER.SEMS_USER_ID = dbo.Request.Actual_Author_Id INNER JOIN --(Added DPM, 10/XX/09)
dbo.Next_Approval_Defaults ON dbo.PersonnelS.ID = dbo.Next_Approval_Defaults.Primary_Id --(Added DPM, 10/XX/09)
WHERE (dbo.SubmitFromXrefSubmitTo.Segment_ID = CAST(@Segment_ID AS UniqueIdentifier))
AND (dbo.SubmitFromXrefSubmitTo.Customer_ID = CAST(@Customer_ID AS UniqueIdentifier))
AND (dbo.Level_No.Level_Number = @Level_Number)
AND (dbo.Level_Type.Level_Type = @Level_Type)
AND (NOT (dbo.SEMS_USER.Deleted = 1))
AND (NOT (dbo.Level_Type.Deleted = 1))
AND (NOT (dbo.PersonnelS.IsTerminated = 1))
ORDER BY dbo.SEMS_USER.Last_Name, dbo.SEMS_USER.First_Name
END
ELSE

--repeated for '34-%'
--repeated for '35-%'
--repeated for '36-%'
--repeated for '37-%'
--Etc, etc
GO
-----------------------------

The modified SP returned the following errors:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 137, Level 15, State 1, Line 15
Must declare the variable '@Segment_ID'.

I humbly grovel at your feet for assistance. Thanks......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-02 : 16:25:26

You need to put parenthesis around the query for the IF.

IF (SELECT ...)
BEGIN
...
END
...

You'll get a boolean error at this point, so you probably want IF EXISTS but I'm not exactly clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

RAVSKINS
Starting Member

21 Posts

Posted - 2009-11-02 : 16:35:39

quote:
Originally posted by tkizer

You need to put parenthesis around the query for the IF.

IF (SELECT ...)
BEGIN
...
END
...

You'll get a boolean error at this point, so you probably want IF EXISTS but I'm not exactly clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Awesome - thanks for looking and replying. I modified the top portion to:

IF EXISTS (SELECT *
FROM dbo.Request
WHERE dbo.Request.Job_Number LIKE '32-%')

This took care of the first error
(Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.)

Still not sure about the remaining (second) error though:
Server: Msg 137, Level 15, State 2, Line 15
Must declare the variable '@Segment_ID'.

Any ideas? And thanks again for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-02 : 17:35:05

I don't get that error on my system, so I suspect you haven't posted the entire code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

RAVSKINS
Starting Member

21 Posts

Posted - 2009-11-02 : 17:39:07

quote:
Originally posted by tkizer

I don't get that error on my system, so I suspect you haven't posted the entire code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Okay - let me double check.

I'm using the following:

CREATE PROCEDURE [dbo].[GetSendReqToCustUsers_TEST]
@Segment_ID VarChar(36),
@Customer_ID VarChar(36),
@Level_Number int,
@Level_Type VarChar(50),
@SEMS_User_ID VarChar(36),
@DeptChecked int
AS
IF EXISTS (SELECT *
FROM dbo.Request
WHERE dbo.Request.Job_Number LIKE '32-%')
BEGIN (SELECT CONVERT(VarChar(36),
dbo.SEMS_USER.SEMS_USER_ID) AS SEMS_User_ID,
dbo.SEMS_USER.First_Name + ' ' + dbo.SEMS_USER.Last_Name AS Full_Name
FROM dbo.SubmitFromXrefSubmitTo INNER JOIN
dbo.SEMS_USER ON dbo.SubmitFromXrefSubmitTo.Level_Type_To_ID = dbo.SEMS_USER.Level_Type_id AND
dbo.SubmitFromXrefSubmitTo.Level_No_To_ID = dbo.SEMS_USER.Level_No_Id INNER JOIN
dbo.Level_No ON dbo.SubmitFromXrefSubmitTo.Level_No_From_ID = dbo.Level_No.Level_No_ID INNER JOIN
dbo.Level_Type ON dbo.SubmitFromXrefSubmitTo.Level_Type_From_ID = dbo.Level_Type.Level_Type_ID INNER JOIN
dbo.PersonnelS ON dbo.SEMS_USER.SEMS_USER_ID = dbo.PersonnelS.Id INNER JOIN
dbo.Request ON dbo.SEMS_USER.SEMS_USER_ID = dbo.Request.Actual_Author_Id INNER JOIN
dbo.Next_Approval_Defaults ON dbo.PersonnelS.ID = dbo.Next_Approval_Defaults.Primary_Id
WHERE (dbo.SubmitFromXrefSubmitTo.Segment_ID = CAST(@Segment_ID AS UniqueIdentifier))
AND (dbo.SubmitFromXrefSubmitTo.Customer_ID = CAST(@Customer_ID AS UniqueIdentifier))
AND (dbo.Level_No.Level_Number = @Level_Number)
AND (dbo.Level_Type.Level_Type = @Level_Type)
AND (NOT (dbo.SEMS_USER.Deleted = 1))
AND (NOT (dbo.Level_Type.Deleted = 1))
AND (NOT (dbo.PersonnelS.IsTerminated = 1)))
ORDER BY dbo.SEMS_USER.Last_Name, dbo.SEMS_USER.First_Name
END
ELSE

I'll run it again.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources