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
2 answers
Formatting XML with `GO` into a set of sql commands to be executed
I have seen this question Replace value in XML using SQL and again I am thankful, however, I have a XML file with all the trigger creation scripts but I have not been able to execute it because of the GO.
Partial view of my XML file:
IF…
Marcello Miorelli
- 3,368
- 4
- 44
- 67
0
votes
1 answer
dynamic sql in oracle passing data para
I need to convert this in dynamic sql in oracle passing data para
Select * from Student
where effective_date = '30-APR-2022';
set serveroutput on;
declare
date_ varchar2(100) := '30-APR-2022';
sql_stmt varchar2 (10000);
begin
sql_stmt := 'select *…
Sami
- 1
0
votes
1 answer
Dynamic Pivot, convert NULLs to 0's
I have the following code:
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the column names
SELECT
@columns += QUOTENAME(s.fullColName) + ','
FROM (SELECT Case
WHEN…
Jeff Perry
- 3
- 2
0
votes
1 answer
PL/pgSQL dynamic query in stored function to return any table's column names
I am trying to write a stored function with a dynamic query that returns all column names from a table that can then be used to create a dynamic query for a joined view trigger function. But struggling to create a stored function with a dynamic…
akarich73
- 15
- 4
0
votes
1 answer
How can a stored procedure calling a TVF be slower than a procedure that inlines the TVF?
Question:
I have two views:
V_Room, 14969 rows, 9 tables joined together
V_parkings, 3265 rows, 9 tables joined together
Then I have a view
V_Rooms_UsageTypes with 18234 rows
which is the union between V_Room and V_parkings
Then I have a table…
Stefan Steiger
- 78,642
- 66
- 377
- 442
0
votes
1 answer
select from some table based on results from a different table
I have a small table tbl_a that is something like
id
fieldName
tableName
1
field1
tbl_1
2
field2
tbl_1
3
field3
tbl_2
and I want to be able to come up with a function or proc or something where I can specify the fieldId from tbl_a…
Rusty Shackleford
- 53
- 4
0
votes
1 answer
SQL Server Dynamic creation of global temp table and insert data issue
My requirement is to create a global temporary table and store data there which I would access later. I give a dynamic name to my global temporary table and getting error that
Invalid object name '##Tmp1_84'.
84 is @SPID
here is my script. please…
Ramesh Dutta
- 99
- 8
0
votes
1 answer
As a string column, I've constructed the query that I want to run. How can I execute it?
All of my tables in the DIM schema share a bit column called USER_CHECKED. With an agent job, I want to output (I don't particularly care where/what to) the name of each table in the schema and the number of times USER_CHECKED has the value 0.…
J. Mini
- 1,868
- 1
- 9
- 38
0
votes
1 answer
Create new column in view based on calculated result from previous columns
I am trying to create a view which will display an additional column based on the output of previous two columns like below
Current view:
test_id
reported_date
12345
2021-05-07
12345
2022-05-07
23456
2022-05-07
New view:
I want to…
Sam
- 1
- 1
0
votes
3 answers
How to Run Multiple Dynamic Queries in a PostgreSQL Function
I am having some issues figuring out how to run multiple dynamic queries in a single function.
CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
fyq text)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
BEGIN
…
gwydion93
- 1,681
- 3
- 28
- 59
0
votes
1 answer
Dynamic SQL in Synapse dedicated pool fails, but non-dynamic query works
The below dynamic SQL query, which I execute via EXECUTE sp_executesql @sql; through SSMS in an Azure Synapse dedicated SQL Pool, returns random syntax errors, such as
Parse error at line: 105, column: 44: Incorrect syntax near…
Jens
- 35
- 2
0
votes
0 answers
Oracle dynamic sql
Why this code doesn output result? Write PL/SQL procedure successfully completed. and that all
declare
count_train number(20);
v_query_str varchar2(255);
begin
v_query_str := 'select count(*) from TRAIN';
execute immediate …
kaserr
- 17
- 4
0
votes
1 answer
Can I use the same variable in different positions in dynamic SQL query in PostgreSQL
I am using PostgreSQL to create complexe dynamic queries. In my queries I can use one variable multiple times in multiple positions in the query, to simplify things, I want to know if I can do something like this :
SELECT * FROM employees Where name…
Rym
- 5
- 1
0
votes
1 answer
How do I execute clob in execute immediate
I have a table with one row and clob as column (whose size is 5239). This column contains a table script extracted with the help of dbms_metadata.get_ddl. Along with table ddl it also extracts the primary key constraint and unique index script.
When…
kiran
- 5
- 2
0
votes
1 answer
Passing Table Values in SAS Proc SQL
I have a table_A. Sample data for table_A with name and dates
name
date
Mark
29-APR-22
Dave
05-May-22
I would like to pass the name and date column of table_A to the following code as values. The whole code is running in SAS.
proc…
Rogue258
- 109
- 1
- 9