Solved

Using EXEC within a Select Statemet in SQL?

Posted on 2011-02-22
2
276 Views
Last Modified: 2012-05-11
I'm using SQL 2008 RS.  Is it possible to perform a function like the code below?  Obviously the code does not work but I think it makes it clear what I'm trying to do.
Set @ChkItemCheckItemsXpath='/DailyData/Checks/Check/Seats/Seat/CheckItemRecord'


Select case when [GUEST_CHECK_NAME] = 'KJ Chardonnay' then 
		(EXEC msdb.dbo.sp_send_dbmail  @profile_name='MFMail',
		@recipients='5554545@txt.att.net',
		@subject='Void Alert',
		@body='GuestCheckName Test' )
    
		End
		
	 from OPENXML(@hDoc,@ChkItemCheckItemsXpath)  With                                    
       (   
        [GUEST_CHECK_NAME] [varchar](23) 'GuestCheckName'           
          ) XD

Open in new window

0
Comment
Question by:mattkovo
2 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34956699

You can not call a stored procedure this way within a select statement.

You need to break this down into simpler statements
 Have a statement that assigns the values for the email variables and another statement to send the email
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34957456
I suspect this is what you intended:
SET @ChkItemCheckItemsXpath = '/DailyData/Checks/Check/Seats/Seat/CheckItemRecord'


IF EXISTS ( SELECT  1
            FROM    OPENXML(@hDoc,@ChkItemCheckItemsXpath)  WITH  (
         [GUEST_CHECK_NAME] [varchar](23) 'GuestCheckName'           
          ) XD
            WHERE   [GUEST_CHECK_NAME] = 'KJ Chardonnay' ) 
    EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'MFMail', 
			@recipients = '5554545@txt.att.net', 
			@subject = 'Void Alert', 
			@body = 'GuestCheckName Test'

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question