Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
324
questions
0
votes
1answer
23 views
SQL stored procedure to produce custom XML text
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.
With my current solution I have a stored procedure for each 30 tables where I do: ...
1
vote
1answer
33 views
Stored procedure to set value to a column must accept returned values from functions
I created a very simple table in Data Mart to store ETL diagnostic information:
CREATE TABLE [internal].[etl_status]
(
[property_name] VARCHAR(100) NOT NULL PRIMARY KEY,
[property_value] ...
0
votes
2answers
39 views
Join to tables dynamically depending on data
have a logging table that contains references to other objects in other tables. I want to create a view to add some additional data to the logging table by looking back at the other objects.
For ...
0
votes
0answers
8 views
postgres 9.6 - use data from xml to call procedure
I work with a system based on Postgres 9.6 (updated from 9.1 or something similar). One of latest changes requires an ability to remotely call procedure on database with some arguments. Due to various ...
0
votes
1answer
52 views
INSERT INTO… SELECT over a LinkedServer in smaller batches
I've written the following code to move data from one database table, to another (over a LinkedServer);
SET NOCOUNT ON
DECLARE @ApplicationURL varchar(50),
@LinkedServer nvarchar(50),
@...
0
votes
1answer
62 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
3
votes
1answer
119 views
If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?
If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
1
vote
0answers
28 views
Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?
I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
0
votes
1answer
55 views
Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?
If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
2
votes
1answer
68 views
Variable to select database in SQL query
If I want use a variable in the FROM clause of a SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.
There are few different ways of running a query from a ...
0
votes
0answers
13 views
Need to break up a large file into smaller files to send via sp_sendsqlmail
I have created a job to send a "report" to someone (actually two reports). My issue is that the query is returning some 150K+ rows of data (no lobs or xml or anything, so not large rows), but ...
2
votes
2answers
101 views
Postgresql return results of dynamic SQL
I'm using Postgresql, version 12.
My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
1
vote
1answer
63 views
Update dynamic column names using keys from jsonb argument
I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
0
votes
3answers
87 views
Create stored procedure with dynamic number of columns + column names
I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having ...
1
vote
1answer
29 views
SQL Server Dynamic Group By
I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA).
The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone).
This means it can create ...
0
votes
2answers
73 views
Help error message inside dynamic T-SQL
I have a requirement to come up with reporting for indexes for our whole production environment:
I found the below script online and modified it according to my requirement and trying to execute it ...
1
vote
3answers
178 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
78 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 ...
2
votes
1answer
200 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
0answers
36 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 ...
-2
votes
2answers
102 views
Insert Query's of a table including data without the use of pg_dump (--column-inserts parameter)
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
2answers
63 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
2answers
1k 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 ...
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
293 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
126 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
2answers
85 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
29 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
103 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
547 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)
...
0
votes
1answer
1k 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 ...
5
votes
1answer
419 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
1answer
177 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 ...
1
vote
1answer
160 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
21 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
31 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 ...
0
votes
1answer
591 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 ...
1
vote
3answers
65 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 ...
3
votes
1answer
273 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
60 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
543 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 ...
0
votes
1answer
995 views
Preparing query in postgreSQL with variable table name?
I'm trying to do an INSERT INTO a table whose name is in the variable myTable.
In MySQL, I'd do it like this:
SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...');
...
1
vote
1answer
469 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 ...
0
votes
1answer
1k views
CONVERT to datetime fails on Dynamic SQL - “Conversion failed when converting date and/or time from character string” [closed]
I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a ...
1
vote
1answer
42 views
Sort output of plpgsql function
I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples ...
0
votes
1answer
24 views
how to clean some character from row in mysql?
How to remove some part of rows from column in mysql? I have a column like this
my desire output is like this
1
vote
1answer
342 views
alternatives to passing temp table as a parameter to a stored procedure
some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure:
USE MASTER
GO
ALTER PROCEDURE dbo.sp_getDBSpace
@Radhe sysname=null,
...
0
votes
2answers
177 views
Compile Dynamic Sql before Running
I have a table, and want to test if dynamic sql will compile.
Example:
create Table dbo.Product
(
ProductId int,
ProductName varchar(255)
)
This will succeed: Column Name exists
declare ...
1
vote
2answers
70 views
How can I add “in” keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012
I want the parameters in Age and Gender to accept more than one value
Create Procedure [Admission].[SelectPlayerData]
@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish ...
1
vote
2answers
356 views
Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?
Scenario
I have a database that contains all of our customer data, which is separated by
Schemas. For example:
Customer1 = Schema1,
Customer2 = Schema2,
Customer3 = Schema3,
etc...
Sometimes we ...
