Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use of Having In a stored procedure 1

Status
Not open for further replies.
Dalel23

Dalel23

Programmer
Jun 5, 2007
16
US
I am using VB as a front-end. I would like to call a stored procedure by passing a parameter with multiple value. I having difficulties creating the stored procedure. The use of Having is because I could have multiple values seletected by the user.

CREATE PROCEDURE dbo.procLoadDocumentationRecords

@lngEquipID int

AS
SET NOCOUNT ON
SELECT dbo.tblEquipment.EquipmentNo, dbo.tblEquipment.Description, dbo.tblEquipment.EquipID, dbo.tblDocumentation.DocumentationID,
dbo.tblDocumentation.HECPID, dbo.tblDocumentation.EquipmentNoFk, dbo.tblDocumentation.PrDevelopedBy, dbo.tblDocumentation.PrApprovedBy,
dbo.tblDocumentation.DateApproved, dbo.tblUserGroups.UserFullName
FROM dbo.tblDocumentation LEFT OUTER JOIN
dbo.tblEquipment ON dbo.tblDocumentation.EquipmentNoFk = dbo.tblEquipment.EquipID LEFT OUTER JOIN
dbo.tblUserGroups ON dbo.tblDocumentation.PrApprovedBy = dbo.tblUserGroups.UserGroupsID

GROUP BY dbo.tblEquipment.EquipmentNo, dbo.tblEquipment.Description, dbo.tblEquipment.EquipID, dbo.tblDocumentation.DocumentationID,
dbo.tblDocumentation.HECPID, dbo.tblDocumentation.EquipmentNoFk, dbo.tblDocumentation.PrDevelopedBy, dbo.tblDocumentation.PrApprovedBy,
dbo.tblDocumentation.DateApproved, dbo.tblUserGroups.UserFullName


SET NOCOCOUNT OFF

How do I create the stored procedure and how do I call it from VB?

Any Help will be greatly appreciated

Thank you
 
Sort by date Sort by votes
Your stored procedure doesn't use the variable that you have defined. It also doesn't have a HAVING clause in it.

I'm not sure where your question is? Can you please elaborate?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
Hi mrdenny

Thank you very much for the response.

Pretty much what I am doing is trying to convert an existing sql statement to a stored procedure, and then call the stored procedure by passing the correct parameter from VB.
The sql statement that I am trying to convert to a stored procedure is as follow

I am using GetRecords function to send the sql and store the recordset in rsRecD
Set rsRecD = GetRecords("SELECT tblEquipment.EquipID, tblEquipment.EquipmentNo, tblEquipment.Description, tblShutDownProcedure.ShutDownID, tblShutDownProcedure.ShutDownProcedure, tblShutDownProcedure.LockType, tblShutDownProcedure.LocationID, tblShutDownProcedure.bDeenergizedVerified, tblShutDownProcedure.HowEnegyStateVerifiedID, tblDeenergizedStateVerifiedILookUP.DeenergizedStateVerified, tblShutDownProcedure.EquipmentNoFk, tblShutDownProcedure.HECPID, tblEnergyLockType.TypeOfLock, tblShutdownLocation.ShutDownLoc " & _
"FROM (((tblShutDownProcedure LEFT JOIN tblEquipment ON tblShutDownProcedure.EquipmentNoFk = tblEquipment.EquipID) LEFT JOIN tblEnergyLockType ON tblShutDownProcedure.LockType = tblEnergyLockType.TypeOfLockID) LEFT JOIN tblShutdownLocation ON tblShutDownProcedure.LocationID = tblShutdownLocation.ShutDownLocID) LEFT JOIN tblDeenergizedStateVerifiedILookUP ON tblShutDownProcedure.HowEnegyStateVerifiedID = tblDeenergizedStateVerifiedILookUP.DeenergizedStateVerifiedID " & _
"WHERE ((tblShutDownProcedure.HECPID) = " & rsHECP.Fields("HECPID") & ")" & _
"GROUP BY tblEquipment.EquipID, tblEquipment.EquipmentNo, tblEquipment.Description, tblShutDownProcedure.ShutDownID, tblShutDownProcedure.ShutDownProcedure, tblShutDownProcedure.LockType, tblShutDownProcedure.LocationID, tblShutDownProcedure.bDeenergizedVerified, tblShutDownProcedure.HowEnegyStateVerifiedID, tblDeenergizedStateVerifiedILookUP.DeenergizedStateVerified, tblShutDownProcedure.EquipmentNoFk, tblShutDownProcedure.HECPID, tblEnergyLockType.TypeOfLock, tblShutdownLocation.ShutDownLoc " & _
"Having " & strSelectedEquip)

the variable strSelectedEquip could contain a string a values based on the user selection such as '234,345,23'
The values are integers

Thank you so much for your help
I hope I was clear this time with my question
 
Upvote 0 Downvote
Here is the procedure code.
Code: 
CREATE PROCEDURE usp_Something
	@HECPID int
AS
SELECT tblEquipment.EquipID, 
	tblEquipment.EquipmentNo, 
	tblEquipment.Description, 
	tblShutDownProcedure.ShutDownID, 
	tblShutDownProcedure.ShutDownProcedure, 
	tblShutDownProcedure.LockType, 
	tblShutDownProcedure.LocationID, 
	tblShutDownProcedure.bDeenergizedVerified, 
	tblShutDownProcedure.HowEnegyStateVerifiedID, 
	tblDeenergizedStateVerifiedILookUP.DeenergizedStateVerified, 
	tblShutDownProcedure.EquipmentNoFk, 
	tblShutDownProcedure.HECPID, 
	tblEnergyLockType.TypeOfLock, 
	tblShutdownLocation.ShutDownLoc 
FROM tblShutDownProcedure 
LEFT JOIN tblEquipment ON tblShutDownProcedure.EquipmentNoFk = tblEquipment.EquipID
LEFT JOIN tblEnergyLockType ON tblShutDownProcedure.LockType = tblEnergyLockType.TypeOfLockID
LEFT JOIN tblShutdownLocation ON tblShutDownProcedure.LocationID = tblShutdownLocation.ShutDownLocID
LEFT JOIN tblDeenergizedStateVerifiedILookUP ON tblShutDownProcedure.HowEnegyStateVerifiedID = tblDeenergizedStateVerifiedILookUP.DeenergizedStateVerifiedID
WHERE tblShutDownProcedure.HECPID = @HECPID

The group by isn't needed as you don't have any calculated fields in your query. You'll also notice that I've removed a bunch of the brackets. They weren't needed either.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
Thank you so much for the code.
I just have a question. Will the where clause handle more than one variable? Because that's what could happened.
 
Upvote 0 Downvote
Let me clarify one more thing please
The were clause should look like this
WHERE tblShutDownProcedure..EquipmentNoFk = @EquipmentList

I am retrieving by equipments. The goal is to pass multivalued variable to a stored procedure.
I believe the where clause will only handle one value parameter at the time. That is why I was using the having clause in my sql statement. Now how can creat the stored procedure to allow more than one parameter?

Please let me know if you need any more qualificaions?

Thank you
 
Upvote 0 Downvote
Nope. Sounds like you need to use dynamic SQL...
Code: 
...'WHERE tblShutDownProcedure..EquipmentNoFk IN (' & @EquipmentList & ')

...using a comma separated list.



< M!ke >
I am not a hamster and life is not a wheel.
 
Upvote 0 Downvote
Hi LNBruno
Thank you for segment of code.
Now how do I define the data type of @EquipmentList in my stored procedure? The EquipmentNoFk is defined as an integer in my table
 
Upvote 0 Downvote
Since it's going to have to contain commas as delimiters, it will probably need to be a varchar and you'll have to use CAST or CONVERT on it....

< M!ke >
I am not a hamster and life is not a wheel.
 
Upvote 0 Downvote
I tried executing the stored procedure

exec myStoredProcedure '550', '553'

The where clause of stored procedure now looks like this
Where tblShutDownProcedure.EquipmentNoFk In (' & @EquipmentList & ')

It's not working. It says 'Procedure of function has too many arguments specified'
Any idea what I am doing wrong

Also the parameter is defined as
@EquipmentList int
 
Upvote 0 Downvote
I have no idea how to cast on this dynamic sql.
Could you help me write the stored procedure please?

Thank you

 
Upvote 0 Downvote
Let's start with this:

Code: 
exec myStoredProcedure  '550', '553'

The use of single quotes makes these values non-numeric. That they are not enclosed in parentheses makes them two parameters, not one (which is what you have your procedure set up to receive).

Next:

Code: 
Where tblShutDownProcedure.EquipmentNoFk In (' & @EquipmentList & ')

You can't simply change the WHERE clause like this to create a dynamic SQL statement.

And then:

Also the parameter is defined as
@EquipmentList int
Click to expand...

I specifically said
Since it's going to have to contain commas as delimiters, it will probably need to be a varchar and you'll have to use CAST or CONVERT on it....
Click to expand...

Perhaps this link will be of greater, more immediate value:


< M!ke >
I am not a hamster and life is not a wheel.
 
Upvote 0 Downvote
I am familiar with sql language. Not that I am an expect by any mean. I just wanted to find out how to handle a multivalue parameter in my stored procedure.
The link you sent can not help me in any way. But Thank you. I am just looking for some directions.
 
Upvote 0 Downvote
ESquared Thank you very much. That is exacly what I needed.
And I would like to also thank everybody else for thier valuable help.

Thank you again

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
174
grnzbra
grnzbra
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
398
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
363
BobSuruncle
BobSuruncle
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
230
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top