Solved

Using EXEC within a Select Statemet in SQL?

Posted on 2011-02-22
2
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Convert rows to columns 5 33
SQL - Aging Report - Display Months with no data 8 43
When are cursors useful? 8 60
SQL - Subquery in WHERE section 4 33
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

734 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