March 21, 2014 at 8:11 am
Hello,
I am working on the query below. I have created the variable @AcctType='ABC' but when I run the query below, the Count returns zero. However, when I hardcode my AcctType, the query produces the expected results.
Can someone help me understand what I am doing wrong?
DECLARE
@getCnt int,
@AcctType nvarchar
SET @AcctType = 'ABC'
SELECT @getCnt = COUNT(AcctID)
FROM Customer
WHERE accttype in (' + @AcctType + ')
PRINT @getCnt
This works as expected: (hardcoded)
SELECT COUNT(AcctID)
FROM Customer
WHERE accttype in ('ABC')
March 21, 2014 at 8:40 am
SELECT @getCnt = COUNT(AcctID)
FROM Customer
WHERE accttype in (@AcctType)
You can't build a string in the query (use indirection) unless you use dynamic SQL. So the ' + @accttype + ' won't work. But the above should.
March 21, 2014 at 8:47 am
that is it! thank you very much! =)
March 21, 2014 at 9:33 am
Two serious issues here,
the first is that the
@AcctType nvarchar
is only going to be one character in size;
DECLARE @getCnt int
DECLARE @AcctType nvarchar
SET @AcctType = 'ABC'
SELECT LEN(@AcctType)
CHLEN
1
The second is the misconception that the IN clause will parse the content of the variable. It will only work if there is only one value there.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customer' AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.Customer;
CREATE TABLE dbo.Customer(AcctID INT NULL, AcctType NVARCHAR(3) NULL);
INSERT INTO dbo.Customer (AcctID,AcctType)
VALUES (1,N'ABC')
,(2,N'DEF')
,(3,N'GHI')
,(4,N'JKL')
,(5,N'ABC');
DECLARE @getCnt int
DECLARE @AcctType nvarchar(30)
/* IN works like the equal operator */
SET @AcctType = 'ABC'
SELECT COUNT(AcctID)
FROM Customer
WHERE AcctType IN (@AcctType);
/* Will not work */
SET @AcctType = 'ABC,GHI,JKL'
SELECT COUNT(AcctID)
FROM Customer
WHERE AcctType IN (@AcctType);
/* This is correct */
SET @AcctType = 'ABC'
SELECT COUNT(AcctID)
FROM Customer
WHERE AcctType = @AcctType
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customer' AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.Customer;
-----------
2
-----------
0
-----------
2
March 21, 2014 at 9:38 am
Hi Eirikur and thank you!
The senario I really am trying to achieve is:
/* Will not work */
SET @AcctType = 'ABC,GHI,JKL'
SELECT COUNT(AcctID)
FROM Customer
WHERE AcctType IN (@AcctType);
How do I make this work with mutiple parameters?
Thanks again!
March 21, 2014 at 10:13 am
Hi,
the string has to be parsed / split for this to work. Quick solution;
grab the splitter function from
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customer' AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.Customer;
CREATE TABLE dbo.Customer(AcctID INT NULL, AcctType NVARCHAR(3) NULL);
INSERT INTO dbo.Customer (AcctID,AcctType)
VALUES (1,N'ABC')
,(2,N'DEF')
,(3,N'GHI')
,(4,N'JKL')
,(5,N'ABC');
DECLARE @getCnt int
DECLARE @AcctType nvarchar(30)
/* this Will work */
SET @AcctType = 'ABC,GHI,JKL'
SELECT COUNT(AcctID)
FROM Customer AS C INNER JOIN
dbo.DelimitedSplit8K(@AcctType, ',') AS JM
ON C.AcctType = JM.Item
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customer' AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.Customer;
March 21, 2014 at 10:40 am
You just saved my day! Thanks so much!!!:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply