2

I have a stored procedure in SQL Server that executes like this

EXEC @return_value = SP
       @value1 = 'A',
       @value2 = 'B'

and the result is:

value3 | value 4 | value 5

I need to add the columns value1 and value2 to the result (yes, the same I use to execute the stored procedure). There is no chance to change the stored procedure, because it is from another project. Is it possible to do?

I work often with Oracle, so I don't know if maybe it's something simple, or maybe it can't be done.

Thanks for your answers!

S3S
  • 24,809
  • 5
  • 26
  • 45
Alan Bry
  • 27
  • 1
  • 7
  • *Why* do you want to add two static values? What is the *actual* problem you are trying to solve? This almost sounds like you are trying to do reporting work on the server, eg attaching labels. Or that you used a stored procedure when you really wanted a view or a function – Panagiotis Kanavos Sep 22 '16 at 15:06
  • because one of the values are the date, and i need to know the date for the rest of the values. It's almoust obvious that it must be one of the results from the SP, but since i cannot modify it and in the need to know all the values for a year, i don't want to execute it 365 times. – Alan Bry Sep 22 '16 at 15:47

2 Answers2

2

Store the Proc Results to a temp table.

https://stackoverflow.com/a/1228165/6167855

Then do something like

SELECT @value1, @value2, t.* FROM #TempTable t
Community
  • 1
  • 1
S3S
  • 24,809
  • 5
  • 26
  • 45
  • I do something similiar, but i forgot to menction that i'm using Sql Server 2000, so i cannot do that (Supported since SQL Server 2008). – Alan Bry Sep 23 '16 at 18:34
  • Oh wow @AlanBry. You should really upgraded since 2000 hasn't been supported since April 2013. – S3S Sep 23 '16 at 18:37
0

With out changing the stored proc ,one way to get desired result is 

select *,
@value1,
@value2  
from openrowset
('SQLNCLI',
'Server=yourinstancename;
Trusted_Connection=yes;',
'EXEC yourproc ''variables''')

Note,if you get two result sets,second one will be ignored

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • "Access to the remote server is denied because no login-mapping exists", it looks good, but... – Alan Bry Sep 22 '16 at 15:09
  • @AlanBry:This link seems to provide resolution..https://blogs.technet.microsoft.com/mdegre/2011/03/10/access-to-the-remote-server-is-denied-because-no-login-mapping-exists/ – TheGameiswar Sep 22 '16 at 15:12