Dynamic SQL is a technique using SQL (Structured Query Language) whose main difference from traditional SQL is that Dynamic SQL allows to build SQL statements dynamically at runtime, which eases the automatic generation and execution of program statements.
Questions tagged [dynamic-sql]
2681 questions
0
votes
1 answer
I need help regarding dynamic SQL and dynamically setting the table name in a function
So, i need to create a function that returns the count of the lines in a table, the table name must be specified by the user. This is the code i have:
CREATE OR REPLACE FUNCTION datiInTab( table regclass )
RETURNS NUMERIC(5) AS
$$
DECLARE
num…
MetalMessiah
- 3
- 2
0
votes
1 answer
How to write PL/SQL function which returns the result of a select statement having as pararmeters the function's parameters?
I tried to write a PL/SQL function having as parameters a tablename and a column name, which returns the result of the query as a table.
Here's what I tried:
CREATE TYPE TABLE_RES_OBJ AS OBJECT (
employee_id number(30) ,
person_id…
Moldovan Adrienn
- 3
- 1
- 1
0
votes
1 answer
How to use dynamic column names in an UPDATE statement in a function?
I want to update all the text columns by fetching the column names on runtime within the loop.
drop function if exists aa.clean_data();
create or replace function aa.clean_data()
returns void
language plpgsql
as
$$
declare
col_name…
Kiran_t
- 45
- 6
0
votes
1 answer
Executing procedure with varchar parameter, passed into OPENROWSET, error
I'm working on procedure, which uses OPENROWSET.
I need to pass 1 varchar parameter into it. That's how I've decided to do that:
ALTER PROCEDURE [dbo].[GetId]
@designatio varchar
AS
BEGIN
DECLARE @SQLStr varchar(max)=
'select * FROM…
Харламов Даниил
- 163
- 1
- 10
0
votes
1 answer
ora-00900 invalid sql statement execute immediate
I am trying to solve a task with a dynamic SQL, but facing an issue ora-00900 invalid sql statement.
execute immediate 'alter table my_table set interval (NUMTOYMINTERVAL(1, ''MONTH''))';
However, it works in the anonymous block treating the…
Petukhou Mikita
- 49
- 6
0
votes
0 answers
Is it a right approach to take advantage of roles?
After learning about dynamic queries, I'm trying to build a couple of base functions C(reate)R(ead)U(pdate)d(elete)
for my db saving me to repeat a lot of code for table/view.
I want to take advantage of roles and apply security at database layer so…
basilean
- 23
- 5
0
votes
3 answers
Concatenate a value to a table in static SQL
I have a requirement in which i need to concatenate a value to table name.
The requirement is as follows:
DECLARE @TableName varchar(50);
SET @TableName = (SELECT 'TableName' + CONVERT(varchar(50), 2019));
PRINT @TableName;
SELECT * FROM…
user3767459
- 3
- 2
0
votes
0 answers
Dynamic SQL using Cursor
I have a task to find sales forecast for upcoming dates in current month. If the current month is May we have to calculate sales using the formula May 11 = (May10+(Map10*(1-(Apr10/Apr11 + Mar10/Mar11 + Feb10/Feb11)/3))) and so on till end of current…
0
votes
2 answers
Dynamic SQL can make it?
I have one table named AskLists with 2 fields :
AskListId AskInterview
2032 5
2032 3
2032 4
2032 6
5076 1
5076 3
5076 4
5076 5
5076 6
For each distinct number of the field AskListId, there is a table named by the number…
clement
- 9
- 1
0
votes
1 answer
Update null value to a column in dynamic SQL
I need to update a specific set of columns with null value, but when I'm trying to pass null value to dynamic SQL I'm not getting any error or output.
DECLARE @Value VARCHAR(100)
SELECT @Value = null
DECLARE @TableName…
Siva Dss
- 9
- 1
0
votes
3 answers
SQL Server Dynamic SQL - Get output from list of tables
I am trying to loop through a temp table variable that contains a list of table names. I want to simply count the rows in each table where a DateTracked column is greater than 30 days. I am having trouble dynamically changing the FROM @tblName…
Data Dill
- 353
- 4
- 14
0
votes
1 answer
Are there any ways of creating stored procedure for unknown number of columns?
I have cars table and it has 18 columns.
CREATE TABLE CARS
(
ID INTEGER PRIMARY KEY,
Manufacturer VARCHAR2(1000),
Model VARCHAR2(1000),
Year INTEGER NOT NULL,
Category VARCHAR2(1000) NOT NULL,
Mileage NUMBER,
FuelType…
0
votes
1 answer
How I can improve my procedure to work successfully in a backend?
I am trying to make a dynamic sql procedure for the backend in my courser project. I want to make sort by a specific column.
CREATE OR REPLACE PROCEDURE sort_by_column(p_column_name VARCHAR2, p_parameter VARCHAR2) IS
v_stmt…
0
votes
1 answer
Needs fixing a procedure for inserting values to the table dynamically
I am trying to use dynamic SQL to insert values into my table. But I am struggling with it! This is my table
CREATE TABLE CARS
(
ID INTEGER PRIMARY KEY,
Manufacturer VARCHAR2(1000),
Model VARCHAR2(1000),
Year INTEGER NOT NULL,
…
0
votes
1 answer
Loop with DB names inserting
I have a number of databases with tables Table1 with the same structures. All table names are listed in the main1 database in the table testbases. It is necessary in a loop to go through all the rows from the Table1 and insert them into…
Mauzzz0
- 53
- 1
- 6