stored procedure and multiple data sets

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

stored procedure and multiple data sets

Author

Topic

santhi
Starting Member

2 Posts

Posted - 2009-03-23 : 23:58:18

Hi,

I have a report which contains several queries - one of which is a stored procedure which creates a Temp table in the database. This temp table is then used by the other queries.

The issue is that when I run the report, the stored procedure does not create the temp table before the other queries start using it. In fact, I need to run the report, and then refresh it in order to get the report to pull in the correct data (1st run populates the temp table, refresh then allows the queries to use it.).

Is there a way to force the execution of the stored procedure before the other queries run?...I don't want to create stored procedures for each query, because the intitial creation of the Temp table is quite slow (5seconds), and to do this for each data subset would be very resource intensive.

Thanks

santhi

santhi
Starting Member

2 Posts

Posted - 2009-03-24 : 01:48:40

the below is tipical structure of my stored procedure

USE [CMS_PRD]
GO
/****** Object: StoredProcedure [dbo].[testBIDS] Script Date: 03/24/2009 16:47:04 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[testBIDS] @project_id varchar(10)='' As

SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT OFF
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

/* clean up */
delete ssrs_test1

/* build up query */
DECLARE @SQL1 varchar(800)
DECLARE @SQL_PID varchar(100)

IF (@project_id <> '<All>' )
BEGIN
SELECT @SQL_PID= ' p.project_id = '+@project_id

END
ELSE
BEGIN
SELECT @SQL_PID= ' 1=1 '
END

SELECT @SQL1 = 'insert ssrs_test1 select project_id, project_name from project p
where '+ @SQL_PID +' '

BEGIN
PRINT 'SQL is '+ @SQL1
EXEC (@SQL1)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources