Questions tagged [parameter]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
4
votes
3answers
1k views

SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure

Given the following stored procedure, can I protect against SQL injection in the supplied additional WHERE condition? CREATE PROCEDURE [dbo].[ProtectDynamicWhereClause] (@TableName varchar(50), ...
4
votes
1answer
49 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
87 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, ...
2
votes
1answer
2k views

MariaDB I/O capacity with innodb_io_capacity parameter

I use hdparm to test my SSD hard drive I/O benchmark : [root@mail ~]# hdparm -t -T /dev/xvda2 /dev/xvda2: Timing cached reads: 12906 MB in 1.99 seconds = 6477.79 MB/sec Timing buffered disk ...
0
votes
1answer
330 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 ...
0
votes
1answer
18 views

Postgres: 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
2answers
193 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 ...
0
votes
1answer
24 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 ...
0
votes
1answer
411 views

SSRS2016 external parameters for mobile report

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's ...
1
vote
2answers
47 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
0answers
11 views

Does replica config parameter affect primary/master RDS Postgres?

I want to add hot_standby_feedback = on to a RDS readonly replica. The problem is that the replica currently has a parameter group already attached, which is inherited from the master. My question is:...
0
votes
1answer
125 views

Maximum or suggested number of bound query-parameters in InnoDB (MySQL)?

We have code that adds thousands of parameters to a query as members of an IN (...) criterion. My aesthetics say to bucket it into multiple queries, but I want an objective reason. Does InnoDB have a ...
0
votes
2answers
590 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 ...
3
votes
1answer
433 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 ...
3
votes
3answers
26k views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
0
votes
2answers
135 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 ...
11
votes
1answer
72k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
6
votes
1answer
565 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 ...
3
votes
1answer
763 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is intentional....
1
vote
1answer
714 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 ...
6
votes
1answer
486 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
281 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 ...
3
votes
4answers
12k views

Views with parameters, is it possible?

I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and ...
8
votes
2answers
15k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
2
votes
1answer
855 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 ...
1
vote
1answer
75 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 ...
0
votes
0answers
36 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 ...
6
votes
3answers
26k views

SQL Stored Procedure with Output Parameter

I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an ...
1
vote
1answer
695 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, ...
8
votes
1answer
4k views

How to use an array as argument to a VARIADIC function in PostgreSQL?

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module. I would like this to be a simple wrapper around the built-in function, the only difference being ...
2
votes
1answer
2k 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
60 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
253 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 ...
2
votes
1answer
45 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
467 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 ...
4
votes
2answers
431 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 stored procedure based on passed ...
3
votes
1answer
7k 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
248 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 ...
5
votes
2answers
3k 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
49 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 ...
34
votes
3answers
62k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
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 ...
0
votes
1answer
1k 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 "...
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 [¯...
3
votes
1answer
241 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 $...
3
votes
3answers
2k 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 ...
0
votes
1answer
7k views

INSERT INTO statement with parameters for tableName, columns and values?

Is it possible to have an SP that looks something like this: DECLARE @tableName = null; DECLARE @cols = null; DECLARE @values = null; INSERT INTO @tableName @cols @values; The resultant query looks ...
4
votes
1answer
1k views

Odd behavior with SSRS 2016 drop down parameter list

I have a report that is "working" fine except for an issue. I have four dropdown list parameters where I am getting the values from queries. When I select a value from a dropdown list, it selects ...
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 ...
0
votes
0answers
158 views

My most recent expensive query is sp_procedure_param_rowset

I'm stress testing our software running many transactions and reports at the same time on one same DB. analyzing the most recent expensive queries, I found that the top query is the ...