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

learn more… | top users | synonyms

0
votes
1answer
336 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
1answer
36 views

A short way to execute a dynamic SQL string

I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution. This is a well known example of such table actually: ...
0
votes
0answers
24 views

Stored procedure generating dynamic SQL is slow for some paramaeters

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for some parameters. This has been an issue everyday and we are making some ...
5
votes
3answers
2k views

what is causing arithmetic overflow in the query below? [closed]

when I run the following query on MY_Database select * from sys.sysfiles I get the following results: But when I run a dynamic query that gets the percent of free space I get: Msg 8115, ...
1
vote
1answer
47 views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
1
vote
2answers
62 views

Get the Text of the currently running SQL?

This is something I am just interested in knowing if it is possible. Could I write an SQL query that could return the text of itself as a variable? Where I might use this would be on things like ...
1
vote
0answers
30 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
0
votes
2answers
84 views

Search entire database

Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It ...
0
votes
1answer
48 views

How to get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
1
vote
3answers
184 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
1
vote
1answer
102 views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
3
votes
1answer
452 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 ...
2
votes
1answer
56 views

Query Each Database in A Table [closed]

We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the ...
1
vote
2answers
66 views

Building a where clause based on user selections

My group and I are developing a multi agent system (MAS), a system that is supposed to conduct statistics on specific fields of the agents' databases using semantic search and ontology (the user ...
0
votes
1answer
62 views

how to send email message with the results of a job?

I have the following update, that creates a table backup in a database called tablebackups with the records that are going to be updated, and then do 2 different updates. USE ...
3
votes
2answers
3k 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 ...
2
votes
0answers
60 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
0
votes
0answers
14 views

Dynamic Query with User Input 2 on same Input value

Using PL/SQL I have a Dynamic Query that requires users to provide input. How do I set my where condition on an input to ignore when value is null? I have two condition within the same type Model1 ...
1
vote
1answer
48 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
1
vote
2answers
92 views

How can I pass a list of databases to sp_MSforeachdb?

I have the following script that when run gives me a comma delimited list of database names. SET NOCOUNT ON declare @db_list NVARCHAR(MAX) DECLARE @command varchar(1000) ...
0
votes
0answers
36 views

Need hep with a query to retrive data from realtional database

I am having a database for blog. I want to retrieve posts by specific condition (i.e by location and category). The catch is both location and category are present in a single table called terms. This ...
3
votes
2answers
91 views

Find changed columns for near-identical rows across tables

I run PostgreSQL 9.3.5 on Windows 7, 64-bit. My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. ...
7
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 ...
14
votes
1answer
401 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
96 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 ...
3
votes
4answers
314 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
245 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
116 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 ...
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
101 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
652 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
393 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
104 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, keys,...
1
vote
1answer
165 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
275 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
105 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
72 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_' + replace(convert(...
10
votes
1answer
23k 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
121 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
834 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) = Replace(...
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
110 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
152 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
232 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
147 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
28k 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
68 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
135 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
219 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 ...