stored function problem - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored function problem

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-01-30 : 09:58:54

Hi,

when i run this code, function does not get through; should i use procedure or can this be done within function


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE function [dbo].[fn_user_product_groups] 
(@userid as int) returns varchar (1000)
as 
begin
declare @product_groups as varchar(900);
set @product_groups = '';

select @product_groups =

	userid
	,group1
	,group2
	,group3
from
	(select
		userid
	,SUM(CASE WHEN groups.id = 6   THEN 1 ELSE 0 END) AS  group1
	,SUM(CASE WHEN groups.id = 8   THEN 1 ELSE 0 END) AS  group2
	,SUM(CASE WHEN groups.id = 9   THEN 1 ELSE 0 END) AS  group3

	from
		groups_products as groups
		join users as u
		on u.userid = groups.userid
	
	where
		u.userid = @userid
	and condition1 = 1
	and condition2 = 2

group by userid) as a

group by
	
	userid
	,group1
	,group2
	,group3

  return @product_groups;
end

thank you for help :)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-30 : 10:09:43

Functions normally return single value.

What you expect your function to return?
And, how/where is the function going to be used?

A set of sample data definetely will help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 10:43:16

A function can return a resultset too.

What is the purpose with the function? You have told the function to return VARHAR(1000), but the RETURN part is written to return a value from multiple columns and multiple records.

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-01-30 : 10:47:26

peso: Thank you for answer. and yes, that is the problem. How do i "convince" this function to return a multiple columns and multiple records? :-)

do i need to:
declare @group1 int
declare @group2 int
etc...?

and if yes, how exactly do i do it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 10:53:26

[code]CREATE FUNCTION dbo.fnYak
(
@Param1 CHAR,
@Text VARCHAR(8000)
)
RETURNS @Result TABLE (UserID INT, Group1 INT, Group2 INT, Group3 INT)
AS

BEGIN
INSERT @Result
(
UserID,
Group1,
Group2,
Group3
)
select userid,
SUM(CASE WHEN groups.id = 6 THEN 1 ELSE 0 END),
SUM(CASE WHEN groups.id = 8 THEN 1 ELSE 0 END),
SUM(CASE WHEN groups.id = 9 THEN 1 ELSE 0 END)
from groups_products as groups
inner join users as u on u.userid = groups.userid
where u.userid = @userid
and condition1 = 1
and condition2 = 2
group by userid

return
end[/code]For this simple select, you can rewrite the query as an inline function.

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-01-31 : 02:51:17

Peso,

lovely reconstruction and thank you very much. Somehow i can't get @userid variable getgoing? I think it there should be @userid defined. or?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources