create table [dbo].[users]
[userid] [int] identity(1,1) not null,
[username] [varchar](255) not null,
constraint [pk_users] primary key clustered


create table [dbo].[links]
[linkid] [int] identity(1,1) not null,
[linkname] [varchar](255) not null,
constraint [pk_links] primary key clustered


create table [dbo].[userlinks]
[userid] [int] not null,
[linkid] [int] not null,
[repeat] [int] not null


create procedure [dbo].[sp_GetLinks]
@userid int
as
begin
set nocount on;
--here i need to be able to add one to the repeat or add to the table if doesn't exist
--plus i don't know how to add one to repeat for each row selected
select top 10 *
from links
where linkid not in
     (
      select top 10 ul.linkid
      from userlinks ul
      where ul.userid = @userid
      order by ul.repeat desc
     )
order by (newid())
end

quote:

---

Originally posted by webfred


Perhaps you want to have ten links and they are used for your users when visiting your homepage and the visitors shall not see the same link every time they are going to your homepage.

---

select * from userlinks

userid     linkid     repeat
1          1          1
1          63         1
1          178        1
1          423        1
1          245        1
1          121        1
1          167        1
1          386        1
1          12         1
1          499        1

quote:

---

Originally posted by daxbuddy

Declare @found_links Table
(linkid)

Insert into @found_links
Select top 10 linkID
FROM .... (your prior query would work)

Insert into UserLinks
(userid, linkid, repeat)
select @passedUserid, linkid, 1 -- '1' assumes first time use
from @found_links

-- return set
select * from @found_links

---

create function [dbo].[udf_CheckUserLink]
(
@userid int
,@linkid int
)
return int
as
begin

declare @result int
@userlinkid int
,@repeat int

select @userlinkid =  ul.linkuserid, @repeat = ul.repeat
from userlinks ul
where ul.linkid = @linkid and ul.userid = @userid

if isnull(@userlinkid,0) > 0
begin
     --record found. UPDATE
     select @repeat = @repeat + 1
     
     update userlink
     set repeat = @repeat
     where userlinkid = @userlinkid
     end
else
     begin
     select @repeat = 1

     insert userlink (userid, linkid, repeat)
     values (@userid, @linkid, @repeat)
     end
end

select @result = @repeat

return @result
end


create procedure [dbo].[sp_GetLinks]
@userid int
as
begin
set nocount on;
--here i need to be able to add one to the repeat or add to the table if doesn't exist
--plus i don't know how to add one to repeat for each row selected
select top 10 links.linkid,links.linkname,[dbo].[udf_CheckUserLink](@userid,links.linkid) as [repeat]
--i don't really need the repeat value, but decided why not send it
from links
where linkid not in
     (
      select top 10 ul.linkid
      from userlinks ul
      where ul.userid = @userid
      order by ul.repeat desc
     )
order by (newid())

Declare @found_links Table
(linkid int)

insert into @found_links
select top 10 l.linkID
from links l
order by
     isnull(
     select top 1 ul.linkid
     from userlink ul
     where ul.userid = @userid and ul.linkid = l.linkid
     order by ul.repeat asc
     ,0) asc --this can probably be a function, if it will improve performance. please advise
     , newid() --newid() helps grab id's in random order
     )

select *
from links
where linkid in (select * from @found_links)
order by newid()

userlinks table

userid     linkid     repeat
1          1          1
1          2          1
1          3          1
1          4          1
1          5          1
1          6          1
1          7          1
1          8          1
1          9          1
1          10         1