Wednesday, March 28, 2012
How to use WHERE IN @varname?
variable with a list. For example:
This works fine:
update table1 SET
column1 = @.var
where status IN (1,5)
This throws an error:
declare @.thislist as string
SET @.thislist = "(1,5)"
select top 10 id
from table1
where status in @.thislist
Error thrown is:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@.thislist'.
Any suggestions on how I can get the list in as a var? That list will
change. Any other approaches are welcome.
Thanks,
BrettTry,
declare @.sql nvarchar(4000)
declare @.thislist nvarchar(1000)
SET @.thislist = "(1,5)"
set @.sql = N'
select top 10 id
from table1
where status in' + @.thislist
exec sp_executesql @.sql
go
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Brett" wrote:
> I have an integer column named "status" that I'd like to use against a
> variable with a list. For example:
> This works fine:
> update table1 SET
> column1 = @.var
> where status IN (1,5)
>
> This throws an error:
> declare @.thislist as string
> SET @.thislist = "(1,5)"
> select top 10 id
> from table1
> where status in @.thislist
> Error thrown is:
> Server: Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near '@.thislist'.
> Any suggestions on how I can get the list in as a var? That list will
> change. Any other approaches are welcome.
> Thanks,
> Brett
>
>|||Why in the estimated execution plan this has no execution time?
Also, what is the N for?
Thanks,
Brett
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:D09B9A03-8659-4F37-9460-FE232BC1BA97@.microsoft.com...
> Try,
> declare @.sql nvarchar(4000)
> declare @.thislist nvarchar(1000)
> SET @.thislist = "(1,5)"
> set @.sql = N'
> select top 10 id
> from table1
> where status in' + @.thislist
> exec sp_executesql @.sql
> go
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
> "Brett" wrote:
>|||> Also, what is the N for?
http://www.aspfaq.com/2522sql
Monday, March 12, 2012
How to Use NOT IN
Hi,
Im having a view(View1).Its unique keys are nvcrLocationCode, nvcrAssetCode.
Theres another table (Table1) thats also contain same unique keys.
I just want to filter records from View1 which are not in Table1.
Hint:
View1
nvcrLocationCode nvcrAssetCode
H001 A0001
W001 A0001
Table1
nvcrLocationCode nvcrAssetCode
H001 A0001
It should out put
W001 A0001
How do I do this
You could use EXCEPT operator (in SQL SERVER 2005 only) or NOT EXISTS clause:
Code Snippet
create table #View1
(
nvcrLocationCode nvarchar(4),
nvcrAssetCode nvarchar(5)
)
go
create table #Table1
(
nvcrLocationCode nvarchar(4),
nvcrAssetCode nvarchar(5)
)
go
select * fro
insert into #View1 values('H001', 'A0001')
insert into #View1 values('W001', 'A0001')
insert into #Table1 values('H001', 'A0001')
select * from #view1
EXCEPT
select * from #Table1
select * from #view1 v where NOT EXISTS
(
select * from #table1 t
where t.nvcrLocationCode=v.nvcrLocationCode and
t.nvcrAssetCode=v.nvcrAssetCode
)
|||you can also try this..SELECT View1.*
FROM View1 LEFT OUTER JOIN
Table1 ON View1.nvcrLocationCode = Table1.nvcrLocationCode
AND View1.nvcrAssetCode = Table1.nvcrAssetCode
WHERE Table1.nvcrLocationCode IS NULL
AND Table1.nvcrAssetCode IS NULL