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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-09 : 08:02:08
|
| Stuart writes "Is it possible to get the output (note: not "an output parameter") of a stored procedure into a variable in SQL Server 7? I could then use this technique to get a text version of the result set from an arbitrary query into a variable, using an analogue to "@myresultvar = sp_executesql @myarbitrarystringofsql". Sadly, I can't find a way of doing this; I can create a temporary table and insert the results of a stored procedure into it, but because the SQL being executed is an arbitrary string I don't know how many columns to put into the temporary table (and hence the INSERT INTO won't work). I'm pulling my hair out, here: help!" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-09 : 08:56:31
|
| If the rowset generated by your proc has multiple columns, which column would go into your variable?<O> |
 |
|
|
sil
Starting Member
2 Posts |
Posted - 2002-07-09 : 10:05:34
|
quote:
If the rowset generated by your proc has multiple columns, which column would go into your variable?
I don't want a column; that's why I said "a test version of the result set". What I want is the text of the query output, the same thing that Query Analyser gives you or that xp_sendmail appends to a mail (the results of @query). |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-09 : 10:22:35
|
| Don't confuse output with display; Query Analyzer may take the rowset returned from a DML statement and show it to you as a text buffer, but that certainly doesn't mean SQL Server is returning a text buffer. It isn't.There isn't a good way to accomplish what you're describing here, given the detail you've provided. Often that means the database structures you're working with need modification. Post the DDL for the tables involved and give us some background for the business problem.Jonathan Boott, MCDBA |
|
|
|
sil
Starting Member
2 Posts |
Posted - 2002-07-10 : 10:41:04
|
| What I want to do is provide a stored procedure which takes exactly the same parameters as xp_sendmail but uses a third-party COM object (instantiated with sp_oaCreate) to send the mail. I'd like the sp to be generic (so that more than one application can use it) and hence I'd like to be able to pass text to it to be executed as a query. I can't think of a way of doing this other than to have the COM object connect back to the SQL server and execute said text as a query (using, say, ADO) which seems to me to be a waste, given that we're initiating the process from SQL Server anyway (and, additionally, would require the COM object to be passed or to know an appropriate username/password combination). I appreciate that SQL Server doesn't return a text buffer from queries, but xp_sendmail must manage it somehow, and I wondered whether that ability was replicable within SQL Server itself (rather than doable by writing an extension DLL). |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-07-10 : 10:59:46
|
| You could use bcp :bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppasswordbcp the results to a file, and then call the mail program to send the results file. I admit it's not a very nice way, but an option. |
|
|
|
|
|
|
|
|
Getting stored procedure results into a variable