Select Statement in a 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)

Select Statement in a SP

Author

Topic

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-07-01 : 18:20:03

Here is the SP I am trying to execute. When tried to create it I get the following error

IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'Dipper_TO_DTDB' AND type = 'P')
DROP PROCEDURE Dipper_TO_DTDB
GO

CREATE PROCEDURE Dipper_TO_DTDB

AS

Declare @rowcntr int, @dip_incident_id int,@dipper_inc_count int

SET NOCOUNT ON
Select @dip_incident_id = incident_id from Dipper_incident
Set @rowcntr = 1
WHILE (@rowcntr <= @dipper_inc_count)
BEGIN

Print 'Row Count' + @rowcntr+ Convert(varchar(10),@dip_incident_id)
Set @rowcntr = @rowcntr + 1

END

SET NOCOUNT OFF

GO

Error:
Column 'Dipper_incident.INCIDENT_ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

I am basically trying to list all the incident id with a counter. Can you please tell where I am going wrong?

Thanks much!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-02 : 00:46:05

Did you post the full code you used?
This error occurs only if you select a column in the select statement grouping by other column and without grouping that column
Where did you assign value for @dipper_inc_count?

Madhivanan

Failing to plan is Planning to fail

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-07-02 : 14:33:33

Here is the full code.. I was trying something and forgot to put the actual code in. Thanks for your help! Where am I going wrong?

Select @dipper_inc_count = count(*), @dip_incident_id = incident_id from Dipper_incident
Set @rowcntr = 1
WHILE (@rowcntr <= @dipper_inc_count)
BEGIN

Print 'Row Count' + @rowcntr+ Convert(varchar(10),@dip_incident_id)
Set @rowcntr = @rowcntr + 1

END

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-07-02 : 15:00:46

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-02 : 16:03:34

This code doesn't make any sense. What if there's more than one incident_id in the Dipper_incident table? How do you know which one to select? Outside of that, if you are going to use count(*) like that, you will have to GROUP BY incident_id if you want this to work.

Since the statement won't work anyway, I would recommend starting over though.

Think about what you're doing. A variable can only hold one value at a time.

Here is an example of what your code would do if you got it working:


CREATE TABLE Dipper_incident(incident_id INT, col1 INT)

INSERT Dipper_incident(incident_id, col1)
	SELECT 1,1 UNION ALL
	SELECT 2,2 UNION ALL
	SELECT 3,3 UNION ALL
	SELECT 4,4 UNION ALL
	SELECT 5,5 UNION ALL
	SELECT 5,6 UNION ALL
	SELECT 5,7

DECLARE @rowcntr INT, @dip_incident_id INT,@dipper_inc_count INT

SELECT 
	@dipper_inc_count = COUNT(*), 
	@dip_incident_id = incident_id 
FROM Dipper_incident
GROUP BY incident_id

SELECT @rowcntr = 1

WHILE (@rowcntr <= @dipper_inc_count)
	BEGIN
	
		PRINT 'Row Count' + CONVERT(VARCHAR(10),@rowcntr) + CONVERT(VARCHAR(10),@dip_incident_id) 
		SET @rowcntr = @rowcntr + 1

	END

DROP TABLE Dipper_incident

Result Set:


Row Count15
Row Count25
Row Count35

Are you looking for something like this?


CREATE TABLE Dipper_incident(incident_id INT, col1 INT)

INSERT Dipper_incident(incident_id, col1)
	SELECT 1,1 UNION ALL
	SELECT 2,2 UNION ALL
	SELECT 3,3 UNION ALL
	SELECT 4,4 UNION ALL
	SELECT 5,5 UNION ALL
	SELECT 5,6 UNION ALL
	SELECT 5,7

SELECT 
	'The row count for incident_id ' + CAST(incident_id AS VARCHAR(55)) + ' = ' + CAST(COUNT(incident_id) AS VARCHAR(55))
FROM Dipper_incident
GROUP BY incident_id

DROP TABLE Dipper_incident

The result would then be:


The row count for incident_id 1 = 1
The row count for incident_id 2 = 1
The row count for incident_id 3 = 1
The row count for incident_id 4 = 1
The row count for incident_id 5 = 3

If this is what you are trying to achieve, you should just return a recordset with the incident_id and count. Let the presentation layer add the rest and format.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources