Use Getdate Function In A Own Function
Hi,
I have written a stored proc with some temporary tables and also used
a getdate() in my stored proc. When i try to call the sproc the error
is that we can only use extended sprocs or function inside a sproc.
Now if try to write the stored proc directly inside a fuction ie copy
paste after changing my temp tables to tables the problem is , i get a
error invalid use of getdate in sproc.What do i do to get something
for my results inside a table.
Thanks in advance.
RVG
View Complete Forum Thread with Replies
Related Forum Messages:
Getdate() Within A Function
Im really new to SQL SProcs. I have a function that I wrote that I am trying to compare a date within a record to today's date. The problem is that you cant call getdate from within a function... So, I was thinking that I could create a temp table that had a a date column with a default date of today and select that out. However, I cant find any documentation on how you would create a temp table with a default value, or if this would even work. I dont want to have to pass todays date into the function, nor do I want to have to create a permanent table just to hold this data. Any help, or other ideas? Thanks.
View Replies !
Cannot Use GETDATE ( ) In A Function?
I want to use GETDATE ( ) in a user defined function for date comparing. When I compile the function, got the error: Invalid use of 'getdate' within a function. How to get the current date in a function? Any suggestion will be appreciated. ZYT
View Replies !
GetDate Function
I have a number of tables that are using the getdate() function as a default. The records are being populated via a field on an ASP page using IIS Windows 2000. Occasionaly the date that gets entered is 01/01/1900. Is this an issue of the calender control on the page or could this be coming from SQL Server 2000? Any input in this area would be greatly apprecited.
View Replies !
Getdate() Function
The getdate () function returns the following eg: 2000-11-16 16:13:49.717 Do anyone knows of a function to return only the date eg: 2000-11-16??? Not the time. Thanks!
View Replies !
Bad Function GetDate()...
Does anybody knows if there is a problem que SQL Server 7.0 in the date? I Created this Trigger... CREATE TRIGGER pone_fecha_actual ON Pendent_Invoices FOR INSERT AS UPDATE Pendent_Invoices SET Publication_Date = convert(char(10),getdate(),7) WHERE Publication_Date IS NULL But when browse the information it appears thie date... 1900-01-01 00:00:00.000 Thanks!
View Replies !
Using The GetDate Function
Hi Having some trouble using the getdate function here is some sample code that i am using for an ASP application: SELECT COUNT(spotnum) AS total FROM spotcheck WHERE year(checkdate) = year(GETDATE()) that will give me a count of all records for this year, how do i get a count for records last year? year(GETDATE(-1))???? sorry im so clueless -Jesse
View Replies !
Getdate Function SQL
I am trying to return data from between 2 dates. in this instance it is returning data from yesterday which works fine. However I am trying to return data for yesterday before 12 noon. The following returns yesterdays data. WHERE (dbo.People.SystemUser = 1 AND dbo.Actions.ActionDate > DATEADD(dd, DATEDIFF(dd,0,getdate()), -1) AND dbo.Actions.ActionDate < DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) AND dbo.People.PersonId = 'JO') How would I get it to return only yesterday before midday's data? Thanks, Tugsy
View Replies !
Problem With GETDATE() Function.
Hi everyone, DECLARE @altstartdate datetime SET @altstartdate = getdate() set @altstartdate = @altstartdate -1 select * from tbl_document where convert(char,dicreationdttm,101) = convert(char,@altstartdate,101) The person who wrote the code was trying to get the previous day of data, So today is 03/07/06, within the database it only have data from 03/06/06 and back 15 days, so what I want is data from 03/06 - -2/20, which I believe is 15 days worth of data, How would I go about geting 15 days of data, do I still need to use getdate() function. Any help please. Thanks Lystra
View Replies !
Getdate() Error In Function
I have the below function which errors out telling me "Invalid use of 'getdate' within a function." I can run it as sql but not as a function, is there an issue with using getdate() in a function? SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER function fnGetQuantity( @orderid int ) returns int as /************************************************** **************************** ** File: fnGetQuantity.sql ** Name: fnGetQuantity ** Desc: Used to calculate the total order quantity for an order ** Quantity is sigfrequency.timesperday * sigdosageamt.descr * orders.duration ** ** Return values: Quantity ** ** Called by: ** ** Parameters: order Id ** Input Output ** ---------- ----------- ** @patientid ** Auth: DHoefgen ** Date: 04/30/05 ************************************************** ***************************** ** Change History ************************************************** ***************************** ** Date: Author: Description: ** -------- -------- ------------------------------------------ ** 05/02/05 KKowert Changed sql for effdt and added duration and ** times per day logic for zeros. ************************************************** *****************************/ begin declare @QuantityTotal int SELECT @QuantityTotal = (o.Duration * f.TimesPerDay * d.Descr) FROM Orders o INNER JOIN SIGFrequency f ON o.FreqID = f.FreqID INNER JOIN SIGDosageAmt d ON o.DosageAmtID = d.DosageAmtID WHERE (o.OrderID = @orderid) AND (f.Effdt = (SELECT MAX(f2.Effdt) FROM SIGFrequency f2 WHERE f2.FreqID = f.FreqID AND f2.Effdt <= getdate())) AND (d.Effdt = (SELECT MAX(d2.Effdt) FROM SIGDosageAmt d2 WHERE d2.DosageAmtID = d.DosageAmtID AND d2.Effdt <= getdate())) return @QuantityTotal end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
Data By ID And Getdate Function
I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable. For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested. Code Snippet <% set con = Server.CreateObject("ADODB.Connection") con.Open "File Name=E:webserviceKuowKuow.UDL" set recProgram = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))" 'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')" recProgram.Open strSQL,con %> <% recProgram.Close con.Close set recProgram = nothing set con = nothing %> Output: ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink (Reading across the screen from left to right) 1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm Here is the URL used for testing: http://Test Server IP/test/defaultweekday2.asp I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID. http://Test Server IP/test/defaultweekday2.asp?ID=1234 How to do this?
View Replies !
MS SQL GetDate() Function Remove Time
Hi,I am creating creating a table with a Date column dd-mm-yyyy. But Icant seem to find a SQL function that just returns today's date.getDate() returns the time as well so I cant use it.The reason is simply that I want to update/overwrite over and overagain all records from current day but not touch the ones fromyesterday etc and with the timestamp in there I just end up addingmore and more rows for the same day.In other words I only want to preserve rows are from yesterday orolder but overwrite ones from today.Any help will be appricated.Thank you!Yas
View Replies !
GETDATE() With A User Defined Function
Hi, I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated. Regards, Samcute.
View Replies !
Getdate() In User Defined Function
Hi, It is possible to use getdate() in userdefined function. If so, how to do the same ? The following code throws error : create function function1 return varchar DECLARE @currYYMM VARCHAR(20) SET @currYYMM = convert(char(4),getdate(),12) // Here it says the error 'getdate' can't be used inside functions ............... .....................
View Replies !
How To Get Current Date Using Getdate() Or Any Other Function
Hi! I'm new to T-SQL and trying out my queries using the SQL Query Analyzer. What I intend to do is to retrieve records based on current date. The field is Trans_R.Tr_Date, of type nvarchar(255) and the data inside the field is formatted as yyyymmdd. My Q's are: 1. Where should I put the WHERE statement at? 2. Is there any other way to work around this problem without resorting to converting the Trans_R.Tr_Date field to Datetime datatype beforehand? 3. I tried using Getdate function as part of the WHERE statement but nothing comes out. Would this WHERE statement work (or make sense)? WHERE Trans_R.Tr_date = getdate() Please advise and thanks in advance. ------------- START OF CODE ------------- SELECT Cards.card_number,Trans_R.Tr_Date,Trans_R.staff_nu mber, MIN(Trans_R.Tr_In) AS Actual_Time_In, MAX(Trans_R.Tr_Out) AS Actual_Time_Out, 'Status'= CASE WHEN MIN(Trans_R.Tr_In) IS NULL THEN 'ABSENT' WHEN MIN(Trans_R.Tr_In) < 171500 THEN 'ATTEND' ELSE 'ABSENT' END, 'Late Time' = CASE WHEN (MIN(Tr_In) - 080000> 0) THEN (MIN(Tr_In) - 080000) ELSE '' END, FROM Trans_R INNER JOIN Cards ON Trans_R.Staff_Number = Cards.Staff_Number GROUP BY Cards.card_number,trans_r.staff_Number,Trans_R.Tr_ Date ------------- END OF CODE -------------
View Replies !
Sql Server 2005 GETDATE() Function
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector. When I use the SQL Mangment Studio Express to execute this query: SELECT GETDATE() it returns 2008-04-22 16:25:03.690 which is OK !! I want to get that same value using a Java program so I do that query but when I display it it shows as: 2008-04-22 and there is no time there ! I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM So maybe the time isn´t there ? This is the code i am using: Statement st = con.createStatement(); String sql = "SELECT GETDATE()"; ResultSet result1 = st.executeQuery(sql); result1.next(); Date today = result1.getDate(1); Any help would be appreciated.
View Replies !
Problem With Getdate Function In Optional Parameters
Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used. I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works. ALTER PROCEDURE [dbo].[LinksFees_Record] @Date datetime = getdate AS INSERT INTO LinkSearchFees (LinkID, Price, [Date]) SELECT ID AS LinkID, SearchDayFee AS Price, @Date FROM Links WHERE (SearchDayFee > 0) RETURN When I call the StoredProcedure the following exception occur: Conversion failed when converting datetime from character string. How can I fix it?
View Replies !
Derived Column Returning No Data On GetDate() Function
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View Replies !
Automated Testing | How To &"force&" GETDATE() Function To Return Specific Value?
Hello,Our QA team have running a lot of test scripts (for automated regressiontesting), they run them on the different databases (Oracle/MS SQL).Several of those tests are dependent on the current date/time. In order tobeable to use them efficiently, we changed the current date/time on the QAdatabase server to a specific date/time before starting the scripts, so weare sure the test scripts always run in the same environment.Resetting the date/time of the database server gives us more and moreproblems (OS problems, backup/ virusscan, ...).It is possible to fix the problem with SYSDATE function on Oracle by settingFIXED_DATE init parameter.Is it possible to 'change' the current date/time on 'database' level,instead of on OS level for MSSQL2000?Do you know other means to do such things?Thanks in advance,Konstantin
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Function To Call Function By Name Given As Parameter
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 Replies !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
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)
View Replies !
Calling A Function From A Function?
Hi All Yesterday Peso was gracious enough to help me with creating function/views/sp's I took those examples and extended what had from excel into function in SQL however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function? Here are excerpts two functions I have: We'll call this function UserUsage() ------------------------------------ RETURN( SELECT ut.LastName, ut.FirstName, CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)], Max(hu.time_stamp) AS [Last Log Date], pct.Title, cat.topic_name FROM ZSRIVENDEL.dbo.UserTable ut, ZSRIVENDEL.dbo.history_usage hu, ZSRIVENDEL.dbo.pc_CourseTitles pct, ZSRIVENDEL.dbo.cam_topics cat WHERE ut.student_id = hu.student_id AND hu.course_id = pct.CourseID AND hu.topic_id = cat.topic_id AND ((ut.ClientID=@ClientID) AND (pct.ClientID=@ClientID) AND (ut.GroupID=3400) AND (hu.time_stamp>= @StartDate And hu.time_stamp< @EndDate) AND (hu.session_time<21600000)) GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name ) and will call this function UserSummary(): ----------------------------------------- RETURN ( SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] FROM ZSRIVENDEL.dbo.UserTable AS ut INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID WHERE ut.ClientID = @ClientID AND ut.GroupID = 3400 AND hu.Time_Stamp >= @StartDate AND hu.Time_Stamp < @EndDate AND hu.Session_Time < 21600000 GROUP BY ut.LastName, ut.FirstName ) As you can see the first part of the both query are simlar. In particular the: SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] and also the variables @StartDate and @EndDate. In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please? Thank you!
View Replies !
Getdate() >= Startdate And Getdate() <= Enddate
Please i need an exmple of ur solution, thanks :) I'm using some files to show certain pages on certain date for an example File name : aa.doc start date: 10/02/00 end date : 10/03/00 But it expires on 10/02/00, here is the strored procedure: Before the date comes, it expires the page Here is my stored procedure: " SELECT startdate, enddate,archivedate and (startdate is null or (getdate() >= startdate and getdate() <= enddate)) and (archivedate is null or (getdate() <= archivedate)) group by startdate, enddate order by startdate desc " Thankx a lot
View Replies !
Using SQL MOD Function
DECLARE @TotalAmount AS INTEGER DECLARE @TotalPoints As INTEGER SELECT @TotalAmount = SUM(Amount) From Rewards WHERE MemberID = @MemberID AND Redeemed = 1 @TotalPoints = @TotalAmount MOD 20 I have the code above but I keep getting an error message around the last line that I am not sure what it means. Can anyone please help?? Thanks in Advanc3e
View Replies !
Use Function In Asp.net
I have this scalar -valued function in sql called dbo.GetGoodCustomer ALTER function [dbo].[GetGoodCustomer](@client_id int)returns numeric(10, 2)asbegin declare @getgoodcustomer as numeric(10, 2) declare @review_type as int select @review_type = item_num from customers where client_id = @client_id if @review_type = 0 begin select @getgoodcustomer = getgoodcustomer from customers where client_id = @client_id end if @review_type > 0 begin select @getgoodcustomer = l.g from customers c inner join detail l on c.client_id = l.client_id where c.client_id = @client_Id and c.item_num = l.item_num endReturn @getgoodcustomerendNow, how can I use this function in asp.net (vb.net) ?Because whatever is getgoodcustomer would give me I need to display that in datagrid's text box.Loop thru datagrid If CType(dgItem.FindControl("txtID"), TextBox).Text = 13 ThenDim txtgoodcustomer As TextBox = CType(dgItem.FindControl("txtcust"), TextBox)basically amount returns from function would be showing in txtcust textbox.I don't know I have never done this before..
View Replies !
SQL AVG Function
I have a table:tblRateUserUserIDJudge intUserIDJudged intscore int this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself. Each user can only score another user once. tblRateUser content:judged judge score6 5 87 5 107 6 515 7 415 5 9 When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records. declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID) print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7 in an average score of 7.0 whereas I would expect 7.5 (10+5)/2 what am I doing wrong?
View Replies !
Last Function
SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server???? tblClients - ClientID (PK) tblPayments - PaymentID (PK) Thanks for any help,
View Replies !
SQL Function
hello everyone,I created a function in MSSQL that builds an html coupon based upon thecoupon ident (more complex than that, but that's not where i'm havingproblems!)When the function builds the coupon, it returns the stream asVarChar(8000)I am calling this function in a sproc that passes in the coupon ident.But when I execute this sproc, I get the following error:Syntax error converting the varchar value '<html>......"What am I doing wrong? Are there problems with the datatypes I"musing?Thanks in advance for any help on this?
View Replies !
Need A Function
I'm looking for a string function that is similar to the INSTRfunction in VB. I haven't seen anything in the help files that I canuse. Does anyone have any suggestions?Here's what I'm trying to do:There is a field in a table that will look something like this -"XXXXXX - YY".I want to separate it on the dash and get two strings out of it -"XXXXXX" and "YY". I'm trying to keep it all in a stored procedureand avoid a vb script or exe.I'm envisioning something like this:declare @CDT datetimeselect @CDT = createdatetime from imOrderHdrwhere VendorCode = 'SYG' and createdatetime is not nulland status in (1,2,3)select d.VendorStockNumber, substring(i.ItemDescription, 1,instr(iItemDescription, '-') - 1),substring(i.ItemDescription, instr(iItemDescription, '-') + 1),d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) asExtensionfrom imOrderDetail djoin imItem i on i.ItemCode = d.ItemCodewhere d.CreateDateTime = @CDTI'd write my own function, but the computers this will be run on haveSQL 7.Any suggestions will be appreciated.Thanks!Jennifer
View Replies !
Function
Good Morning,I have a question.Is there a function like ISNULL but for the space value(example ISSPACE)?THANKS
View Replies !
SP Vs FUNCTION
Hi, When we talk about the difference between SP and Function, it is said that Function returns a value where as SP doesn't. My Question is, Then what does the "OUTPUT" parameter Stands for in SP? Kindly make my doubts clear on this. Thanks In Adv. Rahul Jha
View Replies !
Last Function
Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature? Example: Item_Code Time 123456 11:40 123456 11:41 123456 11:42 By grouping on item code and using the last function on the Time column, the third record would be returned. Item_Code Time 123456 11:42 All, help is appreciated.
View Replies !
Using A Function
I have found this very practicle function CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000), @FROM VARCHAR(256), @TO VARCHAR(256)) RETURNS VARCHAR (8000) AS BEGIN -- effets de bord IF @VALIN IS NULL RETURN NULL IF @FROM IS NULL OR @TO IS NULL RETURN NULL IF LEN(@VALIN) = 0 RETURN @VALIN -- initialisation DECLARE @I INTEGER DECLARE @OUT VARCHAR(8000) SET @OUT = '' -- lecture caractère par caractère SET @I =1 WHILE @I <= LEN(@VALIN) BEGIN IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0 BEGIN IF LEN(@TO) >= PATINDEX('%' + SUBSTRING(@VALIN, @I, 1) + '%', @FROM) SET @OUT = @OUT + SUBSTRING(@TO, PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1) END ELSE SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1) SET @I = @I + 1 END RETURN @OUT END but how can I use it in MS SQL 2000-25000 ? FN_TRANSLATE(dbo.Users.Name, 'àâäçéèêëîïôöùûüÿ', 'aaaceeeeiioouuuy') doesnt work I get an error on the colum name dbo.Users.Name thank you
View Replies !
SUM Function
I have this query: SELECT SUM(st_Count) FROM Queue WHERE st_ID = 1483 It works, but how can I change it, to see SUM for many ID's, which I have in list? I try this: SELECT SUM(st_Count) FROM Queue WHERE st_ID = List.tw_ID But there is error: The multi-part identifier "List.tw_ID" could not be bound. How to display SUM for meny ID's?
View Replies !
Len() Function
We have len() function in some of our stored procedures. Why one of our SQL servers (6.5) will not recognize this function while others do? While we transfer the stored procedures, the stored procedure can not be transfered to the database of that server. Your immediate help will be appreciated. Su
View Replies !
|
TRACKER
