We’re rewarding the question askers & reputations are being recalculated! Read more.

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
3
votes
1answer
780 views

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date): | COLUMN_NAME | TYPE | |---------------|-------------|...
2
votes
1answer
679 views

How properly call stored procedure from EXEC(SQL)?

I have engine is SQL which uses EXEC(SQLString) to run stored procedures. I do that because I need dynamically determine which stored procedure to call and to prepare parameters for that procedure. ...
0
votes
1answer
86 views

making a TSQL statement that has variables to be dynamic

I am having a real hard time trying to convert a TSQL statement (which be a store procedure) into a Dynamic SQL. Basically I must be able to pass the schema name as a parameter. Here is my code: ...
0
votes
1answer
378 views

sp_executesql processes uniqueidentifier-param with EntityCommandExecutionException but still acts like expected

I use a Azure DB(MS-Sql) and administrate the DB with Management Studio for SQL Server 2016. My Application is a simple C#/WPF-Application. So here we go, this is my SProc: ALTER PROCEDURE [dbo]....
5
votes
1answer
9k views

The metadata could not be determined because statement invokes an extended stored procedure

in sql server 2012 I used to have a look at the jobs by getting the output of SP_HELP_JOB -- https://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx -- getting data from sp_help_job into a temp ...
1
vote
0answers
35 views

Dynamically determine the starting time and ending time for a. . . Breakfast of Champions!

Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table records when ordered appropriately for TOP and BOTTOM records ...
0
votes
1answer
2k views

drop table in a begin try begin catch block - is there any loss of performance?

The code below is a dynamic sql that will give you a list of all alerts in your system. It works. There is a temp table in the code for reasons beyond this question. This question is related to the ...
0
votes
1answer
231 views

retrun multiple functions from dynamic query

I need to return multiple fields from dynamic query.I am referring this link below code what I am doing so far CREATE OR REPLACE FUNCTION get_names(ind_zone text,dst text,nmind text,ind text,implst ...
0
votes
1answer
407 views

Writing SQL injection proof dynamic DDL in Oracle

I have a need to write dynamic DDL statements such as CREATE USER ?. I know that I can use EXECUTE IMMEDIATE to do this, but so far I have found no way to incorporate dynamic parameters without simply ...
3
votes
2answers
7k views

How can I update multiple tables at once in Postgres?

I have several tables with the exact same structure and I need to update a value in all tables. In order to do so I tried to build the following script: DO $do$ DECLARE i pg_tables%rowtype; BEGIN ...
4
votes
2answers
165 views

Why colname as input param of function = not good idea?

Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and ...
6
votes
1answer
3k views

Performance issue with sp_executesql and VARCHAR parameter

Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)). Execution time of the first query is 34 seconds SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT FROM Customers c LEFT JOIN ...
-4
votes
1answer
4k views

Why always throwing error “Must declare scalar variable” while executing stored procedure? [closed]

CREATE Procedure Test12345 @Page INT = 0, @RecsPerPage BIGINT = 5, @IsExport BIT = 0, ...
2
votes
2answers
235 views

nested cursors problem

I'm trying to restore all the inserts that I've done in my tables. I have a cursor that loops through table names, a second one that loops through all the rows from that table and a third one that ...
2
votes
1answer
583 views

How to use an integer pivot in SQL Server?

I want to get multiples rows using a pivot: FIXED | 0 1 2 3 4 ---- ---- ---- ---- ---- ---- ---- First | D1 C1 SP A0 1B I did it: DECLARE @zero int = 0 DECLARE @one int =...
0
votes
1answer
389 views

Insert Into Statement With OpenQuery [closed]

I have constructed the following syntax but I keep getting an error of: Msg 156, Level 15, State 1, Line 26 Incorrect syntax near the keyword 'INSERT'. What do I need to change in my syntax so ...
1
vote
0answers
56 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
2
votes
1answer
1k views

Capturing sp_executesql statement in sql profiler

After catching some data in a SQL Profiler session, I can almost always see a second entry of type RPC:Completed for each query using a sp_executesql statement. I know that this is done to create re-...
-1
votes
1answer
118 views

Why does dynamic SQL behave differently in SELECT FROM than in SELECT in Greenplum? [closed]

We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and ...
2
votes
2answers
1k views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
1
vote
1answer
317 views

For each field name in a list of fields, get the unique values

The Question: I have a view that is a list of fields in tables: FIELD_LIST_VW +-------------+------------+ | TABLE_NAME | FIELD_NAME | +-------------+------------+ | ENG.TABLE_1 | FIELD_1 | | ...
1
vote
0answers
46 views

Dynamic content update problem

I have three tables: forms which contains basic information about a form form_parts which contains information about a certain input field and has a FK to forms (each form parts must have one ...
3
votes
2answers
2k views

A Dynamic where clause in MS SQL Server

The Problem My SQL Server database contains a view that looks like the following: +-------------+-----------+------+-------+------+------+ | CAR_ID | MAKE |COLOR | Miles | Seats| MODEL| +--...
4
votes
1answer
408 views

Modify function to use it in multiple triggers

I have tables that have almost the same columns. The first table has the current values and the other table has the old values and an additional column to store the date data is inserted. The second ...
4
votes
2answers
370 views

Does Greenplum support dynamic sql?

PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked ...
1
vote
2answers
871 views

Insert values from a record variable into a subclass table

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
4
votes
1answer
569 views

SSRS - ERROR NUMBER:7357 while passing multiple value parameters using comma delimited string to stored procedure

I am working on a SSRS report to display the logins permissions in a set of databases on a specific server. the server, the logins and the databases are all parameters. NULL shows them all (logins ...
2
votes
1answer
645 views

using sp_executesql to run bulk insert

I am trying to execute bulk insert statement using sp_executesql. I received syntax error: Msg 102, level 15, state 1Incorrect syntax near '\t'. my code block are based on this link Please ...
2
votes
1answer
104 views

Selecting multiple values from a column and put them into separate columns

I have a table like this: MainUser | PartyUser -------------------- George | Tom Michael | Tim Michael | James Michael | Monica Jim | Nick Jim | Connie And I need to get something ...
2
votes
1answer
75 views

Why would a Greenplum procedure not recognize a view change?

I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
1
vote
1answer
1k views

Insert openquery results into an existing table

I have a stored procedure that queries a linked DB2 server in this fashion: SET @sql='SELECT * FROM openquery(DB2,''SELECT column1, column2 FROM table'')' exec sp_executesql @sql I am trying to ...
0
votes
2answers
4k views

Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ DECLARE srowdata record; ...
0
votes
1answer
1k views

How to write stored procedure use SP_HELPTEXT for getting information of objects belong to another database

I am using SQL Server 2012. I have a db lets called it A in that database I have procedure backup which get all creation information of another database which is B. Now the problem when I am trying ...
1
vote
1answer
1k views

Bind variable vs. string-concatenated variable

I've made an anonymous block that has dynamic SQL: 01 DECLARE 02 l_table_name VARCHAR2(100); 03 l_field_name VARCHAR2(100); 04 l_domain_name VARCHAR2(100); 05 06 BEGIN ...
-1
votes
1answer
26 views

Running query of stored procedure belong to another database [closed]

I have a stored procedure in db A I Need to connect and use objects belong to database B is that possible ?
5
votes
1answer
2k views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
5
votes
4answers
5k views

Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice? It is common to use parameters ...
0
votes
1answer
5k views

Beginner PL/SQL: Return row value from dynamic SQL function

I have a PL/SQL function that successfully compiles: 01 WITH FUNCTION find_CV_errors( 02 l_table_name IN VARCHAR 03 ,l_field_name IN VARCHAR 04 ,l_domain_name IN VARCHAR 05 ...
6
votes
1answer
19k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
1
vote
1answer
83 views

EXECUTE stmt USING expression

I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, ...
0
votes
1answer
626 views

Native dynamic SQL - execute immediate with column and table names

I have to make dynamic query, which binds column names and table name dynamically using native dynamic sql in oracle pl/sql. Here is my first try: declare komenda varchar2(255); v1 varchar2(10):...
0
votes
2answers
1k views

Access Variable Outside Dynamic SQL

What am I missing here? If I run a print @sql statement and run that statement directly in SSMS I get the value that I am after. However, if I run the dynamic sql and try to access Print @value1 ...
0
votes
1answer
2k views

SQL Server Msg 2812, level 16 state 62 line 30

I want to make automatic backup and restore my database on SQL Server 2012. I have made automatic backup with script in my Windows server. Now, I want to make auto restore with script. I have found ...
0
votes
1answer
2k views

integer variable for query string - option(optimize for variables)

I have problem related to my post variable for query string (or dynamic query?). I encountered a hurdle related to using an integer parameter. The query looks like this: DECLARE @area NVARCHAR(255) ...
0
votes
2answers
3k views

Apostrophe in EXECUTE postgres

I need to print an empty string using EXECUTE command in Postgres. So, how to circle with quotes the string str in order for Postgres to realize my intention? CREATE OR REPLACE FUNCTION foo() RETURNS ...
0
votes
1answer
37 views

Dynamic Select with error in where conditon

@P_SqLCommand NVARCHAR(100) OUTPUT, --output which will be return @P_SqlList NVARCHAR(30),-- columns that needs to be filtered out @P_Table NVARCHAR(30),--Table Name needs to be use in the ...
0
votes
0answers
372 views

Execute SQL at different server (with dynamic server name)

I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamically as possible without having to add the server as a linked server ...
3
votes
1answer
3k views

CREATE VIEW as join between tables with identically named columns

As the title says, I'd like to create a view from a join between tables which have columns with identical names (surprisingly "id"). Postgres doesn't prepend the table name to the result, but for ...
0
votes
2answers
141 views

Execution Plan StatementText contains incorrect syntax

I have run an execution plan with management studio 2014 on mssql 2008 R2. To be able to better read the Selectstatement i right-clicked the plan to view it in xml. I noticed that some statements ...
7
votes
1answer
10k views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...