'exec' code help - 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)

'exec' code help

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-28 : 12:48:20

I'm not sure why I'm getting an error in this code:

***************************


Declare @users varchar(8000)
SELECT 	@users = COALESCE(@users + '; ', '') + 'exec sp_isowi_cdontsmail_notify '''+ 
	CAST(pin AS varchar(15)) + '''' + ',''' + wi + '''' + ',''' + CAST(trainers AS varchar(15)) + ''''
FROM dbo.iso_tmpmail2

SELECT @users

exec @users

***************************

The error I get:

(1 row(s) affected)

Server: Msg 203, Level 16, State 2, Line 8
The name 'exec sp_isowi_cdontsmail_notify '170','QP4-1','105'; exec sp_isowi_cdontsmail_notify '37','WI6-3-530','37'; exec sp_isowi_cdontsmail_notify '170','WI4-2-002','105'; exec sp_isowi_cdontsmail_notify '170','WI4-2-008','105'' is not a valid identifier.

However, if I construct the execution string manually, it works fine:

exec sp_isowi_cdontsmail_notify '170','QP4-1','105'; exec sp_isowi_cdontsmail_notify '170','QP5-1','105'
. . . etc

Thanks, JLM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-28 : 12:56:33

i think the problem is that you are "exec"ing an EXEC.