October 7, 2003 at 7:05 am
Is there a way to use a local variable as the expression part of the IN statement. I have an example of what I'm trying to do below.
CREATE TABLE [CARRIER] (
[CARRIER_CODE] [int] NULL ,
[CARRIER] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert carrier values ( 1,'ATT')
go
insert carrier values ( 1,'UPS')
go
insert carrier values ( 1,'AIRBN')
go
insert carrier values ( 1,'FEDEX')
go
insert carrier values ( 1,'SNET')
go
CREATE PROCEDURE PROC1
AS
BEGIN
SET QUOTED_IDENTIFIER Off
go
DECLARE @a varCHAR(200)
SET @a = "'AIRBN'" + ',' + "'UPS'" + ',' + "'FEDEX'"
PRINT @a
select *
from carrier
where CARRIER in (@A)
October 7, 2003 at 8:25 am
Two easy ways:
(1) Dynamic SQL - SET @sqlCmd = 'select *
from carrier
where CARRIER in ( ' + @a + ')'
sp_executesql @sqlCmd
(2) Temporary table
CREATE TABLE #tmpLookup (
CARRIER VARCHAR(5) )
INSERT INTO #tmpLookup VALUES ( 'AIRBN' )
INSERT INTO #tmpLookup VALUES ( 'UPS' )
INSERT INTO #tmpLookup VALUES ( 'FEDEX' )
SELECT * FROM [CARRIER] C
INNER JOIN #tmpLookup T ON T.CARRIER = C.CARRIER
Guarddata-
October 7, 2003 at 10:39 pm
Another easy way is to use a function to split your string.
I have a functin called SplitString which requires a string and a delimiter. It returns a table.
Then use:
Select * From carrier where Carrier in (Select value from dbo.SplitString(@A, ',')
or
Select C.* From Carrier C JOIN dbo.SplitString(@A, ',') T ON C.Carrier = T.Carrier
The latter version has given me some performance issues in the past.
Could also use the function to insert into a normal temp table and join that.
CREATE FUNCTION SplitString(
@Input Varchar(1000),
@Delimiter Varchar(3)
)
RETURNS @vTempStrings Table (Value Varchar(100))
AS
BEGIN
Declare
@vtmpString Varchar(100),
@vMark Int,
@vLength Int
If SubString(@Input, DataLength(@Input), 1) <> @Delimiter
Begin
Set @Input = @Input + @Delimiter
End
SET @vLength = Len(@Input)
While (@vLength > 1)
BEGIN
SET @vMark = PatIndex('%' + @Delimiter + '%', @Input)
IF @vMark <> 0
BEGIN
INSERT INTO @vTempStrings
Values(SubString(@Input, 1, @vMark - 1))
SET @Input = SubString(@Input, @vMark + 1 ,Len(@Input))
END
SET @vLength = @vLength -1
END
Return
End
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
October 8, 2003 at 2:44 am
Using CHARINDEX can cause major problems with false positives...
If you have a value in the db of 1 and your are check using CHARINDEX against a list of say 10,11,12,13 then you will receive a false positive. It will say that 1 exists in the list.
One way around this is to pad both side of the query. So instead checking the db value of 1 your check somthing like 0001 against a list of 0010,0011,0012,0013. I have had to do this before due to certain restraints and have created a UDF to do this 'padding' for me.
Dan
October 8, 2003 at 3:50 pm
I usually basically do what Crispin does in his example. I feel this is much easier to read and I already have the function to split the string 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply