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