Problem with Proc and multi where clause statment.

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 2005 Forums

Transact-SQL (2005)

Problem with Proc and multi where clause statment.

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-04-17 : 10:22:47

I am trying to make a proc with this code:

create proc AddImages (
	@Error smallint output, @ImageName varchar(50), @Game varchar(25), @SubSet varchar(25), @FullSubSet varchar(75), @Width smallint, @Height smallint, @AltText varchar(50)
) as
declare @AbbreviationExists varchar(25), @ImageExists varchar(25)

set @AbbreviationExists = (select Short from Eaglef90.Abbreviations where Short = @SubSet)
set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)
set @Error = 0

if @AbbreviationExists is null
	insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet)

if @ImageExists is null
	insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText)
else
	set @Error = 1

but when I hit execute in Query Analizer I recive this error:

Msg 116, Level 16, State 1, Procedure AddImages, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Line 7 has this code on it:

set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)

Does anyone know what is wrong with that select statement?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 10:32:46

set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)

What (single) value are you trying to set @ImageExists to?
maybe
set @ImageExists = (select ImageName from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)

You don't need the set and subquery you could

select @ImageExists = ImageName from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet

==========================================
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.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-04-17 : 12:18:41

Thanks that is such an obvious answer I can't belive I over looked it. For the select vs. set what are the advantages/disadvantage?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-17 : 14:23:23

You could also get rid of the "assignment" portion altogether:

if NOT EXISTS (select * from Eaglef90.Abbreviations where Short = @SubSet)
	insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet)

if NOT EXISTS (select * from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)
	insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText)
else
	set @Error = 1

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2008-04-18 : 09:01:58

quote:

Originally posted by Lamprey

You could also get rid of the "assignment" portion altogether:

if NOT EXISTS (select * from Eaglef90.Abbreviations where Short = @SubSet)
	insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet)

if NOT EXISTS (select * from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)
	insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText)
else
	set @Error = 1

Thanks I am still pritty bad at T-SQL so any help is apreciated.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources