Questions tagged [parameter]
The parameter tag has no usage guidance.
157
questions
0
votes
1
answer
27
views
Can I make a parameterized equality based condition in the where clause of a SQL statement always true
I have a series of queries which depend on a where clause which looks like the following:
where a = <a>
and b = <b>
and c = <c>
and d = <d>
The angular brackets ...
- 101
1
vote
1
answer
222
views
Why does pg_stat_statement need to be included in shared_preload_libraries?
I am working on AWS RDS Postgres v9.6, and also v14. I noticed that in our shared_preload_libraries parameter, we have pg_stat_statement included. I don't understand why that needs to be there.
In the ...
0
votes
1
answer
294
views
Unknown column in field list MariaDB
I am using PHPMyAdmin and MariaDB and get this weird error as mentioned in the title.
CALL log_total_outstanding(@message);
MySQL said: Documentation
#1054 - Unknown column 'time' in 'field list'
I ...
- 13
-2
votes
1
answer
352
views
Named parameters in queries for SQL Server
ALL,
I'm trying to find a proper syntax to do the following:
SELECT a, b, c FROM foo WHERE foo.id = :id;
(the query above is written as in SQLite).
The "normal" query would be written as:
...
- 217
0
votes
1
answer
88
views
Pentaho does not read sql script (csv)
I was trying to use a different sql script for one job. For doing this i need to pass the script by file csv, so i set the script like a variable and then i make a transformation that "copy to ...
0
votes
1
answer
80
views
SqlCommand using Parameters but still it is creating Multiple Plans for One Query
I have the below code, I'm using parameters, but still it is generating many Duplicate SQL Execution Plans. What is wrong and how it can be fixed, please?
string cmdString = @"INSERT INTO ...
- 21
1
vote
1
answer
385
views
Column name as an argument of the function called in the trigger (PostgreSQL)
I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue.
Take a look at the following code. The bottom line is that I have a function that updates the ...
- 13
0
votes
2
answers
354
views
How can I configure Automatic Memory Management for certain target values?
I am a little confused about the Oracle Database configuration.
My server:
Operating system: Windows Server 2012 R2 64bit
RAM: 40GB
Oracle: Oracle Database 12c (12.1.0.2.0) 64bit
My aim:
Oracle ...
- 11
0
votes
1
answer
48
views
load text file into db using temp variable as place holder for complex column value
Assume I have a text file with tab terminated fields as below. Where value in row m, column n as vmn. Vmn is a string which could wrapped in double quota. Generally this string variable could have ...
- 1
4
votes
5
answers
3k
views
Handling optional parameters in stored procedure
I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the ...
- 41
0
votes
2
answers
276
views
Precautions before relocating the RMAN backups?
To preface... I have mentioned this before in a previous question, but I'll add it again: Our Senior Oracle DBA has just recently passed away, and I've been taking over. My level of experience is just ...
- 11
0
votes
1
answer
129
views
Forced Parameterization generating duplicate plans
https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu
I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8. The vCore count may change much lower for other dbs but for the same ...
- 3
0
votes
1
answer
520
views
Pass variable as an input argument to a function inside another function in PostgreSQL
I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible...)
Inside a function I am trying to assign a value from calling ...
0
votes
1
answer
105
views
How to execute a trigger procedure with an expression as an argument?
How can I pass an expression as an argument when calling a trigger function?
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
'job_name',
(...
0
votes
0
answers
106
views
What C# type is the correct representation for DB2 UDB boolean?
The project that I work on uses IBM.Data.DB2 nuget package for its data layer.
I need to call into utl_mail.send_attach_varchar2 stored procedure on the DB2 side. It has a boolean parameter att_inline ...
1
vote
1
answer
55
views
How to define a parameter that will be used as an IN argument inside a function?
I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
- 133
0
votes
1
answer
96
views
What parameters should be modified after upgrading RAM memory in Oracle server
I have a linux server (rhel4) with oracle 9i (9.2.0.8) to which we have recently upgraded the RAM memory from 4G to 16G. I would like to know what server parameters I should modify and even more what ...
- 135
1
vote
1
answer
3k
views
Passing parameters for dynamic SQL in a function
I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
- 59
0
votes
1
answer
835
views
Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?
I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter.
A simplified version would look something like this:
CREATE OR REPLACE ...
- 59
0
votes
1
answer
119
views
Why doesn't the $1 syntax work for all queries?
I tried to do this:
COPY postgres_log FROM $1 WITH csv;
And with a separately sent file path, like I make all normal queries (SELECT/UPDATE/INSERT/DELETE). However, it gives:
ERROR: syntax error at ...
user215888
0
votes
1
answer
248
views
Query returns NULL - Oracle
I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through ...
- 175
1
vote
1
answer
694
views
How to write correct array string parameter for mysql query?
I'm trying to put up a correct string to pass as a parameter to a simple query.
Correct query that returns records:
SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02',...
- 77
4
votes
1
answer
397
views
How to capture runtime parameters with extended event?
I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way:
right-click extended event -> Watch Live Data and ...
- 1,193
2
votes
1
answer
1k
views
In a Postgres function, how to skip expressions if input value is null?
I have a working function. But it only works if I literally supply both the city and country variables.
CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text)
RETURNS SETOF ...
- 123
5
votes
0
answers
280
views
Hash join hint returns an error when using local variable
The query below is using the StackOverflow2010 sample database:
DECLARE @Id INT = 18471
SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id
...
- 125
4
votes
1
answer
144
views
sp_executesql: what can be parameterized
Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.
For example, predicates can, table names cannot. Number of rows in TOP can:
exec ...
- 215
5
votes
2
answers
167
views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
- 51
0
votes
1
answer
29
views
Create function with a parameter, which will become the part of IN (1, 2, 3) clause
Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to.
CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[])
RETURNS ...
- 11
0
votes
1
answer
1k
views
Retrieving the value of $1, $2 etc. from a Postgres Query
I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (Developer left on short notice and on bad terms).
I have enabled Query ...
- 73
1
vote
2
answers
135
views
Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?
I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with
Parameters being passed in:
@ID int
,@OtherID INT
...
- 193
0
votes
2
answers
3k
views
SSRS report does not work with multiple parameters
My data set has the following where clause:
WHERE a11.PDPOID LIKE '%' + @POID + '%'
My parameter @POID properties are set to single value. The report works, but if I change the parameter properties ...
1
vote
3
answers
189
views
Parameterized Dynamic TSQL - Dynamic Parameters
We have a situation where effectively the same report is requested by different customers but they:
Don't want all the columns
Want the columns in a different order than we naturally have them
Want ...
- 971
6
votes
1
answer
505
views
Mysterious procedure calls without parameters - but no exceptions generated
I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName".
...
- 63
2
votes
2
answers
1k
views
Stored Procedure - Reseed Identity
I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
- 53
0
votes
1
answer
1k
views
Mysql Command line Parameter passing to sql file
In windows mysql(5.7 version),
I want to run mysql commmand using command line for .sql file as input .
Also i was to pass some variable whose value should get used in .sql file.
somehow I am not able ...
- 11
1
vote
2
answers
137
views
Forced Parameterization and CASE statements
We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid ...
- 314
2
votes
1
answer
3k
views
ORA-48108 / ORA-48140 / ORA-48187 - diagnostic_dest value error(s) - init files / control files
This is all testing / training - none of this is related to a production env
Using ORACLE DB 12c + UNIX/LINUX 64-bit on an VM.
.
Hi Guys!
I am running in the below error(s) and seem unable to ...
0
votes
0
answers
120
views
How does query parameters work under the hood?
I thought about how can it work under the hood and I came with an idea
Db engine receives query with parameters and also defined values of those parameters
SQL is being parsed into syntax tree like ...
- 111
1
vote
1
answer
2k
views
alternatives to passing temp table as a parameter to a stored procedure
some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure:
USE MASTER
GO
ALTER PROCEDURE dbo.sp_getDBSpace
@Radhe sysname=null,
...
7
votes
1
answer
1k
views
Unfamiliar syntax - Query with Parameters in Braces at the beggining
I have run sp_WhoIsActive on one of our servers using the following syntax:
sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1
and found a spid with the ...
- 1,575
2
votes
1
answer
5k
views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
- 291
3
votes
1
answer
102
views
Why are there multiple plans for query under forced parameterization?
I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:
delete from [...
- 2,078
3
votes
1
answer
604
views
How to pass an array of arguments to User Defined Aggregate in SQL Server
I have a custom aggregation (UDA) and I want to pass an array of arguments like so:
select dbo.MyAggregation(
t.type,
N'My aggregation property',
t.param1, t.param2, t.param3
)
from ...
- 33
1
vote
1
answer
1k
views
Enable or disable a task based on parameter
I have an SSIS package called Main_Flow that executes several other packages in order.
What I'd like to do is to somehow parametrise the enabling/disabling of packages in the main flow. This way I ...
- 681
2
votes
1
answer
62
views
Is there a way to change the compaction throughput via startup options in Cassandra?
We are currently running a Cassandra cluster in K8S containers as Stateful sets. We're considering exposing the config file but we would like to know if perhaps we can provide this value as a startup ...
3
votes
1
answer
984
views
Parameterized query creating many plans
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.
Queries are like:
(@P1 varchar(1043),@P2 varchar(6))
UPDATE table
...
0
votes
2
answers
298
views
SSRS - select where parameter and fixed value
So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement.
So what I'm trying to do is ...
- 143
3
votes
1
answer
17k
views
"column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11)
Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:
DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
id smallint ...
3
votes
1
answer
329
views
Can't find compiled parameters values in the plan cache
I have found, through the query store, a query that's performing on average 297582 logical reads.
I wanted to see if I was able to tune that query a bit, and after that, try to execute the query ...
- 602
8
votes
2
answers
7k
views
Passing value of datatype interval in parametrized query
The context is connecting to a Postgres db from a rest server.
To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
- 689