February 10, 2012 at 9:33 am
I know this isn't correct - How do I only run one select statement based on what year the user selects?
USE [THISDB]
GO
/****** Object: UserDefinedFunction [dbo].[fn_MORC_THISFUNCTION] Script Date: 02/10/2012 10:42:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_MORC_THISFUNCTION]
(@FiscalYear char(8),
@ListWhatFormat int)
RETURNS TABLE
AS
RETURN (
SELECT * FROM tblTABLE20012002 WHERE (@FiscalYear = '20012002') AND (@ListWhatFormat <> 0)
UNION ALL
SELECT * FROM tblTABLE20022003 WHERE (@FiscalYear = '20022003') AND (@ListWhatFormat <> 0)
UNION ALL
SELECT * FROM tblTABLE20032004 WHERE (@FiscalYear = '20032004') AND (@ListWhatFormat <> 0)
UNION ALL
SELECT * FROM tblTABLE20042005 WHERE (@FiscalYear = '20042005') AND (@ListWhatFormat <> 0)
UNION ALL
SELECT * FROM tblTABLE WHERE (@FiscalYear <> '20012002')
AND (@FiscalYear <> '20022003')
AND (@FiscalYear <> '20032004')
AND (@FiscalYear <> '20042005')
AND (@ListWhatFormat <> 0)
)
February 10, 2012 at 9:59 am
Can you post the DDL for your tables...Also, is there any particular reason you have a seperate table for each year? Why not have all the data in one single table and choose what is selected from it simple by limiting the results by WHERE FiscalYear = @FiscalYear???
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2012 at 12:34 pm
Aside from the very painful structure and naming convention, I am not sure what the issue is. You will return the result of the union of all those select statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply