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' worth of data; outside that window I only want to bring in 3 days' worth of data. While creating the query I tried a few things to avoid having to repeat the query in an IF ELSE statement with hard coded values. As a baseline for testing I created the following query:

SELECT	COUNT(*)
FROM	dbo.Tickets t
		JOIN dbo.TicketsDetails td ON  t.ticketGUID = td.ticketGUID
WHERE	td.dtCreated >  DATEADD(dd, -1, CAST(GETUTCDATE() AS date))

With the hardcoded interval it returns a value of about 750,000 in .829 seconds. When I modify it to use local variables (the second or third WHERE clause below), however, execution time explodes to over 10 minutes:

DECLARE	@Interval INT,
		@StartDate DATE;
SELECT	@Interval = CASE WHEN DATEPART(hh, GETUTCDATE()) IN (7, 8) THEN -120 ELSE -1 END
		, @StartDate = DATEADD(dd, @Interval, CAST(GETUTCDATE() AS date));
SELECT	COUNT(*)
FROM	dbo.Tickets t
		JOIN dbo.TicketsDetails td
			ON  t.ticketGUID = td.ticketGUID
--WHERE	td.dtCreated >  DATEADD(dd, -1, CAST(GETUTCDATE() AS date))
WHERE	td.dtCreated >  DATEADD(dd, @Interval, CAST(GETUTCDATE() AS date))
--WHERE	td.dtCreated >  @StartDate

My question is why does this happen, and if this is working as designed what workarounds are there so I don't have to double the code?

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 sample:

BEGIN
     DECLARE name VARCHAR(10);
     SET name = 'John';
     SELECT * FROM SYSIBM.SYSDUMMY1 WHERE name = :name;
END;

--- **EDIT:** Why I don't want to create stored procedure/function? I perform audit every half a year for database privileges/authorizations on several databases and **I don't have create procedure privilege on some of the databases,** because of strict security settings. In most cases I only have access to system catalog tables which is perfectly enough I can create audit. I list database users and then for "suspicious" users I perform full permission/authority checks executing several SQL-s from system catalog. Up till now for every user I have used text editor and used find/replace, which is OK solution, but variable is better solution. Thanks!

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 is added, a "Bill_xxx" table is created). For example: table: Customers -------------------------------- custId | name | status -------------------------------- 34 | CustomerA | Active 35 | CustomerB | Inactive 36 | CustomerC | Active -------------------------------- table: Bill_34 -------------------- id | InvoiceNumber -------------------- 001 | 98767575 002 | 98767576 -------------------- table: Bill_35 -------------------- id | InvoiceNumber -------------------- 001 | 36528 002 | 36529 -------------------- table: Bill_36 -------------------- id | InvoiceNumber -------------------- 001 | 485795 002 | 485796 -------------------- Basically I'm trying to find a way to build a query like this, just to run it on demand via SQLYog. (I know I can use a cursor, but I need to run this in a prod db, for which I don't have permissions to create Stored Procedures): Select * from (Select custId from Customer where status = 'Active') as customers INNER JOIN (Select * from Bill_[custId]) as bills If it's not clear, I need to dynamically build the name of the table in the second query based on the id's I get from the first one And expect to receive something like this -------------------------------------------- custId | id | InvoiceNumber -------------------------------------------- 34 | 001 | 98767575 34 | 002 | 98767576 35 | 001 | 36528 35 | 002 | 36529 36 | 001 | 485795 36 | 002 | 485796 ____________________________________________ Is this even possible?

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 := current_timestamp; select start_date; when I run the above query, I get an error message: I'm sure this is simple, but I just can't find the answer online. Any help you could provide would be greatly appreciated.

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 &balancing_id END But I get the error below : ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator" *Cause: *Action: this line: >WHEN &balancing_id is null-- &balancing_id = ''--balancing_id>0 UPDATE: > WHEN 1 = 1 works as expected How to use variable like this?

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 would appear in the compiled executable. (In C# I think it is a best practice to use a resource file, but I could be wrong.) In modern times, is it better in a lengthy T-SQL stored procedure to use a literal many times or to assign the literal to a variable and then use that in the code? For example, let's assume with have a stored procedure where there are dozens of queries that cannot be consolidated due to different joins and/or additional WHERE clauses where we have in each, and there is an index with [Key] as the key column along with any other selective column used in an equality predicate, and [Type] and all other needed columns are INCLUDE columns in an non-clustered index:

x.[Key] = @Value1 AND x.[Type] NOT IN ('Project', )

Would it be better to have something like:

@Project CHAR(7) = 'Project';

and then have

x.[Key] = @Value1 AND x.[Type] NO IN (@Project, )

in each query? Use of the variables would prevent having useful statistics for the optimizer, but in the case of a NOT IN with many entries it probably couldn't make us of them anyway. What would be the advantage(s) or disadvantage(s) of each approach? Does the answer change if it is an **IN** instead of a **NOT IN** (with the understanding that when I use "dozen", I mean approximately 12 but definitely 15 or fewer)?

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 columnname, case when count(somecolumnID) = '0' then ( ##insert statement here which will automatically generate a unique id, use @fvar here ##select statement here to get the unique id (@tvar) from first insert statement ##insert statement here which will automatically generate a unique 2nd_id (@fovar), use @svar and @tvar here ##select statement here to get the unique 2nd_id from second insert statement, use @fovar here ) else 'record exist. nothing to do here' end as output from table_name where id = 'user_input';

I'm looking for a way to write a recursive query, but I'm getting an error. "SQL Error : 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 must be inserted into a row "s_table_sd_||r.cut_name||_db_tmp.e_pack_log" i use: with tbl1 as ( select schema_name ,rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') as cut_name from information_schema.schemata s where schema_name like '%db_tmp' and rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') '' ) with recursive r as ( select cut_name from tbl1 union select * FROM s_table_sd_||r.cut_name||_db_tmp.e_pack_log where s_table_sd_||r.cut_name||_db_tmp.e_pack_log like '%'||r.cut_name||'%' ) select * from r Any ideas how to fix the request?

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 the code used an inline parameter. I never though that would make a difference, but the microsoft docs gave the solution and even a short explanations. > The following queries are different. The first query uses Average > Density from the histogram for cardinality estimation, while the > second query uses the histogram step for cardinality estimation: SQL declare @variable1 = 123 select * from table where c1 = @variable1 SQL select * from table where c1 = 123 Great, problem solved. But I have no idea what the explanation actually means. I've read up on cardinality estimation and understand that it's used to select that best query plan. But how does that change when using a variable or not? - Is using a variable always better? - If not, is there a better way to find out what to use other then "use one until it fails and check the other"? This is the first time I really noticed this because it changed from <10ms to 10 seconds, but I'm wondering if other queries are affected by this, but just a lot less.

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?

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 INSERT ON tableB
 FOR EACH ROW 
   BEGIN
     IF @entityVersion IS NOT NULL THEN

       INSERT INTO debug_log 
         VALUES (null, now(), "tableB_ins 1", NEW.taskId, IFNULL(@entityVersion, "WAS NULL"), ROW_COUNT());
       
       UPDATE tableA SET entityVersion = @entityVersion WHERE taskId = NEW.taskId;
       
       INSERT INTO debug_log 
         VALUES (null, now(), "tableB_ins 2", NEW.taskId, IFNULL(@entityVersion, "WAS NULL"), ROW_COUNT());

       SET @entityVersion := null;
     END IF;
   END ;;

The @entityVersion variable is set in a separate request before the INSERT into tableB, which is working as expected. Sometimes we get the situation, that @entityVersion is not set and is therefore null. But randomly the above UPDATE and the debug INSERTs are still called, which should be prevented by the IF @entityVersion IS NOT NULL clause. The content in the debug_log table is:

| 155 | 2022-10-11 12:39:18 | tableB_ins 1      | b613 | WAS NULL | -1           |
| 156 | 2022-10-11 12:39:18 | UPDATE WAS CALLED | b613 | 11       | 0            |
| 157 | 2022-10-11 12:39:18 | tableB_ins 2      | b613 | WAS NULL | 1            |

The final question: how can it be, that the inserts are added with "WAS NULL", which means that @entityVersion was null, although the IF NOT NULL is surround the statements? How can that be?

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 with statements like select into @user_defined_variable but I realized that doing that is very risky, specially on logins/authentications. So the solution in this case was to use the statement set @user_defined_variable instead of select into. But I'm really not sure if it's enough using the set, because that type of variables will keep their value while the session is not finished. Now imagine that the server receives several requests at same time on the Stored Procedures that are using the same @user_defined_variable, can exist a collision of values in this case? For example, if the stored procedure called login uses the user defined variables @uuidUser and the stored procedure called home also uses the @uuidUser, does exist the risk that the home procedure uses the value of @uuidUser assigned inside the login procedure? **Note**: I'm working with Node.js and I only have one connection to the MySQL instance,I don't create a connection for every request. So the @user_defined_variables will always exist.

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 @target. In imperative languages, it's easy to make an inline array of the variables, and then count them - for example in JS: var a = 1, b = 2, c = 2, target = 1; if ([a, b, c].filter(item => item == target).length == 1) // do something The equivalent in SQL of that "inline array" would be a single column table, but this would require using DECLARE TABLE, which I'd like to avoid. Is there a similarly easy method for such counting in SQL? Please note I'm less interested in "creating a table without declaring it" - what I really care about is the counting of variables against a target variable, so if it can be done without using tables at all, then it would be better.