Error Near Exec Statement - 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

General SQL Server Forums

New to SQL Server Programming

Error Near Exec Statement

Author

Topic

gp_330
Starting Member

6 Posts

Posted - 2005-09-19 : 08:21:51

Hi Guys, I have been trying to get this SQL string below to run, but for some reason I carry on getting an error near the EXEC Statements.

Does anyone have any idea what I doing wrong?

CREATE PROCEDURE [dbo].[UpdateWarnings]
@EndDate DateTime
AS
DECLARE @iid varchar(100)
DECLARE @fid varchar(100)

SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')

IF @iid <> ''
BEGIN
EXEC('UPDATE memorial SET active=0 WHERE id IN ('+ @iid +')')
END

SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')

IF @fid <> ''
BEGIN
EXEC('UPDATE memorial SET warning=(warning+1) WHERE id IN ('+ @fid + ')')
END

IF @enddate <> ''
BEGIN
UPDATE warnings SET startdate=getdate(), enddate=@enddate, warnings=(warnings + 1) WHERE id=1
END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 08:34:04

Replace

SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')

by

Select @iid = id FROM memorial WHERE warnings >= 3 AND expires <= getdate()

and

SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')

by

Selct @fid = id FROM memorial WHERE warnings < 3 AND expires <= getdate()

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources