Varchar variable is not working in WHERE clause
Because when you declare
, default varchar
length is 1
. So @MyVarchar
ends up being 'A'
.
This is different to cast(something as varchar)
, where default length is 30
.
The right thing is
DECLARE @MyVarchar varchar(10) = 'ABCDEF';
where 10
is the length of the column in the table.
Use a varchar local variable in WHERE clause
In general this won't work without anything like EXECUTE
or similar.
But you're lucky here. These multiple OR
s could be simplified to
code IN ('RTR',
'RTL',
'LRV',
'LPV',
'LN',
'LFR',
'LCV')
. That follows that you could use a table variable you push your codes into and use it as the list for IN
:
DECLARE @codes
TABLE (code varchar(3));
INSERT INTO @codes
VALUES ('RTR'),
('RTL'),
('LRV'),
('LPV'),
('LN'),
('LFR'),
('LCV');
DECLARE @L01 int = 0;
SELECT @L01 = round(sum(balance) * -1, 2)
FROM loan
WHERE paydue > @databasedate
AND code IN (SELECT code
FROM @codes);
PRINT '"L01": ' + cast(@L01 AS varchar(15)) + ';';
Therefore it can be easily extended (which I guess is the reason behind your approach) by inserting more codes into the table variable.
Where condition as varchar using variable
You can programmatically build the conditions like so:
declare @dataSourceId varchar(500) = ''
declare @SQL varchar(500) =
'select * from datasources where data is not null and '
+ case
when @dataSourceId <> '' then 'datasourceid in (' + @dataSourceId + ')'
else 'datasource is not null'
end
SQL: Conditional where clause not working
Try this one -
DECLARE
@EmlAdd VARCHAR(100)
, @MblNum VARCHAR(100)
SELECT
@EmlAdd = ''
, @MblNum = '5555555'
SELECT [USER_ID], CONTACT_VALUE
FROM dbo.TBL_CONTACTS
WHERE CONTACT_VALUE IN (@MblNum, @EmlAdd)
Mysql Stored procedure issue with varchar variable in where clause
Problem here is that the procedure is referencing your global variable qty
in favour of the Qty
column on your mytable2
. Try changing this:
declare qty int;
to this
declare v_qty int;
Related Topics
Is There a Nesting Limit for Correlated Subqueries in Some Versions of Oracle
What Is It Exactly a Blob in a Dbms Context
SQL Server Update Trigger, Get Only Modified Fields
Error: Functions in Index Expression Must Be Marked Immutable in Postgres
How to Convert a "Legacy" Left Outer Join Statement in Oracle
Errors in SQL Server While Importing CSV File Despite Varchar(Max) Being Used for Each Column
Get Most Common Value for Each Value of Another Column in SQL
What's the Execute Order of the Different Parts of a SQL Select Statement
Function for Week of the Month in MySQL
Group by with Clob in Select-Statement
Slow Simple Update Query on Postgresql Database with 3 Million Rows
Anonymous Table or Varray Type in Oracle
Why Do We Need Group by with Aggregate Functions
Access - Compare Two Tables and Update or Insert Data in First Table
How to Show Row Numbers in Postgresql Query