Questions tagged [parameter]
The parameter tag has no usage guidance.
149
questions
4
votes
5answers
177 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 ...
0
votes
2answers
35 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 ...
0
votes
1answer
61 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 ...
0
votes
1answer
43 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
1answer
36 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
0answers
44 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
1answer
18 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 ...
0
votes
1answer
57 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 ...
1
vote
1answer
268 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 ...
0
votes
1answer
135 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 ...
0
votes
1answer
50 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 ...
0
votes
1answer
47 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 ...
0
votes
0answers
94 views
EMC Legato NSR_DATA_VOLUME_POOL in Rman script
How to put the NSR_DATA_VOLUME_POOL parameter in rman in case this parameter has spaces, I mean that I need to put NSR_DATA_VOLUME_POOL = Pool Oracle but it throws an error however with PoolOra it ...
1
vote
1answer
44 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',...
3
votes
1answer
152 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 ...
2
votes
1answer
158 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 ...
5
votes
0answers
161 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
...
4
votes
1answer
80 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 ...
5
votes
2answers
97 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, ...
0
votes
1answer
25 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 ...
0
votes
1answer
474 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 ...
1
vote
2answers
74 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
...
0
votes
2answers
2k 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
3answers
151 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 ...
6
votes
1answer
492 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".
...
2
votes
2answers
640 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 ...
0
votes
1answer
850 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 ...
1
vote
1answer
94 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 ...
2
votes
1answer
2k 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
0answers
65 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 ...
1
vote
1answer
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,
...
6
votes
1answer
917 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 ...
2
votes
1answer
4k 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(...
3
votes
1answer
74 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 [...
3
votes
1answer
412 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 ...
1
vote
1answer
988 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 ...
2
votes
1answer
54 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
1answer
620 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
2answers
223 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 ...
3
votes
1answer
14k 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
1answer
284 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 ...
7
votes
2answers
5k 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 ...
0
votes
0answers
52 views
In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't
I am using SQL Server 2016-SP1-CU8
Here is the code to setup my scenario:
--1. Drop indexed views (in case of re-running
IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
IF ...
0
votes
1answer
23 views
Parameter evaluation against harcoded string always return false
In Mysql workbench I am trying to make query whit parameter.
But I have strange behavior when I try to compare parameter with static strings.
Why I do not get any rows form query like this ?
When I ...
4
votes
1answer
620 views
Postgres “session variables” kludge on AWS RDS
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the ...
0
votes
1answer
2k views
SQL SERVER dynamic Query with Like Statement for null values
I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only &...
3
votes
1answer
481 views
How to get the function name of a regprocedure in PostgreSQL?
I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:
CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
RETURNS text AS
$...
53
votes
2answers
5k views
How to create Unicode parameter and variable names
All of this works:
CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯...
4
votes
3answers
3k views
AddWithValue performance and plan cache implications
Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:
cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);
In ...
1
vote
1answer
4k views
How to create SQL Stored Procedure with parameters of type List<string>
I need to create a Stored Procedure that will take parameters of type List. For example :
Select *
From Table
Where State in @StateList
or Lob in @LobList
or Company in @CompanyList
The ...
