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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-04 : 19:38:42
|
Sune Pedersen writes "@Seen VARCHAR(4000)
SELECT TOP 1 PictureID
FROM Pictures
WHERE StatusID = 3
AND PictureID NOT IN @Seen
@Seen contains a string with seen pictureid's seperated by a comma, for each seen picture the person has seen, and we only want to show pictures to the user they havent seen, so i thought you could do it this way, but it fails, do you really have to make a tempoary table to achieve this or is there a simple way i dont know of ?
Runnning win2k, sqlserver 8
my solution is the following but i cannot believe this is the easiest way ?
CREATE TABLE #Seen
(
SeenID INT IDENTITY,
PictureID INT
)
WHILE len(@Seen) > 0
BEGIN
SET @CommaPos=CHARINDEX(',', @Seen)
IF @CommaPos > 0
BEGIN
SET @SeenPictureID = LEFT(@Seen, @CommaPos-1)
SET @Seen = SUBSTRING(@Seen, @CommaPos+1, LEN(@Seen)-@CommaPos)
END
ELSE
BEGIN
SET @SeenPictureID = @Seen
SET @Seen = ''
END
INSERT INTO #Seen (PictureID) SELECT @SeenPictureID
END
SELECT TOP 1 PictureID
FROM Pictures
WHERE StatusID = 3
AND PictureID NOT IN (SELECT PictureID FROM #Seen)
DROP TABLE #Seen" |
|
|
|
|
|
SELECT * FROM Table WHERE ID NOT IN @varcharvariable