I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns null value. details are given bellow RSS

• • Reply
  

  anuvasp

  Member

  5 Points

  9 Posts

  I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns nul...

  Apr 23, 2012 12:58 PM|LINK

  set ANSI_NULLS ON 
  set QUOTED_IDENTIFIER ON 
  GO 
  ALTER FUNCTION [dbo].[spamt] 
  (@sprank int=null,@dm int=null,@scheme nvarchar(50)) 
  RETURNS decimal 
  AS 
  BEGIN 
  declare @Return decimal 
  
  SELECT @Return = SUM(CRate) FROM DegDetails WHERE DCode<=@sprank AND NYr=@dm AND SID IN (SELECT DISTINCT SID FROM SchemeDetails WHERE Scheme='@scheme') 
  
  
  return @Return 
  end 
  
  this is my function 
  but when i execute it in sql like 
  
  select dbo.spamt(11 ,12 ,'ASP') 
  
  null value returns... 
  but when i execute bellow statement then it returns 12.45 
  select SUM(CRate)as CRate FROM DegDetails WHERE DCode<=11 AND NYr=12 AND SID IN (SELECT DISTINCT SID FROM SchemeDetails WHERE Scheme='ASP') 
  
  please solve this...

  sql Server 2005 sql server database visual studio sqLserver2005 a

• • Reply
  

  Ramesh T

  Contributor

  5171 Points

  833 Posts

  Re: I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns...

  Apr 23, 2012 01:05 PM|LINK

  Give it a try by removing the quotation (in the where clause) in the below sub SELECT query

  SELECT DISTINCT SID FROM SchemeDetails WHERE Scheme='@scheme'

  • Edited by Ramesh T on Apr 23, 2012 01:05 PM
  • Edited by Ramesh T on Apr 23, 2012 01:07 PM
  • Edited by Ramesh T on Apr 23, 2012 01:08 PM
  • Edited by Ramesh T on Apr 23, 2012 01:08 PM
• • Reply
  

  anuvasp

  Member

  5 Points

  9 Posts

  Re: I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns...

  Apr 23, 2012 01:21 PM|LINK

  but I need this distinct sid

  i can run the select quary without using function

  but when i use the quary in function there return null value.

• • Reply
  

  Ramesh T

  Contributor

  5171 Points

  833 Posts

  Re: I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns...

  Apr 23, 2012 01:24 PM|LINK

  Sorry about this.

  Chage the below (incorrect) sub query

  SELECT DISTINCT SID FROM SchemeDetails WHERE Scheme = '@scheme'

  to

  SELECT DISTINCT SID FROM SchemeDetails WHERE Scheme = @scheme

  and give it a try.
  
  

  • Edited by Ramesh T on Apr 23, 2012 01:24 PM
  • Edited by Ramesh T on Apr 23, 2012 01:25 PM
  • Edited by Ramesh T on Apr 23, 2012 01:26 PM
  • Edited by Ramesh T on Apr 23, 2012 01:27 PM
  • Edited by Ramesh T on Apr 23, 2012 01:28 PM
  • Marked as answer by anuvasp on Apr 23, 2012 01:30 PM
• • Reply
  

  anuvasp

  Member

  5 Points

  9 Posts

  Re: I want to use function in select quary like select a,b,sp(a,b) from tab1..but sp(a,b) returns...

  Apr 23, 2012 01:30 PM|LINK

  thank you so much..

  it really works...