Returning Data without Accesing a Table

Question

The Situation (Ignore this it is boring): I have reports that I created using reporting services. Some of these reports take the parameter, "Month". They enter in an integer for the month they want. Example: December = 12. In order to view the report, I am simply using the Report Viewer in visual studio. I need the month field to be a drop down box in order to select the month by name. There is a feature in in reporting services that allows you to bind the field to a stored procedure in order to create value/text pairs for the drop down list.

The Problem: I don't want to create a "months" table in my database but I need to have a stored procedure that can return all the month/int pairs. I'm sure there is a very easy solution to this but I'm not sure what it is! My first thought was creating a temp table, but I am not sure how to add manually add each month/int pair to the table... All your suggestions are appreciated!

What I want is the following statement, except without the use of the Months Table:

SELECT MonthID, MonthName
FROM Months

Answer 1

Sounds like what you need is a view, basically you write a custom query and it returns it as a table.

Also, a SP can just contain a Query and it will return a table, just create it as:

CREATE PROCEDURE [dbo].[mytable] 
    -- Add the parameters for the stored procedure here
    @inputarg1 = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT 
     .....
    FROM
     .....
    WHERE
     .....
END

Answer 2

in the stored proc, do the following

Declare @Months Table 
   (monthNo TinyInt Primary Key Not Null,
    name varChar(10) Not Null,
    Abbrev char(3) = Substring(name,1,3))
Insert @Months ( monthNo, name) Values (1, 'January')
Insert @Months ( monthNo, name) Values (2, 'February')
...
Insert @Months ( monthNo, name) Values (12, 'December')

or, if you need this table in more than one stored proc, write a UDF that dies the same thing and returns this table to the calling proc...

Answer 3

Do you really need to create a table for this? You could just do the selects manually:

SELECT 1, 'January' UNION ALL
SELECT 2, 'February' UNION ALL
...
SELECT 12, 'December'

Answer 4

How about:

CREATE PROCEDURE ListMonths
AS

    SELECT 1 AS MonthId, 'January' AS MonthName
     union all select 2, 'February'
     union all select 3, 'March'
     union all select 4, 'April'
     union all select 5, 'May'
     union all select 6, 'June'
     union all select 7, 'July'
     union all select 8, 'August'
     union all select 9, 'September'
     union all select 10, 'October'
     union all select 11, 'November'
     union all select 12, 'December'

GO

Call this and I believe it returns what you want.

Answer 5

You might like this:

http://www.sqlteam.com/forums/topic.asp?TOPIC%5FID=99696

I wrote this to be able to set default parameters for various dates.

Answer 6

WITH    months AS
        (
        SELECT  CAST('2009.01.01' AS DATETIME) AS m, 1 AS num
        UNION ALL
        SELECT  DATEADD(month, 1, m), num + 1
        FROM    months
        WHERE   num < 12
        )
SELECT  num, DATENAME(month, m)
FROM    months