What is missing Here - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

What is missing Here

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 07:10:38

Hi,

With this code,

DECLARE @nc_c int
DECLARE @conta varchar(20)
DECLARE @Sconta varchar(100)
DECLARE @vc_c varchar(20)
set @conta='62211'
set @nc_c=len(rtrim(@conta))
set @Sconta=''

WHILE (1=1)
BEGIN
set @vc_c=substring(@conta,1,@nc_c)
if @nc_c >=1
set @sconta=@sconta+''''+@vc_c+''''
if @nc_c > 1
set @sconta=@sconta+','

else
BREAK

set @nc_c= @nc_c - 1
END

print @sconta //RETURNS '62211','6221','622','62','6'

select * from CCTPOC
where ano='2011'
and COD_CONTA IN (@Sconta)
and TP_CONTA='G'

---
returns No Records

...and COD_CONTA IN ('62211','6221','622','62','6') //returns OK

...and COD_CONTA IN (@Sconta) //can't put the string here ?

Regards
Pedro

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 08:31:33

What's in CCTPOC
try printing @Sconta to see what the query is.
suspect you want
where ',' + @Sconta + ',' like '%,'+ COD_CONTA + ',%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 09:16:33

Hi,

The query:

..and COD_CONTA IN ('62211','6221','622','62','6')
Returns some rows, that what i want,

If i use the Var
@sconta that have '62211','6221','622','62','6'

the Query:

...and COD_CONTA IN (@Sconta)

doesnt' return any rows.

Is it possible to use a var with "IN " clause ?

Regards.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 10:07:35

Yes - like in my previous post usuing a like statement - you could also use a cte to split it into a derived table and join to that.
You have embedded quotes in the string so proably want
where ',''' + @Sconta + ''',' like '%,'+ COD_CONTA + ',%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 10:42:15

Hi,

I don´t want "LIKE" clause, i need "IN" clause.

AS you can see in the code i Have a print @sconta

Regards.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-18 : 14:17:45

I've had very little success using variables with IN. None in fact.
For example, this returns nothing

declare @var varchar(100)
set @var = '''D'''
select @var
select * from sometable st
where st.active in (@var)

while this returns data

select * from sometable st
where st.active in ('D')

If I run it as a dynamic query it works fine...so I'd have to say you can't use IN with a variable unless you build and execute a dynamic query with it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-18 : 14:34:07

quote:
Originally posted by psfaro

Hi,

I don´t want "LIKE" clause, i need "IN" clause.

AS you can see in the code i Have a print @sconta

Regards.

You can't use a variable in an IN clause like that. Nigel is showing you an "Inverse-Like" clause (that's what I call it) that can function on a variable and return results as if you were using an IN clause. Here are some break-downs which may or may not help:

DECLARE @conta varchar(20) = '62211'
DECLARE @Sconta varchar(100) =  '''62211'',''6221'',''622'',''62'',''6'''

SELECT
    CASE WHEN @conta IN (@Sconta) THEN 1 ELSE 0 END,
    CASE WHEN @conta IN ('62211','6221','622','62','6') THEN 1 ELSE 0 END,
    CASE WHEN ',' + @Sconta + ',' LIKE '%,''' + @conta + ''',%' THEN 1 ELSE 0 END

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 20:19:01

Hi

I found the best way to do it , with gain about 30% in perfomance

1-Create a table value that receives "conta" parameter and create the number of records needed.

Ex: 62211
6221
622
62
6

2- Update CCTPOC table JOINING the table value

... FROM CCTPOC a INNER JOIN dbo.tvcontas(@conta) b ON a.COD_CONTA=b.cConta
where ano=@ano and TP_CONTA='G'

Conclusion :

I try too a Dynamic SQL , but this method to substitute Vars in IN Clause is great

Tanks to all that helps

Regards

Pedro.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources