store proc help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

store proc help

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2011-11-18 : 10:08:20

create procedure sp_test
as
select s.id,s.name,s.type from sysobjects s where s.type = 'U'

Is it possible to get one more column from store proc like ...

exec sp_test + 'test msg' as message

like

id, name, type, message
............................
7931350 SPONSOR U test msg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:23:11

not like this
for that you've to do like


select *,'test msg' as message from OPENROWSET('SQLOLEDB','Data Source=<Servernamehere>;Trusted_Connection=yes;
          Integrated Security=SSPI','exec sp_test')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:24:20

another way is by using a temporary table


insert #temp 
exec sp_test

select *, 'test msg' as message from #temp

the temp table should have same structure as resultset of sp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources