isnull in the table declaration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

isnull in the table declaration

Author

Topic

shobhaaradhya
Starting Member

37 Posts

Posted - 2012-10-19 : 06:23:48

Hi,

I am facing serious issue in one of the script which is causing problem in subscribing the db.

The function is as below.

create FUNCTION [dbo].[Fn_Get_Names]
(
@Data1 decimal(18,9),
@Data2 decimal(18,9),
@Data3 bigint
)
RETURNS @tmptbl TABLE
(
v_rec1 decimal(18,9),
v_rec2 decimal(18,9),
v_rec3 decimal(18,9),
v_rec4 AS (ISNULL(CONVERT(decimal(18,9),(v_rec2+v_rec3-v_rec1)),0))
)

AS

BEGIN

DECLARE @Debug bit
SET @debug = 0

DECLARE d_rec1 decimal(18,9)
DECLARE d_rec2 decimal(18,9)
DECLARE d_rec3 decimal(18,9)

----------------------------------------
code for some calculation here

----------------------------------------

INSERT INTO @tmptbl
(
v_rec1,
v_rec2,
v_rec3
)
VALUES
( d_rec1,
d_rec2,
d_rec3
)
RETURN
END

Below is the error details

The schema script '\\SERVER1\C$\Program Files (x86)\Microsoft SQL Server\MSSQL$DEVELOPMENT\ReplData\ftp\SERVER1$DEV_EMPLOYEE_PubEmp\20121019095745\Fn_Get_Currency_Values_2793.sch' could not be propagated to the subscriber.

The schema script '\\SERVER1\C$\Program Files (x86)\Microsoft SQL Server\MSSQL$DEVELOPMENT\ReplData\ftp\SERVER1$DEV_EMPLOYEE_PubEmp\20121019095745\Fn_Get_Names_2793.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
Extracted file 'Fn_Get_Names_2793.sch'
(Source: TESTSRV\TKT (Agent); Error number: 20151)
---------------------------------------------------------------------------------------------------------------
Line 1: Incorrect syntax near 'isnull'.

If I remove (ISNULL(CONVERT(decimal(18,9),(v_rec2+v_rec3-v_rec1)),0)) from the table declaration it works fine, else the subscription shows the above error.

Is there any workaround for this or any other way to rewrite this script?

Thanks in advance,

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 06:46:04

create FUNCTION [dbo].[Fn_Get_Names]
(
@Data1 decimal(18,9),
@Data2 decimal(18,9),
@Data3 bigint
)
RETURNS @tmptbl TABLE
(
v_rec1 decimal(18,9),
v_rec2 decimal(18,9),
v_rec3 decimal(18,9),
v_rec4 AS (ISNULL(CONVERT(decimal(18,9),(v_rec2+v_rec3-v_rec1)),0))
)

AS

BEGIN

DECLARE @Debug bit
SET @debug = 0

DECLARE @d_rec1 decimal(18,9)
DECLARE @d_rec2 decimal(18,9)
DECLARE @d_rec3 decimal(18,9)

SET @d_rec1 = @data1
SET @d_rec2 = @data2
SET @d_rec3 = @data3

----------------------------------------
code for some calculation here

----------------------------------------

INSERT INTO @tmptbl
(
v_rec1,
v_rec2,
v_rec3
)
VALUES
( @d_rec1,
@d_rec2,
@d_rec3
)
RETURN
END

--
Chandu

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2012-10-19 : 06:50:25

I'd adjust the definition of v_rec4 to have a specific datatype of it's own, and shove the calculation element of v_rec4 inside the function code and not into the fucntion declaration section.

it might also be worthwhile proving that the function can be created manually in the receiver database, in case there is an incompatibility issue at play.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources