As an example, I have a scalar function called TRIM that takes a VARCHAR parameter, does a LTRIM(TRIM(VARCHAR)), and returns the result.
How can I call this function from java using JDBC? I have only had luck calling basic stored procedures, but I need to call functions as well.
Thanks, Ken
I want to add four fields of a table and place the toatal in a new field.Also I wanna have the average of the fields.
For e.g
I have created a marksheet having four subjects.Now I wanna add the subjects and find the average of the subjects and place them in two different fields in the same table along with the respective names in the table.Pls help.
Thanks in advance.
Hello ,
I get this error (connection closed) in web applications, whenever SQL Server database Service is restarted. When I run the application again, the error disappears. Granted that there are lots of users and Database gets restarted every night is not helping.
Is there any setting in the JDBC driver properties, where I can tell it to open a closed connection (like a retry count), rather than fail the first time and work the second time.
We use
1. SQL SERVER 2005 SP2,
2. Websphere Protal Server 5.1
3. SQL SERVER 2005 JDBC driver 1.1(com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource.)
I am wondering if connection pool driver is causing this problem. I do not care about connection pooling, but Websphere doen not seem to like any other JDBC Driver. I don't mind switching to another driver as the last option.
Error is :
[11/27/07 17:01:59:331 MST] 7202440d WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getCatalog(Unknown Source)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.getCatalog(WSRdbManagedConnectionImpl.java:676)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.introspectSelf(WSRdbManagedConnectionImpl.java:808)
at com.ibm.ws.rsadapter.FFDCLogger.introspect(FFDCLogger.java:169)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.introspectSelf(WSJdbcConnection.java:1253)
at com.ibm.ws.ffdc.IntrospectionLevelMember.getNextMembers(IntrospectionLevelMember.java(Compiled Code))
at com.ibm.ws.ffdc.IntrospectionLevel.getNextLevel(IntrospectionLevel.java:181)
at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:67)
at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:51)
at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWrite(IncidentStreamImpl.java:396)
at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWriteLine(IncidentStreamImpl.java:632)
at com.ibm.ws.ffdc.DiagnosticEngine.dumpObjectAndStack(DiagnosticEngine.java:301)
at com.ibm.ws.ffdc.DiagnosticEngine.processIncident(DiagnosticEngine.java:147)
at com.ibm.ws.ffdc.FFDCFilter.filterEngine(FFDCFilter.java:428)
.......lots of lines
Regards
Bobba
We are using Microsoft jdbc driver 4.1 connecting to SQL 2012, which has a linked server to another SQL 2012 server.Will linked server calls work with kerberos authentication using Microsoft jdbc driver 4.1? connection string looks like this:
jdbc:sqlserver://SQL01;database= product_db; integrated Security= true;authenticationScheme=JavaKerberos..We have the linked server connection configured to use "Be made using the login's current security context"
Date      8/3/2015 4:19:56 PM
Log      SQL Server (Current - 8/3/2015 3:49:00 PM)
Source      Logon
Message
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.196.21.4]
I build a UDF scalar function like this:
CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
[Code] ....
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC
The value are:
1695
2211
41
10698
11697
13696
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
View 4 Replies View RelatedWe are seeing a regression bug with the Microsoft JDBC driver 1.2 CTP.
Using this driver, we don't seem to be able to call stored procedures which return a result set, if those stored procedures use temporary tables internally.
The 1.2 CTP driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver, suceeds in both cases.
Here is a test case which demonstrates the problem (with IP addresses and logins omitted). The prDummy stored procedure being called is quite simple, and I've copied it below:
Code Snippet
public class MicrosoftJDBCDriverCallingStoredProceduresTest extends TestCase {
// CREATE PROCEDURE [dbo].[prDummy]
// AS
//
// CREATE TABLE #MyTempTable (
// someid BIGINT NOT NULL PRIMARY KEY,
// userid BIGINT,
// )
//
// SELECT 1 as TEST2, 2 as TEST2
// GO
public void testStoredProcedureViaDirectJDBC() {
Connection conn = null;
String driverInfo = "<unknown>";
String dbInfo = "<unknown>";
try {
// Set up driver & DB login...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://xxx.xxx.xxx.xxx:1433";
Properties dbProps = new Properties();
dbProps.put("databaseName", "xxxxxx");
dbProps.put("user", "xxxxxx");
dbProps.put("password", "xxxxxx");
// Get a connection...
conn = DriverManager.getConnection(connectionUrl, dbProps);
driverInfo = conn.getMetaData().getDriverName() + " v" + conn.getMetaData().getDriverVersion();
dbInfo = conn.getMetaData().getDatabaseProductName() + " v" + conn.getMetaData().getDatabaseProductVersion();
// Perform the test...
CallableStatement cs = conn.prepareCall("{CALL prDummy()}");
cs.executeQuery();
// If the previous line executes okay, the test is passed...
System.out.println("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" calls the stored procedure successfully.");
}
catch (Exception e) {
// Fail the unit test...
fail("Accessing "" + dbInfo + "" with driver "" + driverInfo + "" fails to call the stored procedure: " + e.getMessage());
}
finally {
// Close the connection...
try { if (conn != null) conn.close(); } catch (Exception ignore) { }
}
}
}
The output of this test under both drivers and accessing both databases is as follows:
Code Snippet
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.1.1501.101" calls the stored procedure successfully.
Accessing "Microsoft SQL Server v8.00.2039" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
Accessing "Microsoft SQL Server v9.00.3042" with driver "Microsoft SQL Server 2005 JDBC Driver v1.2.2323.101" fails to call the stored procedure: The statement did not return a result set.
I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:
;WITH cte1 AS (
SELECT b.FirstName,
(SELECT fn_similarity('Test', b.FirstName)) AS [Value],
b.LastName
FROM [AdventureWorks2012].[Person].[Person] b
)
SELECT *
FROM cte1
WHERE [Value] > 50.00
ORDER BY [Value] DESC
Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.
At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?
Hi, I have the follow function:
CREATE FUNCTION [dbo].[ToTime]
(
@intHora int, --A valid hour
@intMin int -- A valid minute
)
RETURNS smalldatetime
AS
BEGIN
declare @strTime smalldatetime
declare @errorvar int
select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar)
return @strTime;
END
the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error.
I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example
if @intHora < 0 or @intHora >23
begin
set @intHora = 0
end
if @intMin <0 or @intMin>59
begin
set @intMin = 0
end
please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.
Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases.
The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
create table ICS_ConsraintTest (PrimaryIdentifier varchar(64),
ServiceProvider varchar(64),
SecondaryID varchar(64),
SecondaryServiceProvider varchar(64),
Description varchar(64),
PanoramaObject varchar(64),
Operation int,
ScalingFactor int,
DisplayAs float,
FeedType int,
InputValue int,
OutputValue float,
salary float,
birthdate datetime,
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0
and len(ltrim([ServiceProvider])) > 0
and len(ltrim([SecondaryID])) > 0
and len(ltrim([PanoramaObject])) > 0
and [salary] <> 0.0)))
Stored procedure id defined as follows:
CREATE PROCEDURE SP_ICS_TestWithConstraints1(@PrimaryIdentifier varchar(64),
@PrimaryServiceProvider varchar(64),
@ServiceProvider varchar(64),
@SecondaryID varchar(64),
@SecondaryServiceProvider varchar(64),
@Description varchar(64),
@PanoramaObject varchar(64),
@Operation int,
@ScalingFactor int,
@DisplayAs float,
@FeedType int,
@InputValue int,
@OutputValue float,
@salary float,
@birthdate datetime) AS
BEGIN
BEGIN TRANSACTION
BEGIN
/* Insert */
INSERT INTO ICS_ConsraintTest ( PrimaryIdentifier,
ServiceProvider,
SecondaryID,
SecondaryServiceProvider,
Description,
PanoramaObject,
Operation,
ScalingFactor,
DisplayAs,
FeedType,
InputValue,
OutputValue,
salary,
birthdate)
VALUES ( @PrimaryIdentifier,
@ServiceProvider,
app_name(),
@SecondaryServiceProvider,
@Description,
@PanoramaObject,
@Operation,
@ScalingFactor,
@DisplayAs,
@FeedType,
@InputValue,
@OutputValue,
@salary,
@birthdate)
END
COMMIT TRANSACTION
END
Check out the app_name() is passed as the SecondaryID which causes the failure.
I have troubles with this scalar-valued UDF:
I get the error:
Select statements included within a function cannot return data to a client.
Is this a proper way to include a CTE in a function?
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)
[code].....
I've a scalar function which is killing my performance. I've been used the SQL profiler and also DMVs to catch execution information. I'd like to store the value received by this function and also the time that it happened, but I can't think in a way to do it.
View 5 Replies View RelatedSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DetailedView
AS
BEGIN
Declare @fieldname varchar(10)
Declare @stmt varchar(4000)
Declare Fields Cursor For Select Amounttype From Amounttypes
Set @stmt = 'Select pono, myid, billtype'
Open Fields
Fetch Next From Fields Into @fieldname
While @@Fetch_Status = 0 Begin
Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname
Fetch Next From Fields Into @fieldname
End
Close Fields
Deallocate Fields
Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '
return Exec(@stmt)
END
Hi,
I have an assembly that contains the following function:
Public Class Lookup
<SqlFunction()> _
Public Shared Function MyTest() As Integer
Return System.Data.SqlTypes.SqlInt64.Null
End Function
End Class
Then in SSMS:
CREATE ASSEMBLY IRS_MyTest
FROM '\machine empmyAssembly.dll'
GO
CREATE FUNCTION dbo.MyTest() RETURNS INT
AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest
GO
when I run:
SELECT dbo.MyTest()
the following is returned:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlInt64.get_Value()
at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)
at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()
Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.
Thanks,
Jan.
I wrote a Scalar UDF in SQL2005 that returns an integer. I want to be able to display this integer in a ASP.Net 2.0 web page. I typically use a DAL for all data so I added an ObjectDataSource as a Qeury that contains only the UDF. How do I easily display the value in a Label Control or?
I have tried to use a Repeater with a label, a Formview with a Label, all to no avail. Any suggestions?
I have this code:
Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)
and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:
select dbo.fnGet_Rec_Count('ACA_RSF') as cnt
here is the code:
alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
-- Declare the return variable here
DECLARE @count bigint
-- Add the T-SQL statements to compute the return value here
select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')
-- Return the result of the function
RETURN @count
END
GO
I get zero regardless of where @count is declared as in or bigint.
I have 2 Tables as below,
1) Purchase_Invoice
a. PurchaseInvoiceID
b. SupplierName
c. BillNo
d. BillDate
2) Purchase_Invoice_Items
a. PurchaseInvoiceItemID
b. PurhcaseInvoiceID (FK to Purchase_Invoice Table)
c. ItemName
d. Quantity
e. Rate
Now I want to select all the records of Purhcase_Invoice table exactly once with one column at last containing comma separated Item name of particular PurhcaseInvoiceID as below
PurchaseInvoiceID | SupplierName | BillNo | BillDate | Items (Comma Separated Items)
Currently I am using Scalar Function which takes PurchaseInvoiceID as Argument and Returns Comma Separated Itemnames€¦
It works well but when number of records are large than performance is very poor.
Is there another way of doing same thing? By Join or By some System Function?
Nilesh
Hi, I'm having trouble with this multi-statement table-valued function:
ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@dateStart DATETIME,
@dateEnd DATETIME
)
RETURNS @arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
INSERT INTO @arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @arTemp.Insurer AND
Pims.NABP = @arTemp.NABP AND
Pims.NumRx = @arTemp.NumRx
);
INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO
I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".
I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.
My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?
The two versions of the SVF (they return the rowcount in this simplified example):
CREATE FUNCTION [dbo].[FastFunction]
(
@inDetaljerText varchar(MAX)
)
RETURNS int
[Code] ....
The two versions of the SP
CREATE PROCEDURE [dbo].[FastProcedure]
AS
BEGIN
SET NOCOUNT ON;
select
dbo.FastFunction(al.Detaljer)
[Code] ....
Hi There,
I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..
'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.
I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.
Thanks a lot!!
Dear all,
Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.
I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:
select dbo.CLRHTMLString('abc')
The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).
To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:
EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.
Note: The 'webuser' database role has Execute permission on the Assembly.
I have also tested this from my actual web page, with the following results:
(1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly.
(2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.
Further test results:
(3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.
(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.
Thankful for any advice on this matter.
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies View RelatedI suppose that this could also be called: Can you retrieve a parameterized view with JDBC? Either accomplishes the same basic task.
I have looked in the documentation, and there really isn't much that talks about JDBC and functions - just stored procedures - which do not returned a CURSOR type according to Microsoft's documentation.
So far, I can retrieve scaler type data in both procedures and functions, and retrieve data sets through executeQuery with a provided sql query. How to return a resultset in Java using procedure or function has still eluded me. (<sarcasm intended> And I thought Java could do just about anything. Who would've thought...)
Any ideas would be greatly appreciated. Resources, books, etc. Google hasn't offered much help with SQL Server and JDBC.
Thanks,
Ken
can i make a function call from stored procedure
View 3 Replies View RelatedDoes anybody knows how to call a function from one VB source file to another VB source file??
I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??
Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
1 ALTER PROCEDURE [dbo].[GetPortalSettings]2 (3 @PartyId uniqueidentifier,45 AS6 SET NOCOUNT ON7 CREATE TABLE #Groups8 (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
Hi,
Does anyone know if/how SQL server can call a function in a C++ library?
Cheers,
Xiaobing
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,
I have a procedure which has query
like Query 1.
Query 1
Select Clinetid
from clinet
inner join {
select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c
on c.Centerid = client.Centerid
Query 2
declare @Center table ( centerid int)
insert into @Center
select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)
Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid
I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies View Related