Questions tagged [dynamic-sql]

Constructing a query at runtime with string concatenation operations and executing the query from that string.

Filter by
Sorted by
Tagged with
1
vote
3answers
60 views

Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work

I'm trying to create a temp table using dynamic sql (MS SQL Server 13.0.5426). This is a simplified example: Using straight SQL works: DROP TABLE #tmp_ts; -- will error, but ignored on the first ...
1
vote
1answer
20 views

Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB

I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated. As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating ...
0
votes
0answers
21 views

Join a dynamic amount of tables with the same naming rule in MySQL [on hold]

I am creating an application where users go and register a server, and each server registered gets a table containing information about it, like their users. All tables from those servers are named ...
0
votes
1answer
402 views

How to Alter and add a suffix “DO_NOT_DROP” to several tables using PL/SQL LOOP [on hold]

Say I have table A(see below) which has details of all the tables that has to be renamed. Also, what if the table does not exist and how to handle exception and find out how many of the tables were ...
2
votes
1answer
23 views

Convert a row to a nested JSON document containing all relations

In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the ...
-2
votes
2answers
68 views

Generating insert statements for a table without creating any temporary table

I am able to generate insert statements for any table using this query: select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
1
vote
1answer
675 views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
2
votes
2answers
495 views

Pass data from a table row into a create login statement using dynamicSQL [closed]

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more. We've done NO work concerning cursors/...
0
votes
2answers
2k views

Fill in a table name using a variable from another query

I have a general query I need to run for many tables SELECT sum(honoraria_amount) FROM table_1 The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39,...
4
votes
2answers
1k views

Update query in a trigger

I have a configuration table: Table: refdatecol id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
2
votes
0answers
23 views

RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however ...
10
votes
4answers
10k views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
3
votes
1answer
5k views

Create A View Using Dynamic Sql

I'm trying to create a dynamic database creation script. There are a lot of steps and we create this database often so the script looks something like this. DECLARE @databaseName nvarchar(100) = '...
5
votes
1answer
4k views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS $...
0
votes
1answer
184 views

Get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
0
votes
2answers
146 views

Monthly Date and Hour wise report generation using PHP MYSQL

Attendance Portal in PHP I am facing problem at Final Attendance Report Generation I'm stuck at the final stage of the Project: I have a Table- attendance, If a student is Present I mark it as 1 if ...
5
votes
2answers
543 views

The database mail configuration saved into a temp table

I am currently dealing with a bunch of servers and they have different Database Mail Configurations. I can see the Database Mail configuration for each of those servers. I use the following script: ...
1
vote
1answer
97 views

How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint?

I've worked myself into a corner, and I'm not sure how to handle this. I have a large set of databases with generally the same structure. Each database has a list of ID columns that are currently ...
2
votes
1answer
348 views

Schema for multiple identical queue tables

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical ...
1
vote
2answers
59 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
0
votes
0answers
14 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
3answers
86 views

Must declare the scalar variable question

That is my first post here, need helps: in a stored procedure I had some following code CREATE PROCEDURE [dbo].[SP_getAvg] ( @projectId INT ,@carrierId INT ,@fetchType VARCHAR(20) ) ...
0
votes
2answers
100 views

Parameterized Dynamic TSQL - Dynamic Parameters

We have a situation where effectively the same report is requested by different customers but they: Don't want all the columns Want the columns in a different order than we naturally have them Want ...
0
votes
1answer
199 views

Comparing multiple tables on two databases using dynamic PL/SQL

The issue I'm having is I have a block of dynamic SQL that I can't get the results from. Here's the SQL: CREATE OR REPLACE PROCEDURE GGCHECKER IS cursor c1 is SELECT TABLE_NAME, ...
0
votes
2answers
42 views

Creating a SQL Agent Job using Dynamic SQL

I'm currently putting together Agent Jobs for Database backups, using the Ola Hallengren scripts. I've put together a script that creates an Agent Job to perform FULL backups and it's working ...
0
votes
1answer
24 views

Executing an oracle variable string as I do in SQL Server

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? ...
0
votes
3answers
98 views

Select with dynamic columns

I have an InvoiceHeader, InvoiceItem tables InvoiceHeader content: InvoiceId InvoiceNumber BusinessId BusinessName BranchId 1 1 10 BSN INC 1 ...
3
votes
2answers
539 views

Dynamic SQL query - how do I add an int to the code?

Consider: Declare @stringsvar varchar(1000) Declare @Emp_id int DECLARE @strvar SYSNAME = 'Employee_test' SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id) ...
1
vote
1answer
228 views

How to send parameters to BCP inside dynamic sql using sp_executesql

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a ...
5
votes
2answers
9k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
1
vote
1answer
314 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
0
votes
1answer
258 views

Stored Procedure error “Must declare the scalar variable” [closed]

I am trying to create a stored procedure with dynamic SQL inside of it. The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part. I ...
2
votes
1answer
155 views

Inserting to a table when everything is parameterized

I need to grab data stored on a remote AS/400 file system to which I connect using a linked server connection, so I can send OPENQUERY select statements and receive the data on SQL Server. I don't ...
3
votes
1answer
1k views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
1
vote
3answers
57 views

How to create comment on table from query result

I want to use a query result as comment on the table. I have tried in psql: \set list select count(*) from department.master_data COMMENT ON TABLE department.master_data IS :list; But I got this ...
5
votes
1answer
376 views

Is it good practice to create tables dynamically?

Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present. I already ...
1
vote
2answers
298 views

Low performance with dynamic queries and cursors

Doing some legacy work, I found the following procedure that used to take between 1.5 to 6 hours, totally not expected when dealing with less than 2 million records. After the change it takes less ...
2
votes
1answer
157 views

Facing error while trying to run dynamic pl/sql

Hope you're doing well I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL. What I've written so far is : declare sql_stmt varchar2(100); v_student_id number := 1; v_new_stname ...
3
votes
1answer
1k views

How to set column DEFAULT using value of a variable

In a script for setting up a database which uses random UUIDs for primary keys, I have: CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT); CREATE TABLE beta (aref UUID ...
10
votes
2answers
2k views

Can sp_executesql be configured / used by default?

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I ...
10
votes
2answers
7k views

How to Dynamically change the database using TSQL

I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL: EXEC sys.sp_executesql N'USE db1 ' ; It executes successfully however the database ...
2
votes
1answer
1k views

Comprehensive troubleshooting of DatabaseMail

I am having a problem sending email from one of my servers. I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail I have other production servers that use all the same databaseMail settings ...
2
votes
2answers
2k views

Execute Dynamic SQL Generated From Select

A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process. How does one execute ...
1
vote
1answer
88 views

passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

I am often in need to run the sp_executesql in the context of different databases I would like to add the same parameters as the sp_executesql into the sp_ineachdb But when I look at this: -- Syntax ...
0
votes
1answer
20 views

enable the data access for the current server - is this operation logged somewhere?

I have a similar question related to the same subject: security concerns over enabling data access for the current server BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
0
votes
1answer
29 views

How to select maximum date_created value from all tables matching certain pattern?

Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each ...
3
votes
1answer
132 views

INSERT to a table from a database to other (same SQL Server) using Dynamic SQL

I need to copy a record from a Database DBa, Table Tbl1 to Database DBx, Table Tbl1 and get the Scope Identity inside the same SQL Server 2005 server I have already checked this: How to Dynamically ...
-1
votes
1answer
56 views

Msg 207, Invalid column name [closed]

alter procedure SP_AUTO @table_name varchar(200), @careof_check varchar(200), @ACCTG_careof_check varchar(200) AS begin declare @code_assgn varchar(2000) set @code_assgn=' if COL_LENGTH('+@...
2
votes
3answers
179 views

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Weird question?...maybe, but I have a need. :) I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in ...
7
votes
2answers
13k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...