Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
303
questions
0
votes
2answers
95 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 ...
-2
votes
0answers
27 views
Generating Output variables dynamically [on hold]
I am trying to do following ang getting the error. Actually I am getting all the column names and also creating select statements dynamically during run time. I am required not to hardcode any values/ ...
0
votes
2answers
36 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
22 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
96 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
534 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
63 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
363 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 ...
2
votes
1answer
139 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
56 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
27 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
164 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
55 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
101 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
50 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
141 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
289 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
117 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
769 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
35 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
110 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
89 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
62 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
31 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
460 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
79 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
61 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
464 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
286 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
2k 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
335 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
704 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
105 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
271 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
310 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
145 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
106 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
173 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
740 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
348 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
140 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
66 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
187 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 ...
