SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error while executing a function


Error while executing a function

Author
Message
swagh
swagh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 27
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.
David Webb-CDS
David Webb-CDS
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7262 Visits: 8587
Can you post the actual function and the sql that's calling it?



And then again, I might be wrong ...
David Webb
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72976 Visits: 20485
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
Luis Cazares
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72976 Visits: 20485
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
swagh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 27
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
swagh
swagh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 27
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'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search