Stack Overflow
Stack Overflow

Questions tagged [dynamic-sql]

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.

2681 questions
0
votes
2 answers

Stored Procedure to Update Table with a Variable Column Name

I am trying the write a stored procedure in plpgsql to simply update an integer value in a table. I have a table (called conditions) with four columns of type integer called structure, missing, repair, and cosmetic that accept 0, 1, 0r 2 (see table…
gicarto
  • 3
  • 5
0
votes
0 answers

Relation "" already exist dynamic query

I'm trying to execute a query within a loop which is within another loop, which is within an anonymous code block. I'm trying to run this query query := 'CREATE VIEW hopsPartialDistance AS ' ||'SELECT AvgDistance ' ||'FROM…
coderoftheday
  • 1,987
  • 4
  • 7
  • 21
0
votes
0 answers

How to search for all table name in database ending with Some strings and check some values on the returned table?

I have a database which has many tables What i want to do is retrive all the tables ending with a string "Validate" I am able to return those tables but, how can i query on those returned tables to check for a specific value in a column. Can anyone…
Akang Toshi
  • 193
  • 5
  • 17
0
votes
3 answers

Oracle Query Logic Declare and With

I want to printout a Dynamic built query. Yet I am stuck at variable declaration; Error At line 2. I need the maximum size for these VARCHAR2 variables. Do I have a good overall structure ? I use the result of the WITH inside the dynamic…
Benoît
  • 143
  • 1
  • 2
  • 15
0
votes
0 answers

ExecuteNonQuery is not working when using dynamic SQL to update

I've tried to use the ExecuteNonQuery() to update a table using the SqlCommand. However, nothing happened. I'd like to know what happened. I've run the same command in SSMS and everything worked successfully. Don't know why it doesn't work on my C#…
Casey
  • 25
  • 5
0
votes
1 answer

Is there a way to leave out the zeros in this pivot table?

I dynamically create a pivot table based on a variable I create to account for the number of weeks. I then used a derived table to only include weeks that actually have data in them (those weeks become my columns in the pivot table) I Need to…
Aaron Jackson
  • 1
  • 1
0
votes
1 answer

How to pass schema name dynamically in a function?

I have function called list_customers, taking i_entity_id, i_finyear as input params. The schema name is built from i_finyear, I need to execute the query based on the given schema. I tried the below code: CREATE OR REPLACE FUNCTION…
ABHISHEK
  • 5
  • 3
0
votes
1 answer

Passing Server Name into T-SQL Stored Procedure

I have a stored procedure that is being called by an app from different environments (DEV/QA). From the stored procedure below, I wanted to pass the server name as a parameter, replacing "HARDCODEDSERVER" with the one passed as parameter…
Patrick
  • 318
  • 3
  • 13
0
votes
2 answers

Stored procedure dynamic where with date

I must have a query with a dynamic where but I have a problem with the dates. The error message is Msg 295, Level 16, State 3, Line 45 Failed to convert a string to data type smalldatetime Here is my procedure CREATE OR ALTER PROCEDURE…
halim
  • 1
0
votes
1 answer

Postgresql dynamic query with named parameters inside

Is there a possibility to write dynamic SQL, where I can use parameter names inside the code? The following syntax I know, makes long code completely unclear and hard to figure out the sense without digging and mapping the parameters. format ($$…
sh4rkyy
  • 343
  • 2
  • 19
0
votes
1 answer

Dynamic SQL with loop through table rows with cursor in SQL Server

I have two tables (#aaa_Test & #bbb_Test). I want to loop through each row of the #aaa_Test table. I need to iterate every row of #aaa_Test using a cursor. Also, I have a dynamic SQL statement I've created in a stored procedure. I want to use…
ghj
  • 29
  • 5
0
votes
0 answers

Variable number of bind variables in EXECUTE IMMEDIATE statement

in Oracle, is it possible to have a dynamic number of bind variables in a EXECUTE IMMEDIATE dynamic SQL? For example, in a given situation i might want to do something like: execute immediate stmt using lv_name; But, assuming that stmt is some…
user1508072
  • 217
  • 1
  • 3
0
votes
1 answer

How to nest variable in query string passed to function

I need to be able to get the value stored inside rec_key.empname when I call this function: CREATE OR REPLACE FUNCTION public.txt(text) RETURNS SETOF record LANGUAGE plpgsql AS $function$ declare var_param text; var_req TEXT; rec_key…
user2210516
  • 613
  • 3
  • 15
  • 32
0
votes
1 answer

for loop with dynamic table name and execute immediate

in my Procedure there is the following code line for i in (select schema_name, table_name, restricted_columns from GRANTED_TABLES_FOR_ROLE where restricted_columns = 0) loop execute immediate 'grant select on ' ||…
Moribundus
  • 39
  • 9
0
votes
1 answer

Doesn't find variable when passing query as parameter

I have a function with a static output that works. (Postgres v.10) This returns the Amount of users per Server. Here is the code: CREATE OR REPLACE FUNCTION public.test() RETURNS SETOF record LANGUAGE plpgsql AS $function$ DECLARE var_req…
user2210516
  • 613
  • 3
  • 15
  • 32
1 2 3
…
99 100