Constructing a query at runtime with string concatenation operations and executing the query from that string.
2
votes
2answers
58 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
40 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
1answer
17 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');
...
2
votes
2answers
56 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
...
3
votes
2answers
86 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 ...
4
votes
1answer
563 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
105 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
221 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
96 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.
...
5
votes
2answers
177 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
210 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 ...
2
votes
1answer
672 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. ...
2
votes
1answer
293 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 ...
9
votes
1answer
2k 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 ...
1
vote
2answers
390 views
How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive
a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.
Service Broker ...
