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.
| Author |
Topic |
|
goellerk
Starting Member
1 Post |
 Posted - 2013-05-30 : 10:57:21
|
| I have a report that needs to accept an input parameter from the user at runtime to determine which table the report will query. To simplify for the user, instead of requiring the full table name, I would like to have them input just the year (all table names for this would be SA_XXXX where XXXX is a 4-digit year).I've tried a number of approaches with no success. When I DECLARE a local variable for @SaYear and concat within that to create the table name, I get the error "Must declare the table variable @SaYear" when I run the query directly in SSMS. If I run it in BIDS, I get "The DECLARE SQL construct or statement is not supported."My code is below in its latest iteration - I would greatly appreciate any insights or direction. I can't find anything anywhere on how DECLARE statements are handled in order of operation or query optimization calcs. DECLARE @SaYear AS varchar(7)SET @SaYear = 'SA_' + @Year SELECT [SA_STUDENT_ID], [LAST_NAME], [FIRST_NAME], STTR.STTR_STATUS, (SELECT SUM(STC_CRED) FROM coll18_production.dbo.STUDENT_ACAD_CRED JOIN [coll18_production].[dbo].STC_STATUSES ON STUDENT_ACAD_CRED.STUDENT_ACAD_CRED_ID = STC_STATUSES.STUDENT_ACAD_CRED_ID WHERE SA.SA_STUDENT_ID = STUDENT_ACAD_CRED.STC_PERSON_ID AND STUDENT_ACAD_CRED.STC_TERM = @Term AND STC_STATUSES.POS = '1' AND (STC_STATUSES.STC_STATUS = 'A' OR STC_STATUSES.STC_STATUS = 'N')) AS CREDITS FROM [@SaYear] AS SA JOIN [coll18_production].[dbo].[STUDENT_ACAD_CRED] AS STC ON STC.STC_PERSON_ID = SA.SA_STUDENT_ID JOIN [coll18_production].[dbo].[STC_STATUSES] AS STCS ON STC.STUDENT_ACAD_CRED_ID = STCS.STUDENT_ACAD_CRED_ID JOIN [coll18_production].[dbo].[PERSON] AS PER ON PER.ID = SA.SA_STUDENT_ID JOIN coll18_production.dbo.STTR_STATUSES AS STTR ON STTR.STUDENT_TERMS_ID = SA.SA_STUDENT_ID + '*' + STC.STC_TERM + '*' + STC.STC_ACAD_LEVEL WHERE STCS.STC_STATUS_DATE BETWEEN @StartDate AND @EndDate AND STTR.STTR_STATUS = 'W' AND STTR.POS = '1' AND STC.STC_TERM = @Term AND SA.SA_AWARDED >= 0GROUP BY [SA_STUDENT_ID], [LAST_NAME], [FIRST_NAME], STTR.STTR_STATUSORDER BY CREDITS, [SA_STUDENT_ID], [LAST_NAME], [FIRST_NAME] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-31 : 02:29:07
|
| where are you trying to run this query? Instead of doing DECLARE why not pass the value as a parameter? Or wrap the entire code in a procedure and then use it in reports------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Using input parameter in FROM statement