one expression, select list and subquery

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

Transact-SQL (2008)

one expression, select list and subquery

Author

Topic

worddiva
Starting Member

1 Post

Posted - 2014-12-22 : 23:12:14

Below it the code, when I run it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I don't understand this - and that maybe because my coding skills are not up to snuff.
Note: I am looking for the job to fail so it will go to the next step where some else has set it up using cmdexec to send out an email to send the error file. Neither of us know how to send an email attaching a file with the results of a query showing the failing processes.
Looking for assistance - Thanks.

Set NoCount On
Declare @LastRun varchar (50)
Declare @CreateFail varchar(50)
Declare @FailureCount int
Declare @Fail int
Set @LastRun =
(SELECT Process_ID, Process_Name, Last_Message_Time
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
)

SELECT @@ROWCOUNT

SET @Fail = @@ROWCOUNT

If @Fail >= 1
Begin
Print 'There was a failure for the following processes: ' + @LastRun --
Set @Fail = 'Fail'
End
Else
Begin
Set @CreateFail = 'Success'
End

Let it roll to the fountain of perpetual mirth. Let it roll for all its worth.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-23 : 07:27:25

Hard without any data, but you are selecting multiple columns in the @Lastrun...?

Set NoCount On
Declare @LastRun varchar (50) =
(SELECT Process_ID, Process_Name, Last_Message_Time
FROM [ITF3].[dbo].[Process])
--Declare @CreateFail varchar(50) = 'Success' -- dont need it
--Declare @FailureCount int -- not used anywhere?
Declare @Fail int = @@ROWCOUNT

--WHERE Process_ID IN (1,2,3,4)
--and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)

Begin Tran
If @Fail >= 1 AND
Process_ID IN (1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
Begin
Print 'There was a failure for the following processes: ' + @LastRun
Commit Tran
Return
End
Else print 'Success'

We are the creators of our own reality!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-23 : 11:03:27

Set @LastRun =
(SELECT Process_ID, Process_Name, Last_Message_Time
...
You have a single variable but the SELECT list has 4 values: that's not valid or allowed, which is what the error message is.

It should be this:
Set @LastRun =
(SELECT Last_Message_Time
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources