Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

scaler function error. 1

Status
Not open for further replies.

747576

Programmer
Jun 18, 2002
97
GB
Hi,
Can I use a declare table in a function. I get the error 'RETURN statements in scalar valued functions must include an argument.' with the code below. It shoulf return just an integer value.
Thanks

Code:
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'ID_NO')
	DROP FUNCTION ID_NO
GO
 
--USAGE: select * from dbo.ID_NO('ID50210','ID50245','ID50225','ID50255','ID50267','ID50289','ID50267','ID50290')
---------------------------------------------------
CREATE FUNCTION dbo.ID_NO (@from varchar(10), @to varchar(10), @from2 varchar(10), @to2 varchar(10), @from3 varchar(10), @to3 varchar(10), @from4 varchar(10), @to4 varchar(10)) 
RETURNS INT AS

BEGIN
RETURN

DECLARE @TEMP_ID TABLE (

            [ID_FROM] [varchar] (20),
			[ID_TO] [varchar] (20)
			)

INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES ('@from','@to')
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES ('@from2','@to2')
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES ('@from3','@to3')
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES ('@from4','@to4')

;with IDS (SID, EID) as (
  select (select cast(right(min(ID_FROM), 5) as int) from @TEMP_ID),
   (select cast(right(max(ID_TO), 5) as int) from @TEMP_ID)
  union all
  select SID+1, EID from IDS where SID<=EID
) select count(*) NUM_OF_UNIQUE_IDS from IDS
   where exists (select ID_FROM from @TEMP_ID
    where SID between cast(right(ID_FROM, 5) as int) and cast(right(ID_TO, 5) as int))

END
GO
GRANT ALL ON dbo.ID_NO TO PUBLIC
 
The syntax is wrong.
Code:
...
 RETURNS
  @TEMP_ID table (
            [ID_FROM] [varchar] (20),
            [ID_TO] [varchar] (20)
            ) as begin
...
return
end
 
Sorry, I misunderstood you.
Code:
CREATE FUNCTION dbo.ID_NO (@from varchar(10), @to varchar(10), @from2 varchar(10), @to2 varchar(10), @from3 varchar(10), @to3 varchar(10), @from4 varchar(10), @to4 varchar(10))
RETURNS INT AS

BEGIN
declare @NOOFIDS int
DECLARE @TEMP_ID TABLE (

            [ID_FROM] [varchar] (20),
            [ID_TO] [varchar] (20)
            )

INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from,@to)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from2,@to2)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from3,@to3)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from4,@to4)

;with IDS (SID, EID) as (
  select (select cast(right(min(ID_FROM), 5) as int) from @TEMP_ID),
   (select cast(right(max(ID_TO), 5) as int) from @TEMP_ID)
  union all
  select SID+1, EID from IDS where SID<=EID
) select @NOOFIDS=count(*) from IDS
   where exists (select ID_FROM from @TEMP_ID
    where SID between cast(right(ID_FROM, 5) as int) and cast(right(ID_TO, 5) as int))
return @NOOFIDS
END
GO
 
Hi again,
The function is working fine, but if I pass a value to the function which is over a 100 more than the previous ID ranges, then I get the following error:

'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

Code:
select [dbo].AFN_SAMPLE_NO('ID50210','ID50245','ID50225','ID50255','ID50267','ID50289',[COLOR=red]'ID50367','ID50390'[/color])


Code:
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'AFN_SAMPLE_NO')
	DROP FUNCTION AFN_SAMPLE_NO
GO
 
--USAGE: select [dbo].AFN_SAMPLE_NO('ID50210','ID50245','ID50225','ID50255','ID50267','ID50289','ID50367','ID50390')
---------------------------------------------------
CREATE FUNCTION dbo.AFN_SAMPLE_NO (@from varchar(10), @to varchar(10), @from2 varchar(10), @to2 varchar(10), @from3 varchar(10), @to3 varchar(10), @from4 varchar(10), @to4 varchar(10))
RETURNS INT AS

BEGIN
declare @NOOFIDS int
DECLARE @TEMP_ID TABLE (

            [ID_FROM] [varchar] (20),
            [ID_TO] [varchar] (20)
            )

INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from,@to)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from2,@to2)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from3,@to3)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from4,@to4)

;with IDS (SID, EID) as (
  select (select cast(right(min(ID_FROM), 5) as int) from @TEMP_ID),
   (select cast(right(max(ID_TO), 5) as int) from @TEMP_ID)
  union all
  select SID+1, EID from IDS where SID<=EID
) select @NOOFIDS=count(*) from IDS
   where exists (select ID_FROM from @TEMP_ID
    where SID between cast(right(ID_FROM, 5) as int) and cast(right(ID_TO, 5) as int))
return @NOOFIDS
END
GO
GRANT ALL ON dbo.AFN_SAMPLE_NO TO PUBLIC
 
Code:
...
 where SID between cast(right(ID_FROM, 5) as int) and cast(right(ID_TO, 5) as int))[b] option (MAXRECURSION 0)[/b]
 
The following function works great:

Code:
IF EXISTS (SELECT * 
       FROM   sysobjects 
       WHERE  name = N'AFN_SAMPLE_NO')
    DROP FUNCTION AFN_SAMPLE_NO
GO
 
--USAGE: select [dbo].AFN_SAMPLE_NO('ID50210','ID50210','ID50210','ID50210','ID50210','ID50210','','')
---------------------------------------------------
CREATE FUNCTION dbo.AFN_SAMPLE_NO (@from varchar(10), @to varchar(10), @from2 varchar(10), @to2 varchar(10), @from3 varchar(10), @to3 varchar(10), @from4 varchar(10), @to4 varchar(10))
RETURNS INT AS

BEGIN
declare @NOOFIDS int
DECLARE @TEMP_ID TABLE (

            [ID_FROM] [varchar] (20),
            [ID_TO] [varchar] (20)
            )

INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from,@to)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from2,@to2)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from3,@to3)
INSERT INTO @TEMP_ID(ID_FROM, ID_TO) VALUES (@from4,@to4)

;with IDS (SID, EID) as (
  select (select cast(right(min(ID_FROM), 5) as int) from @TEMP_ID),
   (select cast(right(max(ID_TO), 5) as int) from @TEMP_ID)
  union all
  select SID+1, EID from IDS where SID<=EID
) select @NOOFIDS=count(*) from IDS
   where exists (select ID_FROM from @TEMP_ID
    where SID between cast(right(ID_FROM, 5) as int) and cast(right(ID_TO, 5) as int)) option (MAXRECURSION 0)
return @NOOFIDS
END
GO
GRANT ALL ON dbo.AFN_SAMPLE_NO TO PUBLIC

but for example if there are no values for the last 2 arguments in the function it returns too many values,

Code:
select [dbo].AFN_SAMPLE_NO('ID50210','ID50210','ID50210','ID50210','ID50210','ID50210','','')

what can i substitute in the null arguments to get the correct number od id's

thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top