Create dataset with exist dynamic table name and datetime parameter SQL Server-CodePudding

Table involved

I would like to create dataset with datetime parameter, when user choose date, it automatically select data from the date table as in the picture (dynamic table name).

SP created is as shown

Already use method store procedure, but then I had trouble when joining the SP with another table. Can suggest me best way to solve this issue?

CodePudding user response：

You can use table functions. you able join T.F with another table.

CREATE FUNCTION [dbo].[fn_test] 
(
--INPUT PARAMETERS
) 
RETURNS @output TABLE(ID NVARCHAR(MAX)) 
BEGIN 
        INSERT INTO @output (ID)  
        EXEC('SELECT 1 AS A')
    RETURN 
END

CodePudding user response：

Storing each day in a separate table is probably not the best way to go but more on that later, to solve the immediate problem....

You can dump the results of your SP into a temp table and join to that.

I have assumed here that your SensorDataRainfall table ( the one without a date) is the same structure at the ones with dates.

Your dataset query would look like this.

SELECT TOP 0 * INTO #SDR FROM dbo.SensorDataRainfall -- create an empty table to put the results in.

INSERT INTO #SDR EXEC spGetDataRainfall @year, @month -- dump data from the SP

SELECT * 
    FROM #SDR a 
    JOIN myOtherTable b on a.column = b.column

However There are a few issues with your general approach.

Having data which is essentially the same data split by dates is not very scalable, even if the amount of data is very small, it's just not a good approach. You should probably investigate the process that creates there tables in the first place and insert the data into a table which has the same structure but also include columns for the year and month. This way all the data will be in a single table and can easily queried for a single month of across multiple months, years.