Varchar Variable Is Not Working in Where Clause

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 ORs 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



Leave a reply



Submit