Bulk insert problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update