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

learn more… | top users | synonyms

1
vote
3answers
53 views

Get Column Value from Subquery in Stored Procedure

In my stored procedure I got code like @Select = '... (Select ...) AS NAME ... AS AGE ... AS CLASS ' @Where = 'CLASS = ...' I can make validation ...
14
votes
1answer
266 views

User-shared queries: Dynamic SQL vs. SQLCMD

I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come ...
1
vote
1answer
62 views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
2
votes
2answers
2k 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 ...
0
votes
1answer
288 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 ...
2
votes
4answers
118 views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
3
votes
1answer
275 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
1answer
55 views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS ...
3
votes
1answer
48 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 ...
6
votes
2answers
3k 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 ...
0
votes
0answers
25 views

Dynamic columns or many columns? [duplicate]

I am using MySQL and I am trying to figure out how best to store my dynamic data I get from my application so it can easily be transformed into a single row for reporting purposes. My current ...
3
votes
1answer
80 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 ...
4
votes
2answers
143 views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | ...
3
votes
3answers
180 views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
1
vote
3answers
94 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
2
votes
1answer
89 views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable ...
2
votes
1answer
90 views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
15
votes
2answers
2k views

Why does SQL Injection not happen on this query inside a stored procedure?

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' ...
1
vote
1answer
56 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 ...
2
votes
1answer
66 views

looking for a cleaner way to select from multiple tables

I'm currently using this code to select from 2 tables using a stored procedure. DECLARE @now as varchar(26) set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9) --Select 'SMD_' + ...
10
votes
1answer
21k 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 ...
0
votes
2answers
52 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
4
votes
1answer
266 views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = ...
2
votes
2answers
2k 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 ...
2
votes
2answers
67 views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" ...
3
votes
2answers
85 views

Switching between databases with dynamic SQL

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database. ...
-1
votes
1answer
93 views

Job successfully execute Stored Procedure but no results [closed]

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job ...
2
votes
2answers
110 views

Database design for web mail client

I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue. Lets say this application has ...
-2
votes
2answers
24k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to ...
1
vote
1answer
55 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 ...
0
votes
3answers
75 views

Dynamic query execution error

I'm trying to execute the following dynamic query code: declare @sql nvarchar(max) set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction ...
0
votes
0answers
101 views

SSIS detect & manipulate all user variables ( set all empty )

I am developing a rather large ETL ( 39 distinct Flat File or Excel Connection Managers in current build & about as many to go ) through SSIS ( Visual Studio and MSSQL Server versions both 2008-R2 ...
2
votes
1answer
150 views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
0
votes
1answer
128 views

Dynamic SQL To Send HTML Email

I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email. However, anytime I try to execute my statement ...
2
votes
2answers
61 views

Dynamic Pivot SQL table [duplicate]

Table dbsize loading with data of every database size every night. dbsize (id, database_name, database_size, insert_date) It looks like this: - 1 dbname1 1000 1/1/1900 - 2 dbname2 ...
0
votes
1answer
61 views

Create a table dynamically in SQL Server

I am trying to create a table dynamically depending upon current date in SQL Server. But I am unable to do so. I am getting error as invalid identifier. These are the details: SQL query: DECLARE ...
1
vote
1answer
57 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
0
votes
0answers
47 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
3
votes
1answer
73 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
0
votes
1answer
124 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
3
votes
3answers
111 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
1
vote
1answer
51 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
5
votes
1answer
66 views

Dynamic casting from text within plpgsql

Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
1
vote
2answers
464 views

Concatenate Columns in Stored Procedure [duplicate]

I have a stored procedure which returns a weekly result from input parameters start date and end date. I am getting multiple rows in the result. Requirement: I want to add the results of columns in a ...
4
votes
1answer
916 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 ...
0
votes
2answers
535 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 ...
1
vote
1answer
76 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
2
votes
0answers
109 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 ...
5
votes
2answers
4k 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
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 ...