0

In this query :

DECLARE @TempPerformance TABLE(FeatureTitle VARCHAR(50), FeatureText VARCHAR(50))

INSERT INTO @TempPerformance 
EXEC [usp_GetPerfomance] @Value

It some times gives 2 columns and sometime one column only..It does not work when it returns only 1 column as the temporary table is not designed so..

How can I differentiate between these 2 results.

EDIT

If the SP fails..it returns one column - one row as :

Standard
--------
No Records Found
James
  • 2,136
  • 3
  • 23
  • 42
  • 2
    You need to change the stored procedure so that each possible result set that it produces has the same "shape" - the same number of columns, the same types for each of those columns, ideally the same names for these columns (for most scenarios, column names are more important than position, but this is one of the few where it's not actually a requirement). – Damien_The_Unbeliever Oct 23 '13 at 09:57
  • The code for usp_GetPerformance is relevant, but you didn't provide it. – Dan Bracuk Oct 23 '13 at 09:59
  • Since I am not able to change the SP...as lots of dependency are there..so I dint find it relevant.. – James Oct 23 '13 at 10:00
  • @Damien_The_Unbeliever - Is there any other way to work around here..as I cant change the SP.. – James Oct 23 '13 at 10:01
  • Unfortunately, there's no way around it unless you can tell yourself from the value of `@Value` what result is going to be returned and can set up different tables to hold the result. See [INSERT](http://technet.microsoft.com/en-us/library/ms174335.aspx): "If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list." – Damien_The_Unbeliever Oct 23 '13 at 10:02
  • Yes..that makes sense..I think there is no way to deal with this in SQL level. – James Oct 23 '13 at 10:06

2 Answers2

1

There is one way. Using OPENROWSET you can get the result of stored procedure into dynamicly created (temp) table. More about it in this question. There could be issues with security and login problems.

Afterwards you can easily check for number of columns in your temp table using sys.columns

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
        '[usp_GetPerfomance] ' + CAST(@Value AS VARCHAR(MAX)));

SELECT COUNT(*) FROM tempdb.sys.columns WHERE object_id=object_id('tempdb..#MyTempTable');
Community
  • 1
  • 1
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
0

You can use OPENROWSET to execute a query and get the result as a table. Then you can use that table to verify whether you should handle it one way or the other.

If you go to the link and look at the section:

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

then you can replace the SELECT with the EXEC yourDatabase.Schema.Procedure.

It however requires you to reconfigure the server to Ad Hoc Queries, and you might not have the proper rights to do that. It's generally not a solid solution for a production environment, so I'd not personally recommend the method.

Allan S. Hansen
  • 4,013
  • 23
  • 25