Questions tagged [variable]
13 questions
1
vote
1 answer
Why do variables in the WHERE clause cause the execution time to balloon
I have a t-sql query that executes hourly, pulling in a variable amount of data depending on the current time when the query executes, i.e.: if it is executing between midnight and 2 AM local time (7 to 9 AM UTC) I want to pull in the last 120 days'…
Jim Stephenson
- 11
- 2
1
vote
1 answer
What is the optimal way to handle literals that are referenced in NOT IN clauses of many queries within a Stored Procedure?
Back in the days of C (before C#) and in environments where memory was very limited or expensive (or both), there was a benefit to reducing repetitive use of a literal as it would reduce memory usage and executable size, as each copy of the string…
Mark Freeman
- 2,293
- 5
- 32
- 54
1
vote
1 answer
Is it safe to use user defined variables inside stored procedures in MySQL?
Currently I'm working with Stored Procedures in MySQL and I'm using in some procedures user-defined variables and I've seen that type of variables are initialized in the current session and keep their value until the session ends.
I was also working…
Edgar Magallon
- 123
- 7
0
votes
1 answer
How to define variable in dynamic SQL in IBM Db2?
In Db2 v11.5 on Linux I would like to create variable and then use this variable in SELECT statement. I don't want to create procedure or function on database, I just want to do this in dynamic SQL from db2cmd or some database tool.
Simplified…
folow
- 523
- 1
- 6
- 25
0
votes
0 answers
Dynamically selecting a table in a query based on result from joined SELECT
I have seen many examples but they don't match with my special case (I know... everyone's case is a special case :D )
I have to match values from different tables, some of which are named after the id field of the main table (whenever a new customer…
jprealini
- 101
- 1
0
votes
2 answers
PostgreSQL: Issue Declaring Variables in Query
I'm trying to declare a variable for later use in a PostgreSQL query. I've done this often in TSQL, but I wasn't sure about the syntax.
The stuff I've seen online all points to something like this:
declare timestamp_utc timestamp :=…
Lexen
- 13
- 1
- 2
0
votes
1 answer
comparing a variable in when oracle sql
I want to use a variable in when clause of oracle sql like below
DEFINE balancing_id = 0--'16493'--null;
ON A.BALANCING_ID =
CASE
WHEN &balancing_id is null-- &balancing_id = ''--balancing_id>0
THEN B.BALANCING_ID
ELSE…
guradio
- 95
- 6
0
votes
1 answer
execute insert and select statements consecutively after a case statement
I want to execute insert and select statements once a condition is met on mysql, not sure how to do it.
##declare variables
SET @fvar = 'sometxt', @svar = 'sometxtagain';
SELECT @start := 1, @finish := 10;
select count(somecolumnID) as…
0
votes
0 answers
How to use variable in the select query PostgreSQL
I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error [42601]: ERROR: syntax error at or near "with recursive"
Position: 287"
query result "select * from tbl1" this is a list of numbers: 110,111,112...
Each number…
AleksTr
- 1
- 1
0
votes
1 answer
Average Density & cardinality estimation
In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in SSMS. After checking the basics like the same SET options, we found that there was one difference: SSMS used declare @variable1 and…
Hugo Delsing
- 113
- 6
0
votes
0 answers
Local variables and their impact on execution plans in SQL Server Stored Procedure
If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is it a good practice to use them?
lifeisajourney
- 751
- 1
- 8
- 20
0
votes
0 answers
MySQL IF NULL of a variable is not working as expected in a trigger
We have the following trigger to ensure that a version field is updated in a related table, normally the trigger contains only the UPDATE statement, the INSERTs are added for debugging and to explain the problem:
CREATE TRIGGER `tableB_ins` BEFORE…
Thomas Lauria
- 101
- 2
0
votes
1 answer
Count how many variables equal a target value
Is there a smart way to count how many variables equal some target value (in SQL Server)?
For example, if I have:
declare @a int = 1;
declare @b int = 2;
declare @c int = 1;
declare @target int = 1;
Then I'd like to do how many of @a, @b, @c eqauls…
HeyJude
- 467
- 7
- 18