Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Disadvantages Using Functions


any disadvanges using user defined functions calling from stored procedures??


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Autonumber Disadvantages
hi,
i am thinking of using autonumber in my table which is more likely to grow over time. but i am also concerned that what if the records get too much? is there is any other fields to use instead of autonumber? is uniqueidentifier a better option? i have a bit of discussion with my colleague about this matter.

any help is appreciated

View Replies !   View Related
Advatnages And DisAdvantages Of
Running SQL Server as a DOMAIN user account.

View Replies !   View Related
DisAdvantages Of Cursors.
can any one help me out to know the disadvantages of the cursor

View Replies !   View Related
Disadvantages Of Using Uniqueidentifier (Tell Your Tale)
Dear SQL...

I'm about to use uniqueidentifier and NEWID() for my new DB,

The DB may grow wild and I want to super safely create connections between tables,
so it would not just rely on numbers (that can B changed) as used with IDENTITY...


If U had some BAD experience with this or if U have any tips I can use please let me know

+

Should I take any special steps when using index on it ?

View Replies !   View Related
Advantages/Disadvantages To Using More Than One Database
I have a content site where everything is currently in one SQL ServerDB. As I add features to the site, for example message boards andblogging, does it make sense to put those features in a separatedatabase? What would I lose and gain in doing so? Thanks so much.Erik

View Replies !   View Related
Advantages And Disadvantages Of The Replication Ty
hi,
Can any body help me in knowing what are the advantages and disadvantages of all the three replication types?

View Replies !   View Related
SSIS Advantages And Disadvantages
Greetings SSIS developers,



I would like to start a discussion on the advantages and disadvantages of SSIS according to your own experiences.



Your participation would be much appreciated.



cheers,




View Replies !   View Related
Advantages&&Disadvantages Logical Drives
Are they're any real disadvantages or advantages in having 1 massive disk partioned to create 2 logical drives (not including the C drive) and separating the SQL Database File & Transaction Log so that it doesnt reside on the same logical drive?

After all, it is still a single disk.

thanks.

View Replies !   View Related
Advantages/Disadvantages B/w Being Consultant Vs Employee
Hi, I would appreicate your opinion/ feed back about Being consultant vs Being employee.

What are the advantages/disadvantages.
Does consultant makes more money than an employee and why so


Thanks for your response
Ali

View Replies !   View Related
Disadvantages Of Running 80 Compatibility On 2005?
I have a hosted solution where the web host provider tells me I'm running on a 2005 platform, but when I check the DB options, it shows compatibility level 80. The provider tells me I'm not losing any performance by running in the lower compatibility mode. Is this truly the case? What else am I losing out on in terms of features or capabilities?

View Replies !   View Related
Disadvantages Of The Implementation Of Explicit Subtypes On MS SQL 7 And Oracle8i/9i
Could some database expert post some information or link about that?thanksJanusz

View Replies !   View Related
Olap Mining - Advantages, Disadvantages And Problems
Dear Sirs and Madams,

it's quite hard to find informations about OLAP mining I think. SoI have some questions to you:

What advantages do I have by using Olap Mining instead of "normal" mining (relational databases)?
Is it just faster or are there other advantages?

What are the disadvantages? The data can be overaggregated (no detailled results), can not? Are there other disadvantages?

What problem do I have to face?

Whats going on with empty cells?

Thank you very much in advance and have a nice weekend.

Thorsten

View Replies !   View Related
SQL Server 2005: CLR Functions Vs SQL Functions
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID

View Replies !   View Related
SPs, Functions And
Guys I need help with sql server 2005 syntax
My goal is up update a table called UserStats.
I have numerous functions in SQL that return Scalars (one for each statistics I want to use)
How do I then use a stored proceedure to return a table of the values for use on my site?

View Replies !   View Related
Functions?
WIthin SQL Server 2005, there are functions.  This feature is new to me and I haven't found anyone that has written their own fucntions?  I'm wondering if functions are written the same as stored procedures, and can a function be called from a stored procedure or even from within a query.
 

View Replies !   View Related
Using Own Functions
Hello,how I can use a function of my own within a select statement. I triedcreate function funny() returns intas beginreturn( 2 )end goand then "select funny()" but 'funny' is not a recognized function name.How can I solve this?thanks and regardsMark

View Replies !   View Related
Functions
Hi,,I'm having a problem with calling a function from an activex scriptwithin a data transformation. the function takes 6 inputs and returnsa single output. My problem is that after trying all of the stuff onBOL I still can't get it to work. It's on the same database and I'mrunning sql 2000.when I try to call it I get an error message saying "object requiredfunctionname" If I put dbo in front of it I get "object required dbo".Can anyone shed any light on how i call this function and assign theoutput value returned to a variable name.thanks.

View Replies !   View Related
Functions (is This Possible)
Hiya folks,
Posted a thread (http://www.dbforums.com/showthread.php?threadid=988002). Could anybody please tell if this is possible>

Thanks in advance

View Replies !   View Related
First/Last Functions In SQL?
hello Im having a difficult time translating this query from Access to SQL because it uses the First/Last functions.

I have a 'Projects' Table and a 'Project_Comments' table, each has a 'Project_ID' field which links the 2 together. What I need to do is retrieve a Project List from the Projects Table and also the first Comment of each project based on the Commend_date field in the Project_Comments table. This is the MS ACCESS query:


SELECT Projects.Project_Number, Projects.Project_Name, First(Project Comments.Comment_Date), First(Project_Comments.Notes)
FROM Projects Left Join Projec_Comments ON
Projects.Project_Number = Project_Comments.Project_Number
GROUP BY Projects.Project_Number, Projects.Project_Name


Now I can use Min() for the Date instead of First, however I dont know what to do with the Notes field. Any help on how to get this over to sql would be greatly appreciated!

View Replies !   View Related
SQL Functions
Iam trying to convert a date string to date format.....in access I could just use CDate, but SQL apparently does not allow this.
Any help appreciated
Thanks

View Replies !   View Related
Functions Help
Hi,

I have created a function that returns a comma seperated list of product id's from a table. I need to call this function from a stored procedure to help filter my product results, something like the following:

SET @SQL = 'SELECT dbo.Products.ProductID FROM dbo.Products WHERE dbo.Products.ProductID IN (' + dbo.GetModels('dbo.Products.ProductID', '') + '))'

The problem I am having when executing the above is:

"Conversion failed when converting the varchar value 'dbo.Products.ProductID' to data type int."

Can anyone shed some light on how I can call the function, feeding through the product ID from the row of the select statement I am trying to execute (if this makes sense).

Any help would be great.

Matt

View Replies !   View Related
Are There MIN(A,B) Or MAX(A,B) Functions In SQL?
Just wondering, since I came across a case where I have to select the MIN of 2 dates, or MAX of 2 dates

say I want

min_date = MIN(dateA, dateB)

max_date = MAX(dateA, dateB)



I can probably do it with CASE statement, but that seemsa bit clumsy

like min_date = CASE WHEN dateA < dateB THEN dateA ELSE dateB END



Thanks

View Replies !   View Related
Functions In Functions
Hi,

I have to calculate data in function with "EXEC".During runtime Iget the Error:

"Only functions and extended stored procedures can be executed from within a function."

I woulduse aStored Procedure, but the function is to be called from a view. I don't understand, why that should not be possible. Is there any way to shut that message down or to work around?
btw: Storing all the datain a table, would mean a lot of work,I rather not like to do. ;-)

Thx for any help
Blubb10



View Replies !   View Related
SQL Functions
Hello,

I've created a function that performs modulo. I understand that SQL server 2000 / 2005 uses % for modulo, but we have an application that was written for Oracle. Oracle has a mod(dividend, divisor) function.

As to not rewite the queries, I would like to implement the function below:

the function executes properly but I must prefix it with the dbo schema.

Net: I can execute --- select dbo.mod(9,2) and it returns a 1 just like it should.

but I can not execute --- select mod(9,2) I receive the error "'mod' is not a recognized function name." on SQL 2000 and 2005.


If I can execute select mod(9,2) then I won't need to re-write any queries.

Also, on SQL 2005, I have tried to adjust the default shema, and the execute as clause, but neither helped my cause.

I'm going to try building the function in the CLR, but I think I will be faced with the same problem.

Can someone point me in the right direction?

Thanks

Tom



create function mod
(
@dividend int,
@divisor int
)
RETURNS int
as
begin
declare @mod int
select @mod = @dividend % @divisor
return @mod
end




View Replies !   View Related
SQL CE DLL Functions
Can someone explain what the different SQL CE DLL's functions are? I can issue a successful merge replication and these seem to be the DLL's loaded just after it completes.


RSSWM.exe base address: 2C000000
=========================
sqlceoledb30.dll 00CB0000 35000 A
sqlceca30.dll 00CF0000 75000 A
sqlceqp30.dll 00D70000 DD000 A
sqlcese30.dll 00E50000 6A000 A
sqlceer30en.dll 00EC0000 24000 A
itcnetreg.dll 00EF0000 15000 A
itcswinsock.dll 00F10000 D000 A
itc50.dll 00F30000 27000 A
sqlceme30.dll 00F70000 10000 A
rsshelper.dll 00F80000 6000 A
tcpconnectiona.dll 00F90000 10000 A
edbgtl.dll 00FA0000 14000 A
itceventlog.dll 01110000 B000 A
rsaenh.dll 01350000 2B000 RO, H, S, C, RAM from ROM
iq_lapi_c_wrapper.dll 01B80000 27000 RO, XIP
ssapi.dll 01BB0000 17000 RO, XIP
mscoree2_0.dll 01D90000 C3000 RO, H, S, XIP

After some application activity, I attempt a merge replication again and it fails with a "DLL could not be loaded" yadda, yadda message. These seem to be the loaded DLL's at this point.

RSSWM.exe base address: 2C000000
=========================
sqlceqp30.dll 00D70000 DD000 A
sqlcese30.dll 00E50000 6A000 A
sqlceer30en.dll 00EC0000 24000 A
itcnetreg.dll 00EF0000 15000 A
itcswinsock.dll 00F10000 D000 A
itcadcdevmgmt.dll 00F20000 9000 A
itc50.dll 00F30000 27000 A
sqlceme30.dll 00F70000 10000 A
rsshelper.dll 00F80000 6000 A
tcpconnectiona.dll 00F90000 10000 A
edbgtl.dll 00FA0000 14000 A
itceventlog.dll 01110000 B000 A
rsaenh.dll 01350000 2B000 RO, H, S, C, RAM from ROM
iq_lapi_c_wrapper.dll 01B80000 27000 RO, XIP
ssapi.dll 01BB0000 17000 RO, XIP
mscoree2_0.dll 01D90000 C3000 RO, H, S, XIP

I'm at a loss as to why SQL CE cannot load unless I am out of my 32MB of process space for some reason.

(App on an Intermec 751 with WM5 and 128MB RAM)

TIA for any help

View Replies !   View Related
FUNCTIONS
I just installed SSRS 2005 and I have experience with SQL.



How come this function does not work?



SELECT SUBSTRING(YEAR_MONTH, 1, 2) AS Expr1
FROM table1



I get a message which states that this command is not supported by the provider?



It works fine with other SQL tools like winsql?



thanks

View Replies !   View Related
Aggregate Functions
I have a table that is used for employee evaluations.  There are six questions that are scored either 1, 2, 3, 4, or 5.  I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question).  I know I can do  "SELECT  COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT  COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on.  But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"?  If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.

View Replies !   View Related
Need Help--implementing Functions In C#???
Hi y'all I think i have some problem in here perhaps you guys can help.I have this code: 1 public void AddQstrWhere(ref string TmpStr,string Parameter)
2 {
3 if(TmpStr=="")
4 {
5 TmpStr +="WHERE"+Parameter;
6 }
7 else
8 {
9 TmpStr +="AND"+Parameter;
10 }
11 }
12
13
14 public string querySlsPerson;
15 //public string queryLastSls;
16 public ArrayList CLSGetSalesman(string szSalesmanID)
17 {
18
19
20 // TODO: Add CLSReportManager.CLSGetSalesman implementation
21 BOSTx Tex = GetTx(CLSReportConstants.TrnMasterID.FQN2,"QueryRptSales");
22 try
23 {
24 string querySalesPerson = "SELECT dbo.BOS_GL_Workplace.WpszState, BOS_PI_Employee_1.szName, dbo.BOS_PI_Employee.szName AS Expr1, dbo.BOS_PI_Employee.szEmployeeId, "+
25 " dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription "+
26 " FROM dbo.BOS_PI_Employee LEFT OUTER JOIN "+
27 " dbo.BOS_GL_Workplace ON dbo.BOS_PI_Employee.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN "+
28 " dbo.BOS_PI_Division ON dbo.BOS_PI_Employee.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN "+
29 " dbo.BOS_PI_Team ON dbo.BOS_PI_Employee.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN "+
30 " dbo.BOS_PI_Employee BOS_PI_Employee_1 ON dbo.BOS_PI_Employee.szSupervisorId = BOS_PI_Employee_1.szEmployeeId ";
31 //"WHERE dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
32 //" WHERE BOS_GL_Workplace.WpszState = '" + szProvince + "' AND BOS_PI_Employee.szName = '" + szsalesPrsn + "' AND BOS_PI_Employee_1.szName = '" + szSupervisor + "'";
33
34 string wheretext = " dbo.BOS_PI_Employee.szEmployeeId = '"+szSalesmanID+"'";
35 string queryLastSls;
36 //AddQstrWhere(szSalesmanID,wheretext);
37 if(szSalesmanID==string.Empty)
38 {
39 queryLastSls = querySalesPerson;
40 }
41 else
42 {
43 //string querySlsPerson;
44 AddQstrWhere(ref queryLastSls,wheretext);
45
46 queryLastSls = querySalesPerson + querySlsPerson;
47 }
48
49 OleDbCommand comd = Tex.GetTextCommand();
50 comd.CommandText = queryLastSls;
51
52 DataTable slTable = new DataTable();
53 OleDbDataAdapter adapt = new OleDbDataAdapter(comd);
54 adapt.Fill(slTable);
55
56
57 ArrayList DataSalesArr = new ArrayList();
58 foreach(DataRow row in slTable.Rows)
59 {
60 CSDatasales sls = new CSDatasales();
61 sls.sz_province = row["WpszState"].ToString();
62 sls.sz_supervisorname = row["szName"].ToString();
63 sls.sz_salemanname = row["Expr1"].ToString();
64 sls.sz_salesmancode = row["szEmployeeId"].ToString();
65 sls.sz_Divisi = row["Divisi"].ToString();
66 sls.sz_typeOp = row["szDescription"].ToString();
67
68 DataSalesArr.Add(sls);
69 }
70 Tex.CloseConnection();
71 //AddQstrWhere("",wheretext);
72
73 return (DataSalesArr);
74
75 }
76 catch(Exception ex)
77 {
78 throw ex;
79 }
80
81 }
82 You see I want to make the query more dynamic. So instead of making the SQL parameter permanent I wanna make it dynamic, so the usercan choose whether they want to use the parameter or not. I already declare the parameter which is szSalesmanID. There's a function called AddQStrWhere which can make the query more dynamic. But the thing is when I wanna use the parameter it seems the code doesn't take it, it just use the regular query I dont know why.I already use the function but it just wont work, can you guys tell me what's wrong, Please I really do need some help from you guys, feelin; a lil' bit desperate in here.I appreciate any kinds of help, thanks. FYI: I use Visual Studio.NET 2003, SQL Server 2000.Best Regards.   

View Replies !   View Related
How Do You Execute Udf Functions From ADO .net
 I hope this is a right form for ADO .net type of question. 
 My question is, can you call SQL function the way you call stored procedure from ADO .net.  I coded it this way and does not seems to be getting result set back.  The DataReader is seems to be coming back with nothing.  Can someone post an example.  I know you can write "SELECT udf_function()" but I really mean the way the stored procedure is called.  Thanks.
 

View Replies !   View Related
Access SQL Functions Through .net??
I usually access stored procedures using SQL data source. But now  I need a string returned from the database. If I write a function in SQL how do I access it from an aspx.vb file?

View Replies !   View Related
Datetime Functions...
Hi all,

I have a problem with date functions...

In my program I use weeks, and with this variable I need to know which is the first day of the selected week...

For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).

I have proved with this functions...

SET DATEFIRST 1 (to configure Monday as first day of week)

SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)

With this I have the day of the current week... but I can't put my week in this function... aarrgggg.

Please help me...

Thanks a lot.

View Replies !   View Related
Procedures V. Functions
The place where I work is moving to MS SQL Server from Lotus Notes. Ihave done a lot of coding in Lotus Notes, and have, I suppose,intermediate skills in basic SQL -- queries, insert, updates, tabledesign, etc. I have a couple of questions, however. First, storedprocedures vs. functions. In my world, a function is a body of codethat returns a value; a procedure is a body of code that does thingsbut does not return a value (other than an error if it fails). At firstblush, MS SQL seems to follow this distinction. But further reading ofthe Books Online and paper books (like Advanced T-SQL for SQL Server2000) demonstrate that stored procedures also return values. So, whyuse one over the other? Apparently, I can debug SQL stored procedures(haven't tried yet) and it looks like I can't debug functions. On theother hand, I can use a function in an SQL statement but I can't use aprocedure (I think). So, if I want to take two columns and do somehand-waving over them to make a third column for each row in a row set,I need to use a function.I guess the above gives you a sense for what little I know. What arepeople's thougts on functions vs. stored procedures. What's a good bookthat might give me better insight into these sorts of issues withoutspending a lot of time on what a left outer join or a derived table is?Thanks.BD

View Replies !   View Related
Column Name In Functions
Hi,Can we use a parameter that is a column name in a function ?Here's my function :CREATE FUNCTION dbo.fn_counting (@colnumber varchar(2),@numbervarchar(1))RETURNS int ASBEGINDECLARE @column varchar(2)DECLARE @ColTotal intSET @column = 'R' +@colnumber(This next line WORKS !!!)SELECT @ColTotal = COUNT(*) FROM dbo.Tbl_Answers WHERE R3 = @number(This next one DOESN'T WORK - because of the ' it is treated as astring)SELECT @ColTotal = 'COUNT(*) FROM dbo.Tbl_Answers WHERE ' +@column +'=' +@numberRETURN @ColTotalENDThank you

View Replies !   View Related
Math Functions
Hi,How to find the list of SQL math functions in SQL Server 2005?ThanksKai

View Replies !   View Related
Creating Functions
Well, I just downloaded SQL 2005 Express to have a play. Good old Billsays "up and running in 20 minutes"Well 5 hrs, 5 reboots and 4 full un-install and re-install later I havefinally got it working. About what I expected really.Anyway, my question relates to creating user defined functions. I've 10yrs exp in MS Access and VB, and user ent Manager to create views onthe work server, but havnt done much with functions.I'm using the Management Studio Express.I've opened a new view and copied the following sample code but it wontlet me save it.-------------------------IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULLDROP FUNCTION dbo.GetWeekDay;GOCREATE FUNCTION dbo.GetWeekDay -- function name(@Date datetime) -- input parameter name and datatypeRETURNS int -- return parameter data typeASBEGIN -- begin body definitionRETURN DATEPART (weekday, @Date) -- action performedEND;GO------------------------------------------When I try to save it it says"incorrect syntax near the key work 'IF'incorrect syntax near 'GO'CREATE FUNCTION must be the first statement in a query batchmust declair the scalar variable "@Date"Whats wrong with the syntax???Thanks in advanceGrant

View Replies !   View Related
Date Functions
Hi all,Are there a functions for last day of the month or first day of the month?Thanks in advance,Jen--Fast Track On Line -Web Design and DevelopmentPortfolio http://www.fasttrackonline.co.uk---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

View Replies !   View Related
Help With More Advanced Functions
I have been able to get several basic databases to function both in playingaround and functional ones on the web but they have all been pretty simple.I am now trying to develop a database for the web using Access. What I amreally needing help with is how to actually reduce the number of record setsthat I think i am going to need. Here is what I am trying to do.I am having people sign up and create teams. So you come to the site and yousee Team 1, Team 2... Team 8. (These numbers will later be replaced by realnames given by the team captains and replaced in the database, this will bedone by the captain through the web)You pick a team you want to be on, enterthe info and you are placed on that team (in the database) I have two tablesin the database TeamRoster (everyone on all of the teams) and Teams (Listthe captains and the names of their team)So when I query the database to fill the tables I am confused as to how Ishould go about the query. As I see it I need to build a RS (Record Set) foreach team to get the name of the team for the appropriate table, thenpopulate it with the players for that team (repeating region) if there areeight teams, that means 16 RS. This does not seem right.Am I making any sense?Thanks for trying to understand all of this.Houston

View Replies !   View Related
Calling Functions
Dear Everybody,

Hi again!

Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.

The following line of code will not work in SQL Server. How should I achieve this?

declare @Res INT
EXEC @Res = Fxn2 (CONVERT( INT, Fxn1 ))

Thanx in advance!

View Replies !   View Related
Calling VBA Functions From SQL
I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?

View Replies !   View Related
Max No. Of Parameters In Functions
Hi,
Can anybody tell me the max no of parameters that we can use in a single
UDF?
Thanks!!

View Replies !   View Related
Various Functions In TSQL
Hi,
I have a good collection of UDFs in my store,anybody interested to get those plz echo I want...
:angel:

View Replies !   View Related
CHILD Functions?
How can I pick out a "top child" in SQL Server 2000?

Example:

---------------------------------
| Employee || code || action date |
---------------------------------
| 00000001 || AAA || 01/02/2005 |
| 00000001 || DDD || 01/02/2001 |
| 00000001 || CCC || 01/06/2000 |
| 00000002 || AAA || 01/02/2006 |
| 00000002 || CCC || 01/10/2004 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
| 00000004 || SSS || 09/06/2000 |
---------------------------------

I want to pick out the record with the latest date for every employee so I end up with something like this:

---------------------------------
| Employee || code || action date |
---------------------------------
| 00000001 || AAA || 01/02/2005 |
| 00000002 || AAA || 01/02/2006 |
| 00000003 || DDD || 05/12/2001 |
| 00000004 || AAA || 09/09/2006 |
---------------------------------

Any ideas?
Thanks in advance!

-GeorgeV

View Replies !   View Related
Problem With Functions
I have a main table that stores the names of all the tables in that database & the next pkey that the record in the respective table should have when a new record is inserted in it(This is the requirement). I am writing a function which returns the primary key of the tablename entered.

The function that i have written is as follows:

CREATE Function next_pkey(@in_tablename varchar(250),@in_increment int)
RETURNS int
AS
BEGIN
DECLARE @nextid int
DECLARE @out_nextid int
SET @out_nextid = -1
UPDATE main_ids SET nextid = nextid + @in_increment WHERE tablename = @in_tablename
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO main_ids (tablename, nextid) VALUES (@in_tablename, 1 + @in_increment)
SET @nextid = 1
END
ELSE
BEGIN
SELECT @nextid = nextid - @in_increment FROM main_ids WHERE tablename = @in_tablename
END
IF (@@ERROR = 0)
SET @out_nextid = @nextid
Return @out_nextid
END
GO

This function gives 2 erros as mentioned below:
Invalid use of 'UPDATE' within a function.
Invalid use of 'INSERT' within a function.

I have the following query:
Is an update statement allowed in a function?

Can you tell me what should i do so that this function executes properly???
Plz Help?????
Thanks.

View Replies !   View Related
DATE Functions
Hi all,

The statement

select dateadd(month, -1 , convert(datetime, '30-SEP-1996')
return Aug-30-1996.

but I want to get the last day of the month. (ie AUG-31-1996)
for example
select dateadd(month, -1 , convert(datetime, '30-SEP-1996')
I want MAY-31-1996 to return NOT MAY-30-1997.

Thanks in advance for your help
Tung Nguyen

View Replies !   View Related
Sql6.5 Functions
Hello All,
Does anyone know if stored functions can be written for sql similar to stored procedures? And if theey
can, what language?

Thanks

View Replies !   View Related
Date Functions
I have a table of sales transactions that I'd like to group by date, but this is a datetime field so it groups by the date and time, and so it returns more records that I want. Is there an easier way to do this other than using the datepart function to build a date? How can I just look at only the date of a datetime field?

View Replies !   View Related
SQL Server7 Functions
I have an Access 97 project written in VB5. It uses a whole bunch of `Format`s, `IIF`s, etc... I would like to be able to move to SQL Server 7 without making any changes to the code - if I could create a function and add it to the SQL Server 7 system. Does anyone know, or can people tell me what they think the best solution is? even if you do not think that what I want is doable, please tell me your thoughts. Thanks...

View Replies !   View Related
Extending Functions In SQL 7.0?
Is there a way to add functions to SQL (other than stored proceedures) to emulate functions in other systems?

I would like to create a FOXPRO type OCCURS function:

Select * FROM Table1 WHERE OCCURS('Y', FLAGS) > 3

If FLAGS is a VARCHAR(7) and equals "YYYYNNN" then OCCURS would return 4 and the row would return. The database that I am

accessing has >165,000,000 rows and >500 columns per row so you see that I need all the speed I can get!


'
' This is a VB function which works.
' Can this be compiled into a DLL and
' somehow made available to SQL?
'
Public Function OCCURS(expr1 As String, expr2 As String) As Integer
Dim i As Integer
Dim count As Integer
For i = 1 To Len(expr2)
count = count + Abs((Mid(expr2, i, 1) = expr1))
Next
OCCURS = count
End Function

View Replies !   View Related
Greatest And Least Functions
1.I am trying to write following query in SQLserver(this is oracle query),
IS their any equivalent for GREATEST and LEAST functions in SQLServer ???

Update mwebtemp
Set Temp_amount1 = (SELECT SUM(AuthAttr_Amount*(num_Business_Days
(GREATEST(AuthAttr_Start_Date, @dtstartdate),
LEAST(AuthAttr_Finish_Date, @dtenddate))))
.....where...
etc...
.....

Note: this is part of a stored procedure...

2. Also how do you write and call a function in SQLServer (syntax ??), eg.num_Business_Days
is a function returning number of business days between the 2 dates....

thanks a lot

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved