Questions tagged [parameter]
155 questions
53
votes
2 answers
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 [¯\_(ツ)_/¯] ON…
Brent Ozar
- 41,112
- 30
- 166
- 313
39
votes
3 answers
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 not null
,name varchar(50) not null
,value…
adopilot
- 2,313
- 6
- 29
- 44
21
votes
2 answers
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 they are OUTPUT parameters.
At first I thought…
Erik
- 4,722
- 4
- 23
- 56
18
votes
2 answers
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')
, (5, 'D')
, (9, 'E');
db<>fiddle here…
Erwin Brandstetter
- 146,376
- 19
- 347
- 493
15
votes
1 answer
What allows SQL Server to trade an object name for a string passed to a system procedure
What causes it to be legal to pass an object name to the system stored procedure sp_helptext?
What mechanism converts the object name to a string?
e.g.
-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same…
JJS
- 611
- 1
- 5
- 15
13
votes
1 answer
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 subprogram name might be misspelled, a parameter might have…
Stephan Schielke
- 233
- 1
- 2
- 6
10
votes
1 answer
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 a statistics update with FULLSCAN. When it slows…
Mark Wilkinson
- 1,224
- 8
- 17
10
votes
1 answer
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 that it accepts citext as the first parameter…
Jake Feasel
- 587
- 1
- 6
- 22
9
votes
2 answers
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
, filter_param2 varchar default null)
…
Richard
- 216
- 1
- 2
- 6
8
votes
2 answers
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 older/newer than an arbitrary value.
In the example…
Yogesch
- 669
- 1
- 8
- 15
8
votes
1 answer
Output parameter not set if stored procedure fails inside a TRY / CATCH
In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of the output parameter is not the same if we call the…
irimias
- 1,861
- 1
- 10
- 24
8
votes
2 answers
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 where is the fun in that...No, literally where IS…
Michael Wells
- 81
- 1
- 3
8
votes
1 answer
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
from [schema].[tEventlog] as e
join…
Leon
- 83
- 1
- 6
8
votes
2 answers
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:
Incorrect syntax near '+'
Is this possible ?
I…
Pawel85
- 441
- 4
- 6
- 9
7
votes
2 answers
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).
After suspecting parameter sniffing as the culprit, I masked…
Onion-Knight
- 1,079
- 2
- 10
- 15