Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
1
vote
1answer
37 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
19 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
26 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 ...
1
vote
1answer
42 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
49 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
86 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
47 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
131 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
145 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
57 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
496 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
33 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
23 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
86 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
72 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
68 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
56 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
30 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
38 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 ...
2
votes
2answers
362 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
60 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
57 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
414 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
220 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,...
2
votes
1answer
1k 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
233 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
447 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
48 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
78 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
198 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 ...
1
vote
0answers
247 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
119 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
89 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
144 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
535 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
312 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
121 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
61 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
3k 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
180 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 ...
1
vote
1answer
544 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
734 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
1k 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
439 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
292 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
97 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
318 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
336 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" ...
