Stored Procedure help with error

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure help with error

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-05-11 : 15:20:30

Here's the error message.

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

Here's the code.


USE byrndb
GO

IF OBJECT_ID('spInsertClientIndividuals') IS NOT NULL DROP PROCEDURE spInsertClientIndividuals
GO
CREATE PROC spInsertClientIndividuals
	@IFirstName		varchar(50) = NULL,
	@ILastName		varchar(50) = NULL,
	@IAddress1		varchar(30) = NULL,
	@IAddress2		varchar(30) = NULL,
	@ICity			varchar(15) = NULL,
	@IState			int = 1,
	@IZipcode		varchar(10) = NULL,
	@IHomePhone		varchar(12) = NULL,
	@IWorkPhone		varchar(12) = NULL,
	@IWorkPhoneExt	varchar(4) = NULL,
	@IFaxPhone		varchar(12) = NULL,
	@ICellPhone		varchar(12) = NULL,
	@IEmail			varchar(50) = NULL,
	@IID			int = NULL,
	@FullName		varchar(150) = NULL
AS
INSERT clientIndividuals VALUES (UPPER(@IFirstName), UPPER(@ILastName),
								 UPPER(@IAddress1), UPPER (@IAddress2),
								 UPPER(@ICity), @IState, @IZipcode,
								 @IHomePhone, @IWorkPhone, @IWorkPhoneExt, @IFaxPhone, @ICellPhone,
								 @IEmail)
SET @IID = (SELECT * FROM clientIndividuals WHERE ClientIndividualID = IDENT_CURRENT('clientIndividuals'))
BEGIN
	SET @FullName = @ILastName + ', ' + @IFirstName
	INSERT clients VALUES (@IID, 0, @FullName)
END

What could be causing this error?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-11 : 15:22:07

[code]SET @IID = (SELECT * FROM clientIndividuals WHERE ClientIndividualID = IDENT_CURRENT('clientIndividuals'))[/code]You are selecting all the columns from Clientindivisuals and trying to push them all into @IID..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:23:15

the reason is because

SELECT * FROM clientIndividuals WHERE ClientIndividualID = IDENT_CURRENT('clientIndividuals')

returns resultset which you cant store in a variable @ID

you should be having a table variable instead and use


INSERT @TableVariable
SELECT * FROM clientIndividuals WHERE ClientIndividualID = IDENT_CURRENT('clientIndividuals')

and it should have same structure as what above select returns ie same columns as in resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources