Dynamic SQL query
-
Hi,
I need some help with this.
I´ve this simple query
select * from tligfact
where ligfaccalculcode in (171,5710,5713)
ligfaccalculcode is numeric field
But the numbers inside the IN clause are variable, so I must put a subquery instead of the numeric values. I need this subquery to pass as a variable.
I take an example from this forum where explain how to concatenate a subq result, in my case is :
declare @columna1 varchar(255)
set @columna1 = ''
select @columna1 = @columna1+','+ rtrim(exportlineitem)
from (select distinct exportlineitem from invoices.dbo.codecalcul where columna='1') ss
set @columna1= right(@columna1,len(@columna1)-1)
Then @columna1 get the value 171,5710,5713
Rewriting the simple query...
select * from tligfact
where rtrim(ligfaccalculcode) in (@columna1)
I get 0 rows, that isn´t fine....
Could you help me??
Thanks in advance
Eduardo
-
For some reason this syntax won't work. Someday, I will find out why.
Why don't you just use a subquery or an inner join:
select * from tligfact
where ligfaccalculcode in (select distinct exportlineitem from invoices.dbo.codecalcul where columna='1')
or
select tligfact.*
from tligfact inner join (sselect distinct exportlineitem from invoices.dbo.codecalcul where columna='1') as cc
on tligfact.ligfaccalculcode = cc.exportlineitem
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
-
To answer your question, would this help?
declare @search nvarchar(50)
declare @stmt nvarchar(4000)
set @search = '53129,53128,53127'
set @stmt = 'select * from mails_header where id in ('+@search+')'
exec sp_executesql @stmt
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok,many thanks
but what I´m trying to do is something more complex...
Select aa, bb, 'Neto Gravado'= sum(case when tligfact.ligfaccalculcode
in ('5701','5702','5703','5704','5705','5706','5707','5708','5709','5710','5711','5712','5713','171','172','12','16','28','180','5608','7709') then 0
else (tligfact.ligfacunitprice*tligfact.ligfacqtefac) end)
from tligfact where ......
If I put a subquery inside the IN, I get an error, it saids that I cannot put a subquery inside an agregate function !!!, seems to belong from the SUM() ?
So I think that a good idea is to put the result of this subq into a variable. But does not work.
Ive tried Frank´s , but I get this "Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure. " and now I dont know how to jump this.
waiting for any other idea..........
-
Can you post what you've got so far?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
here it is..
declare @qq varchar(4000)
declare @columna1 varchar(255)
set @columna1 = '171,5710'
set @qq = 'select * from tligfact
where rtrim(ligfaccalculcode) in ('+@columna1+')'
exec sp_executesql @qq
result :
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
-
Changing your varchar decalration to nvarchar should do it
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Tks Frank, that´s the point. I´ve too many code in my head that I can´t see it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply