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

learn more… | top users | synonyms

1
vote
0answers
593 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 ...
2
votes
1answer
70 views

Update various tables depending on survey answer

I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the ...
2
votes
1answer
1k views

Create A View Using Dynamic Sql

I'm trying to create a dynamic Database creation script. There are a lot of steps and we create this database often so the script looks something like this. DECLARE @databaseName nvarchar(100) = ...
1
vote
1answer
377 views

How to improve performance of SP with many optional filtering parameters?

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem ...
2
votes
0answers
143 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql ...
2
votes
1answer
3k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
2
votes
1answer
132 views

getting the output of delete statement in UDF in postgres

In Postgres in a user defined function I do execute format('delete from table where session_id = %L', id); ran without execute postgres (may) return(s) the following: DELETE 0 Is there any way I ...
1
vote
1answer
6k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
3
votes
2answers
15k 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 ...
2
votes
2answers
211 views

how to make a particular field into seperate columns?

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. I have this table in the below format: No | User | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank ...
3
votes
2answers
1k views

sp_executesql adds statements to executed dynamic script?

The Question: As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does ...
2
votes
2answers
3k 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
1k 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
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 ...
1
vote
1answer
110 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'); ...
3
votes
2answers
467 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 ...
6
votes
2answers
2k 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 ...
25
votes
2answers
16k 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
228 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
369 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
243 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. ...
8
votes
2answers
2k 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 ...
3
votes
2answers
413 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 ...
3
votes
1answer
4k 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. ...
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 ...
2
votes
1answer
712 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 ...
10
votes
1answer
16k 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 ...
3
votes
2answers
2k 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 ...
5
votes
1answer
1k 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) ...
10
votes
2answers
6k 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 ...
3
votes
2answers
34k 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
344 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 ...
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 ...
3
votes
2answers
439 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 ...
15
votes
3answers
901 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
2k 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
192 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 ...