Constructing a query at runtime with string concatenation operations and executing the query from that string.
3
votes
2answers
71 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
...
2
votes
2answers
82 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
43 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
18 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');
...
1
vote
2answers
411 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 ...
4
votes
2answers
106 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
736 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 ...
7
votes
2answers
2k views
Create View in specified database with dynamic sql?
I am writting a dynamic sql to drop and create view in different database.
So I wrote:
set @CreateViewStatement =
'
USE ['+ @DB +'];
CREATE VIEW ...
2
votes
1answer
294 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 ...
2
votes
0answers
107 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 ...
2
votes
2answers
3k 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 ...
7
votes
2answers
222 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 ...
2
votes
2answers
212 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 ...
-1
votes
1answer
98 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
181 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
1answer
713 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. ...
9
votes
1answer
3k 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 ...
4
votes
1answer
388 views
sp_ExecuteSQL, performance and table variables
Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches:
exec sp_executesql N'
declare @t table (
x int
)
insert into @t (x) ...
2
votes
2answers
6k views
How to include a datetime parameter within a stored procedure along with string query?
My stored procedure is as follows,
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime
--Select query
DECLARE @query nvarchar(max)
...
3
votes
2answers
175 views
switch TOP clause between percentage and rows by parameter
For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.
I have two ideas
Calling two different sub sproc based on passed param.
if ...
3
votes
2answers
278 views
Does adherence to the DRY principle justify dynamic SQL?
Suppose I have two queries that will be frequently run against my database:
SELECT
UserID,
UserName,
UserGender
FROM Users
WHERE UserID = @User
SELECT
UserID,
UserName,
UserGender
FROM ...
14
votes
3answers
400 views
Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?
Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add ...
5
votes
1answer
1k views
Print Parameters in Dynamic SQL
I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.
EG:
Declare @SQL nvarchar(max), @Params ...
3
votes
1answer
185 views
Can I determine if a stored procedure uses dynamic sql without parsing the definition?
Since there seems to be no way to strip the procedure definition from comments without parsing cf. this, there seem to be no reliably T-SQL query to answer this question.
Or can I get this ...
