Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error while executing a function Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 12:45 AM
Points: 4, Visits: 26
SQL Fellows,

I am getting one error below which says

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I am trying to execute this below. I have tried lot of possibilities but failed.



/****** Object: UserDefinedFunction [dbo].[Cat1AndCat2Sector_cat2] Script Date: 12/05/2013 15:13:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[Cat1AndCat2Sector_cat2]()
RETURNS TABLE
AS

returns


DECLARE @Code VARCHAR(MAX)
SET @Code = ''
select @Code = @Code + Name + ',' from Knowledge_domains where DOMAIN_TYPE ='MASTER'



Select @Code as Code,'All' as Name
UNION
Select code,Name from Knowledge_domains
where DOMAIN_TYPE ='MASTER'
order by name


Please can anyone help me out.


Post #1520257
Posted Thursday, December 5, 2013 11:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:31 PM
Points: 865, Visits: 7,452
Can you post the actual function and the sql that's calling it?



And then again, I might be wrong ...
David Webb
Post #1520263
Posted Thursday, December 5, 2013 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 3,908, Visits: 8,863
You have a declaration for a single statement table valued function but your function has multiple statements.
Maybe I can help you with the code but it would be easier with DDL for Knowledge_domains, sample data and expected results as explained on the article in my signature.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520270
Posted Thursday, December 5, 2013 12:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 3,908, Visits: 8,863
This should do the trick, however, I can't assure you that because I have nothing to test on.
Be aware that you can't use ORDER BY in a TVF because it would be useless.
If you're not receiving any parameters, you could as well use a view instead of a function (I'm not sure if there would be any difference on performance or something else).

CREATE FUNCTION [dbo].[Cat1AndCat2Sector_cat2]()
RETURNS TABLE
AS
RETURN
(
SELECT STUFF((SELECT ',' + CAST( Code AS varchar( 15))
FROM Knowledge_domains
WHERE DOMAIN_TYPE ='MASTER'
FOR XML PATH('')), 1, 1, '') as Code,
'All' as Name
UNION ALL
SELECT CAST( Code AS varchar(8000)),
Name
FROM Knowledge_domains
WHERE DOMAIN_TYPE ='MASTER'
)

For explanation on the concatenation, read the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520280
Posted Friday, December 6, 2013 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 12:45 AM
Points: 4, Visits: 26
Thnaks guys for your replies. I created a view but it still does not help. May be after i post this table structure, will help ypu guys to decode further.

here is the crude table knowledge_domains


CODE NAME

CIVIL Civil
CivNucl Nuclear - Civil
DEFENCE Defence
ENERGY Energy
FUNCTION PAR
GENERIC Generic
MARINE Marine
NUCLEAR Nuclear - Submarines
R&T R&T



The output which is expect is this. this output will be for SSRS report


CODE NAME

(Civil,Nuclear - Civil,Defence,Energy,PAR,Generic,Marine,Nuclear - Submarines,R&T,) All
CIVIL Civil
DEFENCE Defence
ENERGY Energy
GENERIC Generic
MARINE Marine
CivNucl Nuclear - Civil
NUCLEAR Nuclear - Submarines
FUNCTION PAR
R&T R&T
Post #1520473
Posted Friday, December 6, 2013 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 12:45 AM
Points: 4, Visits: 26
Thanks Luis for your solution. This seems to be too advanced for me. Anyways i will try and take information about STUFF and XML path definitions


This did the trick !

SELECT STUFF((SELECT ',' + CAST( Code AS varchar( 15))
FROM Knowledge_domains
WHERE DOMAIN_TYPE ='MASTER'
FOR XML PATH('')), 1, 1, '') as Code,
'All' as Name
UNION ALL
SELECT CAST( Code AS varchar(8000)),
Name
FROM Knowledge_domains
WHERE DOMAIN_TYPE ='MASTER'
Post #1520521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse