ALTER FUNCTION [dbo].[BC_getCustomerFavorites]
(
	-- Add the parameters for the function here
	@CustomerId  Bigint, 
	@TypeId TinyInt
)	
RETURNS Nvarchar(75)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Returnstring nvarchar(75)
	DECLARE @Description as nvarchar(75)
	Declare @Type as char(10)
	Declare @Count as int
	SET @returnstring = ''

	-- Add the T-SQL statements to compute the return value here
	IF @TypeId = 1
		-- select favorite book
				select		@returnstring = top 1 productid
				from		tblBC_CustomerProducts c 
				where		c.customerid = @CustomerId AND 
							c.typeid= 1
		
	IF @TypeId = 2
		-- select favorite genre
				select		@returnstring = top 1 p.numeriek1
				from		tblBC_CustomerProducts c INNER JOIN
							tblProduct p ON c.productid = p.productid
				where		c.customerid = @CustomerID AND 
							c.typeid= 2
				group by p.numeriek1
				order by count(*) DESC, newid()

	IF @TypeId = 3
		-- select favorite author
				select		@returnstring = top 1 p.text1
				from		tblBC_CustomerProducts c INNER JOIN
							tblProduct p ON c.productid = p.productid
				where		c.customerid = @CustomerID AND 
							c.typeid= 2
				group by p.text1
				order by count(*) DESC, newid()
				

	-- Return the result of the function
	RETURN @returnstring

END

LTER PROCEDURE [dbo].[BC_getFavoriteBook]
	-- Add the parameters for the stored procedure here
	@CustomerId Bigint

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- select favorite book
	select		top 1 productid as Description, 'book' as Type, 1 as  Count
	from		tblBC_CustomerProducts c 
	where		c.customerid = @CustomerId AND 
				c.typeid= 1


    -- select favorite genre
	UNION ALL
	
	select		top 1 p.numeriek1 as Description, 'genre' as Type, count(*) as  Count
	from		tblBC_CustomerProducts c INNER JOIN
				tblProduct p ON c.productid = p.productid
	where		c.customerid = @CustomerID AND 
				c.typeid= 2
	group by p.numeriek1
	order by count(*) DESC, newid()


    -- select favorite author
	UNION ALL
	select		top 1 p.text1 as Description, 'author' as Type, count(*) as  Count
	from		tblBC_CustomerProducts c INNER JOIN
				tblProduct p ON c.productid = p.productid
	where		c.customerid = @CustomerID AND 
				c.typeid= 2
	group by p.text1
	order by count(*) DESC, newid()

END

select @returnstring = top 1 p.text1

select top 1 @returnstring = p.text1

quote:

---

Originally posted by Peso

NEWID() always return same value in the same scope and execution.


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by Michael Valentine Jones

This is incorrect syntax:

select @returnstring = top 1 p.text1

It should be:

select top 1 @returnstring = p.text1

You are not allowed to use non-deterministic functions, like newid(), in a function. If you need to use it, pass it as an input parameter to the function.

---

quote:

---

Originally posted by SammyWaslow

quote:

---

Originally posted by Peso

NEWID() always return same value in the same scope and execution.


Peter Larsson
Helsingborg, Sweden

---

Not true,

the way I use it here, when several rows have the same value for count(*), the newid() sorts them randomly each time you perform the select. Tried and tested the query itself!

---