SP eliminating Null Values - 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

Transact-SQL (2000)

SP eliminating Null Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-07 : 07:30:26

Rob writes "I have created a stored procedure to insert rows into a table, however whenever I execute the stored procedure I get the following error:

Warning: Null Value is eliminated by an aggregate or other SET operation.

My stored procedure reads:

CREATE PROCEDURE dbo.[360_InsertBlankRows]
(
@ParticipantId VarChar(31)
)
AS

INSERT INTO [360_QuestionResults] (Question, Role, GroupId)
SELECT QuestionNumber, Role, GroupNumber
FROM [360_Reference]
WHERE ((CAST(QuestionNumber AS VarChar) + CAST(Role AS VarChar) + CAST(GroupNumber AS VarChar)) NOT IN
(SELECT CAST(Question AS VarChar) + CAST(Role AS VarChar) + CAST(GroupId AS VarChar) AS Expr1
FROM [360_QuestionResults]))

I know that the SELECT/FROM/WHERE clases of the statment are correct because they select the rows that I want them to. My problem is that I cannot identify where I am making use of an aggregate, and hense why I am getting this problem.

Any assistance would be more than helpful,

regards,

Rob"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 07:59:14

Suspect it's from the in clause.
try

INSERT INTO [360_QuestionResults] (Question, Role, GroupId)
SELECT QuestionNumber, Role, GroupNumber
FROM [360_Reference] t
WHERE not exists (select * from [360_QuestionResults] t2 where t2.QuestionNumber = t2.Question and t.Role = t2.Role and t.GroupNumber = t2.GroupId)

or better

INSERT INTO [360_QuestionResults] (Question, Role, GroupId)
SELECT QuestionNumber, Role, GroupNumber
FROM [360_Reference] t
left join [360_QuestionResults] t2
on t2.QuestionNumber = t2.Question and t.Role = t2.Role and t.GroupNumber = t2.GroupId
where t2.Question is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources