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
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
30 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
168 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
58 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
268 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 ...
0
votes
1answer
573 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
38 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
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 ...
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
2answers
2k views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (...
1
vote
1answer
177 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, ...
3
votes
2answers
124 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
1answer
739 views

MSSQL - GRANT EXECUTE permission inside stored procedure

Environment SQL Server 2012 (11.0.2100.60) Situation user is allowed to execute a number of stored procedure; user is readonly denywrite one of stored procedure has an EXECUTE statement; The ...
0
votes
2answers
144 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
1answer
916 views

Find unmatched rows between two tables dynamically

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $...
1
vote
2answers
172 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 ...
8
votes
2answers
11k views

Select permission inside stored procedure?

I have granted a user execute permission for a stored procedure that uses dynamic SQL. But when he tries to execute it, he gets the error: The SELECT permission was denied on the object '[table ...
-1
votes
1answer
33 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 ...
2
votes
2answers
863 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 ...
0
votes
1answer
42 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
vote
1answer
115 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 ...
9
votes
1answer
5k views

sp_prepexec (sp_execute) vs. sp_executeSQL

The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of ...
0
votes
1answer
73 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 ...
0
votes
2answers
4k 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"...
1
vote
2answers
902 views

Using Dynamic Queries Across Multiple Databases to Change Views

I am seeking to run a query to modify views across multiple databases (See the initial question here.) and was able to find how to get it. I've now run into the problem that if I run this from master ...
6
votes
2answers
7k views

How to drop all of my functions in PostgreSQL?

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. How can drop all of my functions in PostgreSQL in a single ...
2
votes
1answer
3k 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 ...
1
vote
2answers
412 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,...
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 ...
3
votes
1answer
147 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 ...
-2
votes
1answer
500 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 ...
17
votes
4answers
4k views

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values. CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int ...
1
vote
2answers
1k 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
51 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 ...
4
votes
2answers
431 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub stored procedure based on passed ...
1
vote
1answer
146 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
378 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
426 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 ...
3
votes
1answer
2k views

Alternative to Dynamic SQL inside trigger?

My database has the following tables: TAG ---------------------- | tag_id | tag_name | ---------------------- TAG_VALUE: Stores values associated to each tag ----------------...
2
votes
0answers
161 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
189 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
226 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
1k 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 ...
4
votes
2answers
1k 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 $...
3
votes
1answer
431 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
162 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
75 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 ...
4
votes
3answers
200 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 ...