Showing posts with label table1. Show all posts
Showing posts with label table1. Show all posts

Wednesday, March 28, 2012

How to use WHERE IN @varname?

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,
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