Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
406
questions
0
votes
1
answer
16
views
Run SSIS Package but the package path will be decided runtime
I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps.
My idea ...
2
votes
1
answer
27
views
PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?
I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the ...
- 131
0
votes
1
answer
28
views
MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)
Problem:
you want / need to use dynamic SQL to create procedure / function /
view / trigger in other databases than the current
you can't specify the database name in the statement, CREATE VIEW ...
- 845
0
votes
0
answers
23
views
Renaming the return fields of a SQL query with string variables?
Suppose I have a table as follows:
symbol date price volume
GE 2017.01.03 31.82 2,300
GE 2017.01.03 31.69 3,500
GE 2017.01.04 31.92 3,700
GE 2017.01.04 31.8 2,100
GE 2017.01.04 ...
- 109
0
votes
0
answers
23
views
Convenient way to select 100+ columns (but not ALL the table columns) in SQL query at a time?
I have a table with more than 100 columns and I want to query all but the first and second columns. How do I do it quickly without enumerating 100 columns?
Just for illustrative purposes, suppose I ...
- 101
5
votes
2
answers
455
views
sp_executesql Not Working with Parameters
I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any ...
- 178
0
votes
1
answer
54
views
Alter a function in Postgres 12 to allow for dates to be default or populated [duplicate]
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 389
0
votes
2
answers
151
views
Default values for function parameters
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 389
0
votes
0
answers
269
views
Generate dynamic number of columns from CTE
A hospital database (SQL Server 2016) has a table of visits to the hospital. If patients transfer to a different specialty, a new visit is made (but from a research perspective it's considered one ...
- 101
1
vote
1
answer
67
views
Filter a SELECT query with predicates passed as single hstore value
For an API I am building I am supposed to write a function with a single hstore argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an ...
- 111
0
votes
1
answer
389
views
exec limited to 4000 characters?
trying to execute this script gives only the first select as a result
declare @sp nvarchar(max)
select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2')
exec (@sp)
...
1
vote
2
answers
300
views
stored procedure runs fine in all sessions but one
There are procedures that run fine manually but not in a job, or fails when run from an application, or not work in SSIS SQL task
Mine works in all sessions but one.
this is the code I am running - it ...
- 15.3k
1
vote
1
answer
900
views
Use variable as column name
I'm trying to create trigger in PostgreSQL 14:
create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$
declare
equipment_id int := new....
- 11
1
vote
0
answers
462
views
How to compare a subset of two tables using dynamically generated column names in PostgreSQL?
I have two tables, one a test table and one a production table, both with +200 columns and a couple thousand lines of code to create the table. I periodically make changes and am trying to automate QA....
0
votes
1
answer
975
views
Dynamic CREATE TABLE AS with indexes
I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process).
One of the things I need to move are a bunch of stored procedures, that generate tables ...
- 35
0
votes
1
answer
231
views
Allow create table (dynamic name) and insert but nothing else
I'm trying to find a way to allow an application to create tables and insert data into them on a SQL Server 2019 while protecting from injection attacks in case the app credentials would leak. My ...
0
votes
1
answer
23
views
Using tablenames from a config table for joins
SELECT
$model_master_id$,
$model_market_segment$,
$country$,
$ctry_cd$,
$score_run_id$,
$period_id$,
$prediction_from_dt$,
$prediction_to_dt$,
$buy_acq$,
$...
- 31
0
votes
0
answers
223
views
Create a stored procedure that dynamically union tables having extension .csv
I have a database named Fire that has 41 tables with the name format dbo.[#######.csv].
All the tables have the different number of column name like table 1 column A,B and table 2 column A,B,C and ...
0
votes
0
answers
1k
views
Select a dynamic list of columns in Postgres
Just wanted to come back to this question as proposed solution (using a temp table)
Are there other ways to select a dynamic list of columns?
didn't exactly work for me.
My scenario (using example ...
- 1
0
votes
1
answer
153
views
Immutable dynamic sql functions
Scenario #1
Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it ...
- 1,109
8
votes
2
answers
2k
views
How does use of sp_executesql with parameters protect against SQL injection?
The following is a dynamic filtering solution that uses sp_executesql
IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders;
GO
CREATE PROC dbo.GetOrders
@orderid AS INT = NULL,
@...
- 309
4
votes
1
answer
199
views
Evaluate comparison operator from table column
Is it possible to expand a field value into a comparison operator? Something like this:
create table math (
value1 int,
value2 int,
operator text
);
insert into math values(1,2,'>=');
...
- 445
0
votes
1
answer
588
views
How return dynamic SQL value in procedure
I have a procedure that takes in parameters, and makes a dynamic statement based on those. The statement always returns a numeric value. How do I return that value, or pass it to the variable?
For ...
- 1,765
1
vote
2
answers
1k
views
MySQL: Is There a way to dynamically generate multiple date range based on its date
The data I got is
id
date
cnt
1
'2021-11-26'
1
1
'2021-11-25'
1
1
'2021-11-24'
1
1
'2021-11-23'
1
The result I want is
id
from_date
to_date
cnt
1
'2021-11-23'
'2021-11-24'
2
1
'2021-11-23'
'...
- 121
1
vote
1
answer
494
views
How to parametrize DDL in a plpgsql procedure?
How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
- 124
-1
votes
1
answer
205
views
Change dynamic SQL to Static SQL with order by [closed]
We have a SP that has a Dynamic SQL and one of the parameters it gets is order by.
when we use the dynamic we can do something like this
if @order > 0
BEGIN
SET @ORDERBYSTR = 'ORDER BY ' + cast(@...
- 119
0
votes
0
answers
124
views
Using a variable that may be null in a where statement
I select a value into a mysql variable. Eg...
select @userId := userId
from myTable
where id = ?;
The value of @userId can be null.
Now I want to update the rows in ...
- 139
2
votes
1
answer
613
views
My function returned a string. How to execute it?
My function fun() returned a long crosstab table generation string (varchar) that I can view with select fun(). How to execute it not inside a function but from a console?
- 75
0
votes
1
answer
857
views
oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL
I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the ...
1
vote
1
answer
1k
views
In SQL Server, is there a way to pivot on an unknown number columns WITHOUT using dynamic SQL?
Essentially if we have this:
create table #temp ([key] nchar, [value] nvarchar(2));
insert #temp ([key], [value]) values
('a', '1'),
('b', '2'),
...
...
- 157
0
votes
1
answer
1k
views
oracle pl/sql block throwing ORA-00922: missing or invalid option
I have the following segment of oracle pl/sql block
DECLARE
rec all_tab_columns%ROWTYPE;
v_tableName VARCHAR2(100);
v_columnName VARCHAR2(100);
v_schemaName VARCHAR2(100);
BEGIN
...
- 13
1
vote
0
answers
54
views
What is the best way to have actual execution plan and IO stats as values to insert?
We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
- 45
0
votes
1
answer
3k
views
How to insert a row from JSON in Postgres
I have 2 tables recording company market data.
A table recording stock market symbols
CREATE TABLE appl.symbols (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
symbol VARCHAR(10) NOT NULL,
...
- 27
2
votes
2
answers
601
views
Viewing the statement in EXECUTE with USING
Let's say with the below function, I need to debug what is executed inside the EXECUTE statement. (a long, dynamic statement with dynamic variables provided in USING in reality)
CREATE OR REPLACE ...
- 133
1
vote
1
answer
536
views
MariaDB: Create dynamic PrepareStatement inside a Procedure
I am trying to make a stored procedure which returns me the records of a table that match filters that can be applied in layers.
The procedure receives certain variables as parameters and I want to ...
- 113
2
votes
1
answer
331
views
SQL Dynamic Query based on user input in PowerBI
I am pulling data from inSQL using the SQL Query below:
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [X],[Y], [Z], [A]
FROM WideHistory
WHERE wwRetrievalMode = '...
- 21
0
votes
2
answers
515
views
Turn SQL tables into Views - with Dynamic SQL
As the name would suggest, I need to turn a TON of sql tables into views, using dynamic SQL.
Motive-wise, it's just a silly workaround to preserve a Legacy program.
So far I'm creating a temptable, ...
0
votes
1
answer
798
views
Sql: Count Null and Non-Null values for set of tables and fields dynamically
I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields.
I have idea to use it something like this ...
- 11
1
vote
1
answer
1k
views
Using parameters in stored procedure with plpgsql
I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs:
Error is "SQL state: 42601", syntax error at "$1".
Why ...
- 11
0
votes
0
answers
80
views
Dynamic SQL and AO AG - SQL Server
I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary ...
- 349
0
votes
1
answer
3k
views
How can I dynamically create views in Postgres 10 using variables?
I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and ...
- 249
0
votes
0
answers
24
views
Call two function within main function PostgreSQL
i have two functions one with the english language and the other with the french language. I would like to create a third function which will be the main function that will return the first function ...
0
votes
1
answer
183
views
Get another function to return timestamp in PostgreSQL [duplicate]
Can I have another function to return the last timestamp.
I want to compare with this query to use the best query.
My query:
CREATE OR REPLACE FUNCTION f_last_update1(nomTable text , OUT updated ...
0
votes
1
answer
456
views
Get latest timestamp from column in given table dynamically
I want to get last timestmap from a table with the column updated in PostgreSQL.
And I want a function to do that. So I can pass the table name as parameter and the function returns the latest ...
1
vote
1
answer
749
views
Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:
There will be hundreds (maybe even thousands) of ...
- 13
0
votes
1
answer
327
views
Problem calling Postgres function
I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, ...
1
vote
1
answer
46
views
Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?
Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
- 195
0
votes
1
answer
2k
views
Include single quote string in redshift dynamic SQL
I have function named
Schema.func_date(date,varchar)
I wanna do like this(I am using below statement inside Store proc)
Execute 'Select *, ID as Main_Id, ' ||
'schema.func_date(quote_literal(...
- 103
0
votes
0
answers
271
views
How to run query to convert undefined JSON fields into a row dynamically
Related to this answer
I found a solution above to get undefined JSON files into rows.
However, I need to run query twice so I modify the query to run it once.
I tried to run the query returned from ...
- 127
1
vote
2
answers
638
views
Stop SQL job if it runs past a certain time
I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database. ...
- 111