Stored Procs that EXEC others procs? 2

[MFritts253]

Because the weird situation this report required i had to build some helper tables to get things in the format in need from my T-SQL.
My stored procedure looks like this:

BEGIN
EXEC dbo.Keep_Agr_IDs @Number = @Week
END


BEGIN
EXEC Keep_Data
END

BEGIN
Select *
FROM Keep_Table_ALL
END

Each time it this proc is Run i truncate the tables some where in the proc. I know this is not a great practice but the data is imported from another system so there is no sense in keeping it around since each report is specific. I could probley create a an ID for each run and so along with the rest of the data, But i would at first like to know why my crystal report won't get any data when i try to use this procedure. If i run this proc on Query Analyzer i get a result set but when i run this proc in crystal i get nothing and i don't understand why?

Cheers,
Michael

 

[MisterMo]

I think this is because you returning 3 different results from your SP even though Query analyser is only returning the last statement and CR is getting confused

place a

"set nocount on" at the start of SP this might help

begin
set nocount on

BEGIN
    EXEC dbo.Keep_Agr_IDs @Number = @Week
END


BEGIN
    EXEC Keep_Data
END


Select * 
FROM Keep_Table_ALL

end

Mo

 

[vidru]

The SET NOCOUNT ON will likely help, if all that's happening in the other procecedures is filling of tables, and they don't return any records.

Depending on what's going on in the Keep_Agr_IDs and Keep_Data procdures, you might investigate using TABLE variables to keep all of the logic within a single procedure. It would also keep you from having to worry about truncating tables.

-dave

 

[MFritts253]

Thanks for all the helps guys,
I was making a very stupid mistake in one of my stored procs

Instead on using a between join for temporal data i
was just executing a stored proc from with a while
and crystal really didn't like it.

thanks again for the help.