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

learn more… | top users | synonyms

2
votes
0answers
8 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 ...
24
votes
3answers
15k 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 ...
0
votes
2answers
311 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 ...
4
votes
2answers
3k views

Query across multiple schemas, unknown until execution time

I have multiple schemas that I need to query across. The schemas are not known in advance, for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
0
votes
1answer
187 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 ...
0
votes
0answers
36 views

Problems with GO keyword [duplicate]

I have a query: DECLARE @str NVARCHAR(max) SET @str = 'SET QUOTED_IDENTIFIER ON' + CHAR(13)+ CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'Select 1 from MyTable' PRINT @str EXEC (@str) It returns an ...
2
votes
0answers
28 views

OPENQUERY and server permissions

when I run my stored procedure it is a complete success, even if I run it through exec. Running these: sp_GetServerLogins GO exec ( 'sp_GetServerLogins ') GO I get this: Needless ...
3
votes
3answers
37 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
0
votes
2answers
1k views

Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

Q.--> An alternative of OpenRowSet in SQL Server Description: I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 ...
1
vote
2answers
142 views

Collation conflict when running dynamic sql FOR XML PATH

I am getting the following error message when running the query below: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation ...
0
votes
2answers
128 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; ...
1
vote
1answer
48 views

Using Identity Insert on a linked server

We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following ...
1
vote
0answers
35 views

Performance impact in orders of magnitude with Dynamic SQL [closed]

This started as a problem debugging a SSRS performance problem where I found reports were timing out when they previously weren't. Finally I traced down the SQL the report server was submitting, and ...
1
vote
2answers
45 views

Dynamic SELECT and place result in variable columns

Edit: I use Microsoft SQL Server Management Studio, I believe it's SQL 2008R2. Sorry, new to this. I've got a complex question, at least, I think so since I can't find the answer after excessive ...
1
vote
1answer
57 views

Another Dynamically alias column question

I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this: DECLARE @Table1 AS TABLE( Table1ID Int Primary Key ,ColumnName ...
3
votes
1answer
906 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 ...
1
vote
2answers
584 views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
0
votes
1answer
53 views

I am trying to loop through my counter to create a dynamic sql query which should finally look like [closed]

I am trying to loop through my counter to create a dynamic sql query which should finally look like I am trying to use this approach to get the final query but does not seems to work declare ...
2
votes
2answers
170 views

SQL Server: Recursive dynamic SQL puzzle

I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as ...
-2
votes
1answer
135 views

How to use variable value inside copy function [closed]

I want to send data to .csv file using COPY function in Postgres. But I am using anonymous block so my table name should be value of variable. COPY (SELECT cname.portal from user) To '/tmp/out.csv' ...
0
votes
1answer
43 views

dynamic postgres inside shell script not working

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 \$$ BEGIN select * from customer; ...
0
votes
0answers
104 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
1
vote
3answers
163 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
1
vote
2answers
98 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 ...
0
votes
2answers
70 views

Efficiencies and pitfalls of a dynamically updated “Uber” View to streamline future code

We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns. For various procedures and views, we'll draw on ...
1
vote
3answers
91 views

Add fields to view from select (transpose fields)

I've got a table with following structure: CREATE TABLE test ( id integer, field character varying, value character varying ); field value ------ ----- field1 value1 field2 ...
2
votes
1answer
165 views

Stored procedure with dynamic sql. Is it isolation safe by default?

I would appreciate confirmation on the answer I found digging through T-SQL's documentation. I have a stored procedure that references a view twice. Once in the procedure and then a second time when ...
1
vote
1answer
122 views

UPSERT statement never completes, infinite loop?

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would ...
3
votes
2answers
14k 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 ...
0
votes
0answers
191 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
1
vote
1answer
94 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
2
votes
2answers
2k views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
1
vote
1answer
113 views

Refactoring sql for tables with similar columns but different table names

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except ...
2
votes
2answers
97 views

PostgreSQL and storing configs

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact. Lets take currency as an example. ...
-2
votes
1answer
156 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
112 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
29 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
225 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? ...
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 ...
1
vote
1answer
162 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 ...
1
vote
2answers
4k 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 ...
3
votes
2answers
76 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 ...
0
votes
2answers
569 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 ...
3
votes
1answer
504 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
620 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 ...
4
votes
2answers
12k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
1
vote
0answers
100 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 ...
1
vote
2answers
500 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
826 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
124 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 ...