The tag has no usage guidance.

learn more… | top users | synonyms

18
votes
1answer
27k 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 ...
12
votes
2answers
393 views

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if ...
10
votes
1answer
530 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
9
votes
1answer
466 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
7
votes
1answer
322 views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
7
votes
2answers
7k views

How to Determine the input and output values of stored procedures?

I have hundreds of SPs and I would like to find out: input parameters with type output fields with type (not output parameters) Of course I could manually go through each one and write it down but ...
7
votes
1answer
25k 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
2answers
1k views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds). After suspecting ...
5
votes
1answer
429 views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
5
votes
1answer
585 views

When should startup parameter -E be used?

We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online ...
5
votes
1answer
85 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
4
votes
1answer
2k views

How bad is query plan pollution (too many equivalent plans in cache)?

Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well. I noticed some old modules of a system have non-optimal SQL ...
4
votes
2answers
7k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\...
4
votes
1answer
1k views

RETURN TABLE is converted to RETURN SETOF int in Postgres function

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE: CREATE OR REPLACE FUNCTION test( IN parm1 date, IN parm2 interval) ...
3
votes
2answers
2k views

SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not ...
3
votes
2answers
377 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 ...
3
votes
2answers
215 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), ...
3
votes
1answer
3k 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 ...
3
votes
1answer
51 views

Logging Stored Procedure Parameter Values

I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and ...
3
votes
1answer
393 views

Package parameter blank in SSIS catalog

I have deployed a package with 2 package parameters which had their values set inside the package prior to deployment. However if I look at those parameters in my SSIS catalog project the values of ...
2
votes
1answer
1k views

SQL Parameter Sniffing Possible Workaround

Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. ...
2
votes
1answer
143 views

Best Practice for Passing and Working With Parameters Security/Performance

Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks. He says that the best practice is to take the parameter in, then declare ...
2
votes
2answers
964 views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other non-...
2
votes
2answers
296 views

Whats row size in mysql?

i mean what does it mean ?, i have mysql db and table in myisam and it shows Space usage Type Usage Data 1.3 GiB Index 429.1 MiB Total 1.7 GiB Row Statistics Statements Value ...
2
votes
3answers
463 views

How can I safeguard against SQL injection where parameterizing my query is not an option?

I'm working with ESRI's arcpy module, and I need to specify a WHERE clause as a parameter to this function. Since the result will eventually be used to retrieve data from the database, the text of the ...
2
votes
1answer
123 views

Create a Function in sql server without specifying return_data_type

I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type i want to be able to return any datatype based on a parameter sent by the function caller e.g. ...
2
votes
3answers
11k views

How can I pass parameters to an “SQL Script” from isql

I am wondering if it is possible to pass parameters to an SQL script from within sybase's isql utility. For example, I'd like to store a select statement in the file the_script.sql that would look ...
2
votes
2answers
3k 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 ...
2
votes
1answer
189 views

Alternatives to Linked Servers (Allow Inline SQL with Parameters)

I am trying to find a solution to execute inline SQL with parameters against some linked server databases. From what I have found online the only proper way to query a linked server is to use ...
2
votes
1answer
3k views

Named Parameters in SQuirreL SQL?

Is it possible to set named-parameters in queries SQuirreL SQL?
2
votes
2answers
50 views

why I can't set oracle sessions value = 35?

I want to simulate the error "ORA-00018.Maximum number of sessions exceed",so I want to limit sessions = 35 and I am going to open serverl sqlplus to simulate this error,but sessions number didnt ...
2
votes
3answers
4k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
2
votes
0answers
241 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
1
vote
2answers
586 views

SET ROLE via parameterized query

In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I ...
1
vote
1answer
65 views

Why RECOMPILE doesn't fix parameter sniffing, but local variables do?

A developer was asking me about why his procedure wasn't running, but the query was ok. When I run the query, it was fast.But running the procedure, it was executing forever. What I did was to add ...
1
vote
1answer
783 views

Stop NLS settings in init.ora getting overridden by environment variable

We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has ...
1
vote
1answer
4k views

Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB? Should select * from v$parameter return value of SQLNET.EXPIRE_TIME (if it ...
1
vote
1answer
35 views

Oracle SQL script - passing a parameter

I have an Oracle SQL script like this: delete FROM activ_princ where id in (select ap.id FROM activ_princ ap inner join sect e on e.id=ap.FK_ID_SECTIUNE_e inner join chest ch on ...
1
vote
1answer
67 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
1
vote
1answer
46 views

Make query depend on input in PostgreSQL function

I have a simple query: CREATE OR REPLACE FUNCTION companies_all_teams( IN _company_name text = NULL, IN _company_id int = NULL, OUT _result text ) AS $$ BEGIN _result := (SELECT json_agg(...
1
vote
1answer
506 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
1
vote
1answer
53 views

Are db_recovery_file_dest and db_recovery_file_dest_size parameter dynamic or static?

Please have a look at the output below: As I know we can check if the parameter is static or dynamic in Oracle using this query. Please correct me if I am wrong. But I have seen something ...
1
vote
1answer
1k views

What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name [closed]

Just can't understand why it's throwing this error? Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ...
1
vote
1answer
2k views

SQL Linked Server Passing Date Parameters

I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database. On the 2008 box I am creating a stored procedure to be used in a report. I am wanting to pass a date variable so the user ...
1
vote
0answers
50 views

Why won't my subscription parameter value stick?

I've set up an "Aggregate level" parameter on a SSRS report, so that I could easily schedule subscriptions for daily, weekly and monthly aggregates. The parameter has a Text datatype, and doesn't ...
1
vote
0answers
102 views

What would be the rule for parsing parameterized sql for Netezza to be used with nzsql?

I have a bunch of legacy parametrized SQL templates that were used by nzsql utility for Netezza. See examples here: http://queforum.com/netezza/347918-parameterized-sql-file-input-nzsql.html What is ...
1
vote
1answer
54 views

SSRS - Graph Line Value for Parameter

For the purpose of reporting database growth, I am created everything except one and most important thing. In SSDT I'm created report, which contains graph line which need to be show in depend of ...
1
vote
1answer
189 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 ...
1
vote
1answer
49 views

Plans created several times under Force Parameterization

People use forced parameterization to save plan compilation cost. However, I found things different here. The query I will run is as following, and under AdventureWorks2012 database: DECLARE @Var ...
1
vote
2answers
57 views

What is the correct query set up to access the columns in table being passed as parameter?

I am trying to use table-valued parameter to bulk upsert some data into my table. I have the following stored procedure. This setup seems to agree with the intellisense. I am not getting any errors ...