fields are not appearing when used stored proc

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

Development Tools

Reporting Services Development

fields are not appearing when used stored proc

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-11 : 14:43:44

I am trying to use a stored procedure which has the dynamic string in it and when the string is done it will be executed at the end.

Now when i am calling that stored proc from my reporting services it does' get the fields which are in the stored procedure. is it because of it has a dynamic string in it.

here is my stored proc: Please any advice will be greatly appreciated Thank you.:


CREATE PROCEDURE USP_GetStatReportSU
		(@eStatus			nvarchar(20))

AS

DECLARE @ErrorCode				int
DECLARE @SQLStatement 			nvarchar(2000)
DECLARE @SQLJoin 				nvarchar(1000)

SET NOCOUNT ON


SELECT @SQLJoin = 
	CASE 	@eStatus
		WHEN 'All' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
					GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate, 
					TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'

		WHEN 'Open' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate, 
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'	
			
		WHEN 'Closed' THEN 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate, 
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'	 

		ELSE 'WHERE     (TAB_ccsNetActions.ModuleName = ''SU'')
			GROUP BY TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, TAB_ccsNetSU.PDReceivedDate, TAB_ccsNetSU.PDToReviewDate, 
			TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription'
END

SET @SQLStatement = 'SELECT TAB_ccsNetSU.SUNumber, TAB_ccsNetSU.SUTitle, CONVERT(varchar(10), TAB_ccsNetSU.PDReceivedDate, 101) AS PDReceivedDate, 
     CONVERT(varchar(10), TAB_ccsNetSU.PDToReviewDate, 101) AS PDToReviewDate, TAB_ccsNetSU.PDToContractorDate, TAB_ccsNetSU.PDSupervisor, 
                      TAB_ccsNetSU.SUID, TAB_ccsNetActions.ActionDescription, DATEDIFF(Day, TAB_ccsNetSU.PDReceivedDate, GETDATE()) AS Age, 
                      CONVERT(varchar(10), GETDATE(), 101) AS [When]
FROM         TAB_ccsNetSU INNER JOIN
TAB_ccsNetActions ON TAB_ccsNetSU.SUID = TAB_ccsNetActions.ModuleRecordID ' + @SQLJoin

--EXEC (@SQLStatement)
print (@SQLStatement)
SELECT @ErrorCode = @@Error

SET NOCOUNT OFF
RETURN @ErrorCode
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-11 : 14:46:21

The EXEC line is commented out, so it won't work as desired. Make sure to comment out the PRINT statement when you are debugging as there is no one to read the output of the PRINT command when the stored procedure is execute by a program.

Tara Kizer
aka tduggan

reddymade
Posting Yak Master

165 Posts

Posted - 2006-01-11 : 15:27:42

Amazing: Thank you Tara.
I did'nt notice that at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources