Tagged Questions

Constructing a query at runtime with string concatenation operations and executing the query from that string.

learn more… | top users | synonyms

-2
votes
1answer
27 views

How to pass Parameters to stored procedure with input parameters from checkboxes [closed]

I have a query where i need to generate results based on checkbox selection. How do i need to pass my input parameters to the stored procedure in the where condition based on selection. Note: i may ...
1
vote
1answer
25 views

use variable to determine the name of a column in the select in postgre

I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it. The old table is like this: -- old table (OldTable) name| col_a | ...
0
votes
0answers
27 views

Accessing values of dymanic cloumn

I have a table which has 16 fields whose of alternate name to city. Below is my table structure: CREATE TABLE CITY_DATA_TO_CHK ( COUNTRY_CODE VARCHAR2 (100), REGION ...
2
votes
1answer
84 views

Inserting variables into another variable [closed]

I've been working on the code below. I'm trying to insert results of a declared variable into a sql statement. I've highlighted the section below that I'm talking about. What am I doing wrong here? ...
1
vote
1answer
31 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
3
votes
2answers
65 views

Removing all columns with given name

I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion. When inserting records ...
1
vote
1answer
50 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
0
votes
2answers
49 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
0
votes
2answers
75 views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
1answer
67 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
3
votes
1answer
113 views

Difficulty getting sp_executesql to work

Alex Aza's comment solution here generates all names of tables with records referencing a specified input record. I would like to adapt Alex's solution into a table-valued function. I'm having trouble ...
1
vote
2answers
150 views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
1
vote
0answers
57 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 dynamic as possible without having to add the server as a linked server ...
0
votes
0answers
63 views

dynamic sql: merge partitioned tables (annual tables into a single table of all years)

I need to merge (UNION) data from annual tables into one large table. I am confused about how dynamic SQL (or else) should be used for this. Below is an updated version of my question on ...
1
vote
2answers
212 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
2answers
313 views

Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts: DECLARE @database_id int, @database_name nvarchar(100); DECLARE ...
2
votes
1answer
82 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
0
votes
1answer
804 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
2answers
934 views

Postgresql: Query across schemas

I have multiple schemas that I need to query across. The schemas are not know in advance for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
1
vote
2answers
54 views

Trunc sytax problem

If any one could help me with the syntax issues which I am facing here. sqlshipped := ' and a.ORDS_COMPLETED_DATE between ' ||shippedfrom ||' and trunc(' ...
0
votes
0answers
32 views

dynamic query manupulation

I am trying to append a dynamic query given below to a non-dynamic query. Given below is the dynamic query. sqlFilter1 := ' and (g.STSL_USRS_ID = ' || User_ID || 'OR (dept.DEPT_NAME IN (select ...
0
votes
0answers
132 views

Partly rotated table with pivot/dynamic SQL without aggregation

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
1
vote
2answers
3k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
1
vote
0answers
256 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
2
votes
1answer
53 views

Update various tables depending on survey answer

I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the ...
2
votes
1answer
211 views

Create A View Using Dynamic Sql

I'm trying to create a dynamic Database creation script. There are a lot of steps and we create this database often so the script looks something like this. DECLARE @databaseName nvarchar(100) = ...
1
vote
1answer
187 views

How to improve performance of SP with many optional filtering parameters?

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem ...
2
votes
0answers
94 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql ...
2
votes
1answer
2k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
2
votes
1answer
87 views

getting the output of delete statement in UDF in postgres

In Postgres in a user defined function I do execute format('delete from table where session_id = %L', id); ran without execute postgres (may) return(s) the following: DELETE 0 Is there any way I ...
1
vote
1answer
3k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
3
votes
2answers
8k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
2
votes
2answers
159 views

how to make a particular field into seperate columns?

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. I have this table in the below format: No | User | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank ...
3
votes
2answers
906 views

sp_executesql adds statements to executed dynamic script?

The Question: As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does ...
2
votes
2answers
2k views

Using dynamic sql inside Oracle stored procedure

Assuming I have the following procedure CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type, table1_val IN TABLE1.table1_value%type) AS SQL_UPDATE VARCHAR2(500) := ...
1
vote
2answers
631 views

Dropping a group of schemas with similar name patterns

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object. In my case, I need to drop some schemas, all of the ...
1
vote
2answers
2k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
1
vote
1answer
89 views

double date conversion when adding partition

I want to create a procedure that adds new partition in a table partitioned by time. For this purpose I use following commands: s_until := to_char(sysdate+1,' SYYYY-MM-DD HH24:MI:SS'); ...
3
votes
2answers
378 views

Find all the SP's where a particular column is not updated

I want to find stored procedures where a particular column is not updated. As an example SP1: BEGIN UPDATE YourTable SET Foo = @Foo, Bar = @Bar WHERE Id = @Id ...
6
votes
2answers
875 views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
13
votes
1answer
9k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
2
votes
0answers
184 views

Schema for multiple identical queue tables

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical ...
7
votes
2answers
312 views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
-1
votes
1answer
216 views

Selecting just one table from Oracle view [closed]

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins. ...
6
votes
2answers
1k views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
2
votes
2answers
347 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
3
votes
1answer
2k views

sp_execute expects parameter '@handle' of type 'int'

I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
2
votes
1answer
577 views

Inserting into three different tables, using foreign keys — 500000x each

We're working on refactoring many of our offline (python) scripts. I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field. We have a ...
10
votes
1answer
10k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...