Questions tagged [dynamic-sql]

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

Filter by
Sorted by
Tagged with
9
votes
2answers
76k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
2
votes
1answer
102 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 ...
1
vote
1answer
727 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
400 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 N'...
6
votes
1answer
11k 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
543 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 ...
3
votes
1answer
15k 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 ...
2
votes
2answers
293 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
3k 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
5k 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
1answer
125 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'); ...
4
votes
2answers
6k 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 ...
16
votes
2answers
14k 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 [dbo]...
2
votes
1answer
1k 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 ...
7
votes
2answers
1k 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 ...
3
votes
2answers
663 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 ...
9
votes
1answer
12k 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. ...
6
votes
1answer
2k 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) ...
5
votes
2answers
67k 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) ...
4
votes
2answers
675 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 ...
19
votes
3answers
2k 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 ...
8
votes
1answer
5k 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 ...
4
votes
1answer
198 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 ...