Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
0
votes
2answers
59 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
53 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
41 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 ...
-1
votes
1answer
29 views
SQL Server _ Cursor in Dynamic SQL and UPDATE CASE WHEN THEN
I would like to extract the rows that meet the condition in the CASE WHEN condition and put them in the updated table. But the trick is to create a new row every time the condition is met in one of ...
0
votes
1answer
36 views
SQL Server - Cursor and Dynamic SQL
this query is working but not giving me the output I want.
I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But ...
-1
votes
0answers
51 views
Optimize multiple inserts
good day!
Let me explain about my issue. One of my users has a sotred procedure to insert all the transactional information into a table for historic records. Nothing out of the ordinary, except for ...
2
votes
2answers
247 views
Create stored procedure that contains newlines with a oneliner sql statement
I want to put code in a single line that will create a stored procedure that contains newlines.
Is that possible?
Do I need to use sp_executesql?
How do I escape newlines in a sql statement?
How do I ...
1
vote
1answer
31 views
PostgreSQL - Return type and EXECUTE QUERY
My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type:
create type core.search_result as (
user_id uuid,
country core.country,
ethnicity ...
0
votes
1answer
44 views
postgres dyanmic and unlimited where conditions
How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves ...
2
votes
2answers
294 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/...
1
vote
2answers
87 views
Syntax error in SQL Server while making Dynamic SQL
Scenario:
I have a table with 100M+ rows of data so I have created an index with something as:
CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
ON dbo.base_voter (name_voter,...
-1
votes
0answers
49 views
Error in writing Dynamic stored procedure
I'm trying to write a Dynamic query to pass file name and table name to the procedure dynamically. what is wrong with this query?
ALTER Procedure [dbo].[OpenExcel]
@FileName nvarchar(2000),
@...
2
votes
1answer
162 views
Dynamically define a RETURN table (column type, name) for subsequent loop
I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.
https://postgis.net/docs/ST_Intersection.html
https://postgis.net/docs/ST_Intersects.html
...
0
votes
1answer
28 views
How I develop Union Report Views for Multiple Single Tenant Databases?
We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...
-2
votes
1answer
70 views
What is the Optimized Way to Dynamic SQL Select all Columns from Table?
I just generated this script.
It will select all columns from a table dynamically.
Is there an alternative or more clean, efficient way of conducting this?
Additionally, I want to place line breaks ...
1
vote
2answers
63 views
How to get max field value length for each TEXT field in all tables in database
I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like:
|...
0
votes
2answers
45 views
how to run once a bunch of generated scripts with exception handling?
Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.
So I created a script that calls a procedure that will save the contents of this table ...
1
vote
1answer
43 views
Create DDL Trigger Dynamically
I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works ...
1
vote
0answers
111 views
How to run a dynamic select query with column names from another query?
I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem.
I have one table that contains the list of columns in a ...
0
votes
0answers
98 views
SQL Buffer Pool and Plan cache
Does SQL Buffer Pool size = Max server memory.?
I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours.
I have 128 GB Physical Memory.
Earlier i ...
2
votes
0answers
81 views
Plan Guide for the LINQ-SQL Query
I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
2
votes
2answers
79 views
Can you use dynamic sql in a select statement?
I have the following code:
select
[schema].[name] [schema],
[table].[name] [table],
[column].[name] [column],
[type].[name] [type],
[column].max_length [...
0
votes
0answers
80 views
SSIS: Using Event Viewer to Trace Errors of A Package
Overview
A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an ...
0
votes
2answers
140 views
Dynamically choosing column for WHERE clause
I've got a table that holds columns named "Day1" through to "Day31".
Is there a way I can dynamically choose the correct column to put in a WHERE clause?
I can get the current day, that's no ...
3
votes
1answer
223 views
Execution time high for Query in sp_executesql
I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.
I run the application and capture the query using profiler....
0
votes
1answer
94 views
How to get newly inserted row ids when inserting with execute?
I have a function which returns newly inserted row count:
BEGIN
EXECUTE (
SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
_tbl, ...
-1
votes
2answers
55 views
Dynamic SQL isn't updating table
So I need to basically get the count of every table in a database daily and insert those counts into a separate database for analysis and reporting purposes. The table the counts need to be inserted ...
0
votes
2answers
2k views
How to create a table dynamically based on json data?
I have a json that I get as a file, I have loaded that in the Postgres database.
Here is the json data:
{
"tablename": "test",
"columns": [
{
"name": "field1",
"datatype": "BigInt"...
4
votes
3answers
178 views
SELECT rows based on indefinite number of filters
How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)?
Example
Table:
create table ...
0
votes
0answers
24 views
linked server issue while synchronising logins between servers using T-SQL
I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier.
It requires a linked Server.
this is a partial view of the ...
1
vote
1answer
314 views
Transpose Rows to column
Can anyone Please help me to transpose below result
+------+------------+-------+
| SVR | dates | count |
+------+------------+-------+
| SRV1 | 03/25/2018 | 1 |
| SRV1 | 04/06/2018 | 59 ...
2
votes
1answer
416 views
Stored procedure with case statement for 'AND' based on parameter value
I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I ...
1
vote
2answers
384 views
script all triggers in a database
I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.
Every time I need to reinitialise those database I need to script ...
0
votes
1answer
287 views
SQL SERVER dynamic Query with Like Statement for null values
I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "...
2
votes
2answers
233 views
Safely quoting type names to protect against SQL-injection
How would I quote the type name to protect against SQL Injection. For example, take this
SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
^ ...
4
votes
2answers
55 views
Add column for subsequent values for a record
I have a table structured as follows:
CREATE TABLE t (
HHID varchar2(1),
INDIV_ID int,
PRIM varchar2(1)
);
insert into t values ('A', 10, 'Y');
insert into t values ('A',...
0
votes
1answer
75 views
sp_executesql changes non standard characters to?
Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran.
sql server 2008R2 & 2012
...
0
votes
1answer
199 views
Set columns dinamically in UTL_FILE
I've been stuck with this problem, so I'm looking for help now.
I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to ...
0
votes
1answer
268 views
SQL Server 2017 Management Data Warehouse: collection_set_1 not working due to database trigger
The problem
I have got an issue setting up Management Data Warehouse on SQL Server 2017 CU 5. The job "collection_set_1_noncached_collect_and_upload" keeps failing. It is related to the "Disk Usage" ...
3
votes
1answer
85 views
Any way to make stored procedure code dynamically reference other databases, without dynamic sql?
I've a situation where I have two databases:
ABCData
ABCUsers
One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:
INSERT ...
3
votes
2answers
72 views
how to find the database context of the last sql statement?
I would like to find out from the code below,
what was the name of the database where the last command was run.
In this case I am looking for my_other_database.
his there any way of finding this?
Is ...
4
votes
2answers
558 views
How to quote qualified table name with format() function?
I have some problems in properly quoting a table name using the format function in the example below.
CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
RETURNS bool AS $...
1
vote
1answer
29 views
Build dynamic text search on table columns
With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique).
The Number column should be grouped and start a new section of ...
2
votes
1answer
78 views
Generating SQL with SQL [closed]
I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient ...
2
votes
2answers
456 views
Dynamic SQL Query with Paging, Performance and Optimization
I have an interesting problem in front of me. There is a database with ~1 million user accounts with an anticipated growth of 1-2 million per year. The database is strongly TPT but this particular ...
1
vote
1answer
346 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 ...
1
vote
0answers
258 views
MySQL dynamic number of columns
I'm looking for a way to dynamically build up the number of columns in a query.
Imagine a school assessment database.
I desire a way to get, for each question the percentage of students who got a ...
1
vote
2answers
475 views
SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure
I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe.
I have a second stored procedure that generates a where logic.
For example if I run the ...
1
vote
1answer
146 views
Loop INSERT EXEC AT through linked servers
Relating to Inserting EXEC AT linked server into temp table
I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my ...
1
vote
1answer
3k views
Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391
I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is
insert into ##eapb
EXEC (@remotecmd) AT [...
