| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-09-07 : 23:04:02
|
| In articlehttp://sqlteam.com/item.asp?ItemID=259EXEC is shown to work as a subquery inINSERT INTO #foo EXEC (@SQL)I cannot get the EXEC to work as a subquery for a select.Here's a simple exampleSELECT * FROM (SELECT TOP 5 ABC FROM TABLE ) BSELECT * FROM (EXEC (@sql)) B"Incorrect syntax near keyword exec."What am I doing wrong?SamC |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-09-08 : 04:52:21
|
| Curious, why not just EXEC @sql??? |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-08 : 09:04:10
|
| I was hoping to minimize the dynamic SQL.EmailToSQLQuery is a SELECT generated in an ASP Web page that was used to generate a user report. We want to email all users in that report.Here's the procedure. The challenge is can the temporary table be eliminated by placing the EXEC as a Subquery.SamC-----------------Create Procedure dbo.EMailQueueINSERT @ClientID INT , @AdminID INT , @EmailFrom varchar (200) , @EmailToSQLQuery varchar (2000) , @EmailSubject varchar (200) , @EmailBody varchar (6000) AS DECLARE @SQL NVARCHAR(4000) -- sp_executesql requires Nvarchar CREATE TABLE #temptable (UserID INT) -- All we want from @EmailToSQLQuery is the User primary key SET @SQL = 'SELECT UserID FROM ( '+@EmailToSQLQuery+' ) B ' -- Gotta isolate what we want INSERT INTO #temptable (UserID) EXEC sp_executesql @SQL -- too bad this has to go in a table INSERT INTO EmailQueue (AdminID, ClientID, EmailFrom, EmailTo, EmailSubject, EmailBody ) SELECT @AdminID, @ClientID, @EmailFrom, U.Email, @EmailSubject, @EmailBody FROM #temptable t INNER JOIN Users U ON t.UserID=U.UserID -- Now we have access to everything about this user DROP TABLE #temptable -- clean up the messGO |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-08 : 09:59:23
|
| You could probably do it using openrowset or such but that would create a new connection. More overhead than temp table or table variable.Given that you already have dynamic sql here probably better to do the insert into EmailQueue as dynamic sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-08 : 10:00:04
|
| You could probably do it using openrowset or such but that would create a new connection. More overhead than temp table or table variable.Given that you already have dynamic sql here probably better to do the insert into EmailQueue as dynamic sql.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-08 : 10:12:42
|
| I am not really unhappy with this procedure or it's performance.I have taken a beating (trial and error) trying to figure out where EXEC can be used as a subquery, and I don't see much written on it.Seems to me it ought to be completely symmetric, but it doesn't seem to be from my tests.Does anyone have a rhyme or reason that can guide that application of EXEC as a subquery?SamC |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-08 : 10:23:25
|
| Basically, it can't, except in an OPENQUERY call as Nigel described. The INSERT...EXEC syntax is a T-SQL extension that is not part of the ANSI SQL standard. When you think about it, it really doesn't make sense to have EXEC(@sql) as part of a subquery; you can simply modify the SQL string to incorporate the outer SELECT statement, then execute the entire modified string.In looking at the code you have, it appears you want to pass a query that incorporates the UserIDs that should receive the email. Why not simply pass a list of UserIDs as a comma-separated value parameter? There's a couple of reasons why this is better:A. If not, then the query MUST include the UserID column always, and there's no guarantee that the SQL string that is passed will do so. This can cause a runtime error.B. The query that is passed may not return any rows (empty set) and will also cause an error.C. You can separate the user list from what would be returned by the query itself; it would be more flexible this way than perhaps tweaking a query to get the right users.Can you shed some more light on what this procedure is supposed to accomplish? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-08 : 11:19:46
|
| First, I read the article on CSV into rows using SELECT last night. Very neat application of the Tally / ID table.I think I'm good here - I was looking at the INSERT INTO #foo EXEC (@SQL) and spent a fair amount of time unsucessfully trying to work EXEC into other subquery situations. The procedure works well as it is, but I was nagging myself that there might be a way to use the EXEC as a subquery to improve the elegance more than anything.The answer I was looking for (and found in this post) was that the EXEC is not supported as a subquery and the INSERT INTO is an exception.To answer your question about what I'm trying to do, the procedure is launched from an ASP page which contains a report of users. (This is done when an Administrator wants to send an email to the users in the report.) The ASP string SELECT statement used to identify those users is available, and knowing the select also found the Primary Key for the Users, it's pretty easy to use this "existing" query to build a list in the email queue without having to reconstruct the SELECT, and possibly reconstruct it inaccurately -missing users (seemed reliable to me to use the original query).The SELECT string is passed to the procedure as the string @EmailToSQLQuery, the primary key to the Users table (UserID) is taken and inserted into a temporary table (because the EXEC cannot be a subquery).Having the Primary Key to the Users table for all users in the report that we intend to email also gives me some future options. I could extract FirstName, LastName, Organization and by doing string substitution, I can customize the body of the email. (Not important right now to do this.)Thanks for your help.SamC |
|
|
|
|
EXEC in Subquery