Dynamic query based on datetime parameter

Question

I have a SQL Server database with date representation name like below & each database contains table A (table A having column like id, datetime, value, value1 etc).

Jan2018
Feb2018
Mar2018 and so on..

My search condition is user selected date (eg. from 01-Jan-2018 to 01-Jun-2018) which I am passing to a stored procedure (max range 6 month). I want to generate dynamic query to get data from these database based on datetime passed.

How to achieve this functionality as I found difficult to implement.

No. my input to store proc will take care. I will pass max 6 month range to SP. — Sushil Jadhav
– Sushil Jadhav, Commented Dec 26, 2018 at 12:10
If I didn't get this wrong, you should point out, that you have databases named like date representations. Not a column in table a with dates. — sticky bit
– sticky bit, Commented Dec 26, 2018 at 12:14

Anson Aricatt · Accepted Answer · 2018-12-26 13:37:51Z

1

Can you try this query

CREATE PROCEDURE Myproc @FromDate DATE, 
                        @ToDate   DATE 
AS 
  BEGIN 
      DECLARE @SQL      NVARCHAR(max)='', 
              @unionall VARCHAR(10)='' 

      WITH cte 
           AS (SELECT @FromDate dt, 
                      1         mont 
               UNION ALL 
               SELECT Dateadd(month, 1, dt) dt, 
                      mont + 1              mont 
               FROM   cte 
               WHERE  mont < Datediff(month, @FromDate, @ToDate) 

              ) 
      SELECT @SQL += @unionall + '
                         select * from [' 
                     + LEFT (CONVERT(VARCHAR, Datename (month, dt )), 3) 
                     + CONVERT (VARCHAR, Year (dt)) 
                     + '].[dbo].[tablename]', 
             @unionall = ' union all ' 
      FROM   cte 

      PRINT @SQL 

      EXECUTE( @SQL) 
  END

answered Dec 26, 2018 at 13:37

Anson Aricatt

3932 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sushil Jadhav Over a year ago

with slide modification it's working. Great work @Anson

Rena · Accepted Answer · 2018-12-26 13:05:42Z

0

You should query sys.databases to find a database you need. Then, as you can only use static declarations of databases, you should create a textual select statement and execute it.

I tried it on my dbs and it worked. This is my code:

declare  @date varchar(20) = '2018'

declare @dbName varchar(20)
declare @sSql varchar(200)
declare @sConditions varchar(20) = ''

Set @dbName = (SELECT name FROM master.sys.databases
where name like '%' + @date + '%')

print @dbName

Select @sSql = 'Select * From ' + @dbName + '.dbo.MyDB '
--+ ' Where ' + @sConditions

Execute (@sSql)

if you need to query all that fit year. do like this:

declare  @date varchar(20) = 'a' 
SELECT name Into #dbnames
FROM master.sys.databases
where name like '%' + @date + '%'

this brings a table of all sutable dbs. then query each one of them using loop. like cursor

edited Dec 26, 2018 at 13:05

answered Dec 26, 2018 at 12:43

Rena

718 bronze badges

4 Comments

Sushil Jadhav Over a year ago

I am getting error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

. I run it on sql server 2008 on 'master' database. This is error at set @dbName = line

Rena Over a year ago

i guess you just should give more conditions for db name. you said its like feb2018 or sep2018. try search for month as well

Sushil Jadhav Over a year ago

SELECT @dbName= name FROM master.sys.databases where name like '%' + @date + '%' is working now. But its only retrieving 1 records. If I have multiple database with name 2018 its only retrieving single records.

Rena Over a year ago

i thought you need to query one specific db.

Ilyes · Accepted Answer · 2018-12-27 19:18:04Z

0

Are you looking for

CREATE PROCEDURE MyProc
  @FromDate DATE,
  @ToDate DATE,
  @Target SysName
AS
BEGIN
  DECLARE @SQL NVARCHAR(MAX)= N'SELECT * FROM [' +
    @Target +
    '] WHERE [Dates] >= @FromDate AND [Dates] <= @ToDate';

  EXECUTE sp_executesql @SQL,
                        N'@FromDate DATE, @ToDate DATE',
                        @FromDate,
                        @ToDate;

END

Demo

As I now understand what you are trying to do, you can

CREATE PROCEDURE ProcName
  @FromDate DATE,
  @ToDate DATE
AS
BEGIN
  --Declare a variable to hold the Dynamic SQL
  DECLARE @SQL NVARCHAR(MAX) = N'';
  --Generate the databases names
  WITH CTE AS
  (
    SELECT @FromDate D,
           1 N
    UNION ALL
    SELECT DATEADD(Month, N, @FromDate),
           N + 1
    FROM CTE
    WHERE N <= DATEDIFF(Month, @FromDate, @ToDate)
  )
  --Build the SELECT statement
  SELECT @SQL = @SQL+
                N'SELECT * FROM ['+
                CONVERT(VARCHAR(3), D, 100)+
                CAST(YEAR(D) AS VARCHAR(4))+
                '].dbo.TableName UNION ALL ' --Or UNION as you want
  FROM CTE;
  --Remove the last UNION ALL
  SET @SQL = LEFT(@SQL, LEN(@SQL) - 10); --If UNION then just -6
  --Execute the statement
  EXECUTE sp_executesql @SQL;
END

edited Dec 27, 2018 at 19:18

answered Dec 26, 2018 at 12:20

Ilyes

15k5 gold badges31 silver badges61 bronze badges

2 Comments

Sushil Jadhav Over a year ago

Thanks for your effort @sami. What should be pass to Target param ? can you please show it with example.

Ilyes Over a year ago

@JSushil Check the demo.

Collectives™ on Stack Overflow

Dynamic query based on datetime parameter

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related