package to package communication

Hello!

I'm having a problem trying to call a package from another package in PL/SQL.

The package I'm in is TIS_OWNER.portlet_itemised_calls, and the package I'm trying to call is TIS_OWNER.portlet_list_calls. So far I am able to call one of the procedures in TIS_OWNER.portlet_list_calls (openWindow) but I want to call the main Show method which is the default procedure which outputs to the screen, which I don't think I can do as it takes p_portlet_record wwpro_api_provider.portlet_runtime_record as a parameter.

The code is to be opened in a new window, so it looks like this at the moment (this works!):

<A href="http://links.10026.com/?link=JavaScript:openWindow('''||give_path()||'TIS_OWNER .PORTLET_LIST_CALLS.openWindow?line='||summRec.LN| |''');">'||summRec.LN||'</A>

summRec.LN is the dynamically created link that opens the window, and is also sent to the new window. "OpenWindow" is what I want to swap for "show", but I am guessing that I will need to call it differently due to the nature of te procedure.

I'm not sure how confusing this may sound, but if you can help at all I'd be very relieved!
Thanks,
HazelHi,
Oracle DOSENOT ALLOW calling another package from one.
If at all you want to use those routines, u have to either replicate it in your current package or else make it a NORMAL STORED PROCEDURE.

Regards

Overwrite query

Can I call web service to overwrite the query ? If yes, how can do it ?Only by republishing the report. Or you can make the query based on an
expression and pass the value in.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> Can I call web service to overwrite the query ? If yes, how can do it
> ?|||In reporting services books online, I find some class such as
DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
other example that teach us to overwrite the reports original query ?
Since I have some reports which the template and parameter are same but with
different select statment.
"Brian Welcker [MSFT]" wrote:
> Only by republishing the report. Or you can make the query based on an
> expression and pass the value in.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > Can I call web service to overwrite the query ? If yes, how can do it
> > ?
>
>|||When i try to overwrite the query, the following error appear:
The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. -->
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
Can anyone help me ?
"May Liu" wrote:
> In reporting services books online, I find some class such as
> DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> other example that teach us to overwrite the reports original query ?
> Since I have some reports which the template and parameter are same but with
> different select statment.
> "Brian Welcker [MSFT]" wrote:
> > Only by republishing the report. Or you can make the query based on an
> > expression and pass the value in.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > Can I call web service to overwrite the query ? If yes, how can do it
> > > ?
> >
> >
> >|||I have solve it but another problem come out. The error is as follow:
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The required field Extension is
missing from the input structure. -->
Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
The required field Extension is missing from the input structure. at
Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
name, String originalName, DataSourceDefinition definition) at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
item, String name) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
dataSource) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
dataSources) at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
My code is as follow:
dataSrcDef.ConnectString = "data source=TEST"
dataSrcDef.UserName = "user"
dataSrcDef.Password = "password"
QueryDef.CommandText = txtSQL.Text
dataSetDef.Query = QueryDef
dataSetDef.Name = "DS_DEVE"
rsDataSource(0) = New DataSource
rsDataSource(0).Item = dataSrcDef
rsDataSource(0).Name = "DS Deve"
RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
RptSvc.SetReportDataSources(reportPath, rsDataSource)
What happens to my code ? Can anyone help me ?
"May Liu" wrote:
> When i try to overwrite the query, the following error appear:
> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
> Can anyone help me ?
> "May Liu" wrote:
> > In reporting services books online, I find some class such as
> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> > other example that teach us to overwrite the reports original query ?
> > Since I have some reports which the template and parameter are same but with
> > different select statment.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> > > Only by republishing the report. Or you can make the query based on an
> > > expression and pass the value in.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > > Can I call web service to overwrite the query ? If yes, how can do it
> > > > ?
> > >
> > >
> > >|||The error reflects the fact that you need to set the DataSource.Extension to
"SQL". However, you are going down the wrong path - you can't set the query
this way, only the connection string. The query is not directly accessible
from the server.
I would go down the path of dynamically constructing the query in the
report.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
>I have solve it but another problem come out. The error is as follow:
> System.Web.Services.Protocols.SoapException:
> System.Web.Services.Protocols.SoapException: The required field Extension
> is
> missing from the input structure. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> The required field Extension is missing from the input structure. at
> Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> name, String originalName, DataSourceDefinition definition) at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> item, String name) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> dataSource) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> dataSources) at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack
> trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources)
> My code is as follow:
> dataSrcDef.ConnectString = "data source=TEST"
> dataSrcDef.UserName = "user"
> dataSrcDef.Password = "password"
> QueryDef.CommandText = txtSQL.Text
> dataSetDef.Query = QueryDef
> dataSetDef.Name = "DS_DEVE"
> rsDataSource(0) = New DataSource
> rsDataSource(0).Item = dataSrcDef
> rsDataSource(0).Name = "DS Deve"
> RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> RptSvc.SetReportDataSources(reportPath, rsDataSource)
> What happens to my code ? Can anyone help me ?
> "May Liu" wrote:
>> When i try to overwrite the query, the following error appear:
>> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
>> for
>> performing this operation. -->
>> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
>> The
>> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
>> performing this operation. at
>> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
>> report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
>> batchId, String report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources) -- End of inner exception stack
>> trace --
>> at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources)
>> Can anyone help me ?
>> "May Liu" wrote:
>> > In reporting services books online, I find some class such as
>> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
>> > any
>> > other example that teach us to overwrite the reports original query ?
>> > Since I have some reports which the template and parameter are same but
>> > with
>> > different select statment.
>> >
>> > "Brian Welcker [MSFT]" wrote:
>> >
>> > > Only by republishing the report. Or you can make the query based on
>> > > an
>> > > expression and pass the value in.
>> > >
>> > > --
>> > > Brian Welcker
>> > > Group Program Manager
>> > > SQL Server Reporting Services
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> > > rights.
>> > >
>> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
>> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
>> > > > Can I call web service to overwrite the query ? If yes, how can
>> > > > do it
>> > > > ?
>> > >
>> > >
>> > >|||Sorry, I don't understand what you mean. Does reporting service provide web
service that can allow me to change the query dynamically. I see there are
many web service such as DataSourceDfinition, DataSetDefinition or
QueryDefinition. Actually, I am quite confusing about them and their
relationship.
"Brian Welcker [MSFT]" wrote:
> The error reflects the fact that you need to set the DataSource.Extension to
> "SQL". However, you are going down the wrong path - you can't set the query
> this way, only the connection string. The query is not directly accessible
> from the server.
> I would go down the path of dynamically constructing the query in the
> report.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
> >I have solve it but another problem come out. The error is as follow:
> >
> > System.Web.Services.Protocols.SoapException:
> > System.Web.Services.Protocols.SoapException: The required field Extension
> > is
> > missing from the input structure. -->
> > Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> > The required field Extension is missing from the input structure. at
> > Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> > at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> > name, String originalName, DataSourceDefinition definition) at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> > item, String name) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> > dataSource) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> > dataSources) at
> > Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> > report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> > batchId, String report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources) -- End of inner exception stack
> > trace --
> > at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources)
> >
> > My code is as follow:
> > dataSrcDef.ConnectString = "data source=TEST"
> > dataSrcDef.UserName = "user"
> > dataSrcDef.Password = "password"
> >
> > QueryDef.CommandText = txtSQL.Text
> > dataSetDef.Query = QueryDef
> > dataSetDef.Name = "DS_DEVE"
> >
> > rsDataSource(0) = New DataSource
> > rsDataSource(0).Item = dataSrcDef
> > rsDataSource(0).Name = "DS Deve"
> >
> > RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> > RptSvc.SetReportDataSources(reportPath, rsDataSource)
> >
> > What happens to my code ? Can anyone help me ?
> >
> > "May Liu" wrote:
> >
> >> When i try to overwrite the query, the following error appear:
> >>
> >> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
> >> for
> >> performing this operation. -->
> >> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> >> The
> >> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> >> performing this operation. at
> >> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> >> report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> >> batchId, String report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources) -- End of inner exception stack
> >> trace --
> >> at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources)
> >>
> >> Can anyone help me ?
> >>
> >> "May Liu" wrote:
> >>
> >> > In reporting services books online, I find some class such as
> >> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
> >> > any
> >> > other example that teach us to overwrite the reports original query ?
> >> > Since I have some reports which the template and parameter are same but
> >> > with
> >> > different select statment.
> >> >
> >> > "Brian Welcker [MSFT]" wrote:
> >> >
> >> > > Only by republishing the report. Or you can make the query based on
> >> > > an
> >> > > expression and pass the value in.
> >> > >
> >> > > --
> >> > > Brian Welcker
> >> > > Group Program Manager
> >> > > SQL Server Reporting Services
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> > > rights.
> >> > >
> >> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> >> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> >> > > > Can I call web service to overwrite the query ? If yes, how can
> >> > > > do it
> >> > > > ?
> >> > >
> >> > >
> >> > >
>
>

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob