Dynamic query based on datetime parameter
how to pass date parameter in dynamic sql query oracle
how to pass date parameter in sql query
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).
- 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
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.
- 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
- 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.