Dynamic query based on datetime parameter

how to pass date parameter in dynamic sql query
how to pass date parameter in dynamic sql query oracle
how to pass date parameter in sql query
dynamic queries
how to pass parameter in dynamic sql query
how to execute stored procedure with datetime parameter
mysql stored procedure datetime parameter
how to pass date in sql query

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.

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 

[SOLVED] Trying to pass dates as parameters to dynamic SQL string , So when you concatenate a datetime. that accepts two date parameters for a report that will be built in Reporting Services. numeric(9,2), OfficeName varchar​(100)) DECLARE @Query nvarchar(1000) DECLARE @Query2  Dynamic data source based on date Mark as New I believe it is possible by passing parameter to MQuery. You can use query below to find the Excel file with the

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

SQL Query: Pass DATETIME parameter to Dynamic SQL in Stored , SQL Query: Pass DATETIME parameter to Dynamic SQL in Stored Procedure [Solved] convert the parameter to nvarchar rather than to datetime. use extra single inverted comma before and after conversion of the datetime parameter to nvarchar. A couple of years ago, the Power Query team added Parameters as a proper object, but I kept on Creating Dynamic Parameters in Excel Power Query the same way as I always had. The reason for this is two-fold: the first is because I was used to it, the second was because the built-in Parameters are quite static.

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

How to pass datetime parameter to SP dynamically , Hi,In below query I want to pass the parameter whose datatype is Datatime,​dynamically,. I am getting following error,. Conversion failed when  In the dataset properties set the query type to text. Copy and paste the dynamic SQL query into the dataset query textbox as shown below: Set the timeout value to a reasonable number, for example 600 seconds. Go to the Fields tab of the dataset properties for manually adding the Fields Name and Fields Source.

Using DateTime Variable In Where Clause, Your issue relates to the way you are building your dynamic SQL. If you look at the output from your PRINT statement, there are no quotes  In Power Query use “From Table”, select the “Parameter” table and right click one of the values and click on “Drill Down”. In my example it is the value for “SickLeaveHours”. Opening the script code to Access the value of the cell in our parameter table it looks like: I rename the two lines to identify it later and copy the lines.

How to include a datetime parameter within a stored procedure , DECLARE @stringInt nvarchar(3) , @intint int SELECT @stringInt = N'3' , @intint Depending on what you are truly trying to do, this may be beneficial. and the parameter list EXECUTE sp_executesql @query , N'@start datetime, I had a requirement in which the query has to be executed dynamically. To make this change, we’ll add a parameter to our stored procedure, as well as the dynamic query. We’ll use the sp_executesql command to call the dynamic query using these parameters. The updated stored procedure with changes is shown below. The stored procedure parameter is green, and the dynamic query parameter red.

[Solved] how to pass a datetime parameter while executing stored , I could not pass datetime parameter to a stored procedure, where datatime parameter was being used in dynamic sql query, the lesson I have  With Power Query and Power BI Desktop, you can add Query Parameters to a report and make elements of the report dependent on those parameters. For example, you could use Query Parameters to automatically have a report create a filter, load a data model or a data source reference, generate a measure definition, and many other abilities.

Comments
  • Do you need also to check the Max Range (6 months)?
  • No. my input to store proc will take care. I will pass max 6 month range to SP.
  • 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.
  • with slide modification it's working. Great work @Anson
  • 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
  • i guess you just should give more conditions for db name. you said its like feb2018 or sep2018. try search for month as well
  • 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.
  • i thought you need to query one specific db.
  • Thanks for your effort @sami. What should be pass to Target param ? can you please show it with example.
  • @JSushil Check the demo.