0

I don't understand the nature of T-SQL select statements inside stored procedures. I come from the Oracle side and here it's for me well defined.

Hope this sample show my misunderstandings!

I have this stored procedure:

create procedure demo_1
as
begin
    select count(1) from sys.tables;
end;

Now I can execute this procedure for example via management studio.

exec demo_1

I'll get a nice result within management studio. this look well as person before the computer, but when I call this stored procedure via an 2nd stored procedure I can't do anything with this result.

I don't understand the idea behind this. print a not defined result to an console without any possibility to work inside the TSQL world.

The background for this is: I need a stored procedure which call the internal sp_fkeys procedure. after some hours of research I don't find a straight forward way to get the information from sp_fkeys and work with the result inside TSQL.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
oas_public
  • 263
  • 3
  • 6

1 Answers1

2

If you want to use the result set of a stored proc in another proc, create a temp table with the same columns that the proc returns in the calling proc. Then do an insert like this:

insert #test
exec myproc
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • but before you can do this, you have to create a temp tbl. and if i'm understand it right the cols for this temp tbl must look like the result. and here there is no contracting between the temp struct and the result from proc. in my special case: when i want use only one col from sp_fkeys proc i have to create a with temp tbl with many cols to get only one cole. further, when ms change with a new release from sql server the cols from internal sql the code don't work. i don't understand the not existing contracting – oas_public Jul 30 '15 at 14:50
  • Yes you have to have all the columns it returns in the temp table. Then you can select just what you need later from the temp table. – HLGEM Jul 30 '15 at 14:53
  • You can use OpenQuery to avoid knowing the schema. [Insert results of a Stored Procedure into a Temporary Table](http://stackoverflow.com/a/653726/3191303) – AWinkle Jul 30 '15 at 15:01
  • saw already the openquery solution. imho this is a hack and no good and simple solution. you have to go outside from sql server to get the values. and exact this is what i don't understand. sql server offer a procedure which is (out of the box) not! usable with the same language which the proc is written. i don't understand the nature, why this is done. when there is a func for this i understand it. a view is also perfect (like oracle do). but why a proc with no out? – oas_public Jul 31 '15 at 06:30
  • @oas_public, Oracle and SQL Server do things differently. Some things are better the way Oracle does them and some are better the way SQL Server does them and some are just different with neither better than the other. You need to just accept that. We told you how to get the information you want, now you just have to implement your solution but really stop whining that you don't like how SQLServer does it. – HLGEM Jul 31 '15 at 12:31
  • @hlgem: you missunderstand me! this is absolutel not what i want. what i want: understand the system, on which i work and do the best possible job. might i use the wring words with my bad english. but i don't understand, why a very useful stored proc (all in sys db) deliver the result in the output instead of a reuseable structure where tsql can easy and well definded work with. that's the nature from my question. i know many diffs from oracle and sql server, but i agree --> this is not what we have to discuss here! – oas_public Aug 03 '15 at 06:03