Error while executing a function

Question

Post reply

Error while executing a function

swagh

SSC Journeyman

Points: 80
More actions
December 5, 2013 at 11:28 am

#283235

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Can you post the actual function and the sql that's calling it?

And then again, I might be wrong ...
David Webb

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

swagh SSC Journeyman Points: 80 More actions · Answer 4

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

CivNuclNuclear - Civil

DEFENCEDefence

ENERGYEnergy

FUNCTIONPAR

GENERICGeneric

MARINEMarine

NUCLEARNuclear - 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

swagh SSC Journeyman Points: 80 More actions · Answer 5

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'