?
Solved

Scalar-Valued function not working

Posted on 2011-10-14
5
Medium Priority
?
351 Views
Last Modified: 2012-05-12
Have a scalar valued function that nots working, any  help would be grand

error message is Incorrect syntax near 'Inst_Type_Code'. and I want the value  of @StrInstTypeCode  returned
0
Comment
Question by:MrDavidThorn
5 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 36968714
Paste your function so that its easy to trace the error
0
 
LVL 10

Expert Comment

by:plummet
ID: 36968815
Yes, we need to see the function to be able to help
0
 

Author Comment

by:MrDavidThorn
ID: 36968878
sorry thought I had paste it, have worked on it a bit more and I get the error message

Msg 102, Level 15, State 1, Procedure psInst_ID_Type, Line 17
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure psInst_ID_Type, Line 22
Incorrect syntax near 'END'.
CREATE FUNCTION psInst_ID_Type
(
	@Inst_Type_Code Nvarchar(10)
)
RETURNS  nvarchar(1)
AS
BEGIN

declare @return varchar(1)

select @return = case @Inst_Type_Code 
		WHEN 'EQTY' THEN ='I'
		WHEN 'BOND' THEN ='I'
		WHEN 'FUTR' THEN 'A'
		END
	RETURN @return
END

Open in new window

0
 
LVL 10

Accepted Solution

by:
plummet earned 1000 total points
ID: 36968974
Try this:

CREATE FUNCTION psInst_ID_Type
(
	@Inst_Type_Code Nvarchar(10)
)
RETURNS  nvarchar(1)
AS
BEGIN

declare @return varchar(1)

select @return = case @Inst_Type_Code 
		WHEN 'EQTY' THEN  'I'
		WHEN 'BOND' THEN 'I'
		WHEN 'FUTR' THEN 'A'
		END
	RETURN @return
END 

Open in new window


No "=" signs in the case..when!
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 1000 total points
ID: 36968986
Its just a small error in case statement. You are using = for assingnig the value. Use the below query

CREATE FUNCTION psInst_ID_Type  
(  
        @Inst_Type_Code Nvarchar(10)  
)  
RETURNS  nvarchar(1)  
AS  
BEGIN  
 
declare @return varchar(1)  
 
select @return = case @Inst_Type_Code  
                WHEN 'EQTY' THEN 'I'  
                WHEN 'BOND' THEN 'I'  
                WHEN 'FUTR' THEN 'A'  
                END  
        RETURN @return  
END
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question