Calling Stored Procedures

[shaunk]

I have a quite complex block of code containing nested selects , case statements , aggregrate functions etc etc.
This particular block of code has a very high reusabilty factor and I am therefore planning to use it in a routine I can call from any stored procedure requiring it.

At first I planned to use a view...but views do not accpet use of the case statement.

I have since created a stored procedure and have successfully called it from another procedure. The problem is, I don't know how to access the result set of the called procedure , in the caling procedure.
I inserted the resultset of the called procedure into a temporary table, but the calling procedure is falling over when I reference this table.
Thanks

 

[vongrunt]

Maybe the structure of temp table and sproc result set don't match... if they do, INSERT INTO #temptable EXEC mystoredproc should work.

 

[edwinjs]

Create temp table in the main procedure with ## (Global) and try it out.

 

[dbomrrsm]

BTW Views can use the case statement - one of mine :

CREATE VIEW VW_PROD_BITS
AS
SELECT REF,
HS_PLUMB_BIT = CASE WHEN HS_PLUMB=0 THEN 0 ELSE 1
END,
HS_ELEC_BIT = CASE WHEN HS_ELEC=0 THEN 0 ELSE 1
END,
HS_HAP_BIT = CASE WHEN HS_HAP=0 THEN 0 ELSE 1
END,
HS_GAS_BIT = CASE WHEN HS_GAS=0 THEN 0 ELSE 1
END,
HS_PEST_BIT = CASE WHEN HS_PEST=0 THEN 0 ELSE 1
END
FROM  MYTABLE
WHERE MYTABLE.INCOME > 0

[blue]DBomrrsm[/blue]

 

[SQLSister]

Several options
You can do a simple select of the final result in your first SP and then insert it into a temp table at the start of your second sp.

You could create a user defined function. That can be called directly in a select statement.

Questions about posting. See faq183-874

 

[shaunk]

Thankyou all who posted a response.

I managed to get the 'Insert into tmp_table exec sproc option' and the use of a global table up and running.

I will run with the global table because with the 'insert into' option a temp table has to be created with all data types matching the resultset of the called proc. (unless there is an easy way to do this)

Thanks again