I created a stored procedure to return me a select statement. See below:
select nameA from tableA Union Select nameB from tableBI tried the statement above in the Query Analyzer, and it returned to me a result set. But when I executed it from a stored procedure, I can't get a result set, but I get "The command(s) completed successfully." Did I miss some step to make it return a result set? I am using SQL Server 7.0.
I cannot duplicate the problem you are experiencing.
I, like you, created a stored procedure (using Northwind):
create procedure usp_mailingLabels as select firstName + ' ' + lastName as contactName from employees union select contactName from customersHowever, I was able to execute the procedure and get a result set:
exec usp_mailingLabels output: contactName Alejandra Camino Alexander Feuer Ana Trujillo . . . Yoshi Tannamuri Yvonne Moncada Zbyszek Piestrzeniewicz
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2002