Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it.
CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )
I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.
CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int
SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID
SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID
SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)
If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END
insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)
RETURN
END
I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
View Complete Forum Thread with Replies
Related Forum Messages:
Return Multiple Values From A Function
searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005? e.g., I want to do this: select id, data, whatever, dbo.fnMyFunction(id, whatever) from table and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction possible? easier way? thanks in advance...
View Replies !
How To Return Multiple Table Values From A Function
Hi, We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function. Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int), As Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Return End This Works Fine :- And Gives The Reqd. Results. But, If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk. Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int) As Begin Declare @cnt Int Set @cnt = 1 While @cnt <= 2 If @cnt =1 Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Set @cnt = @cnt + 1 End If @cnt =2 Begin Insert @k2 Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B On A.intcustomerid = B.intcustomerid Where Chremail <> ' ' And Chremail Is Not Null And Intpreferenceid In (6,7,2,3,12,10) Set @cnt2 = @cnt2 + 1 End End Return End Can We Return Two Tables Or Is It Not Possible ? Pls Chk Into This And Tell Me. Thanks.
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
I'm a Reporting Services New-Be. I'm trying to create a report that's based on a SQL-2005 Stored Procedure. I added the Report Designer, a Report dataset ( based on a shared datasource). When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure. I'll only reproduce one instance of the error for the sake of brevity. [rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value. I've searched on this error and it looks like it's a Visual Basic error : http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx My guess is that BIDS is creating some VB code behind the scenes for the report. I can't find any other information that seems to fit this error. The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report. BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables. Can anyone help ? Thanks,
View Replies !
Expression Does Not Return Decimal Value!
Hello, Below statement in tsql returns int value (0). SELECT 5/10 How to force it to return decimal value (0.5)? I tried following but it did not help me 1) SELECT Convert(numeric(7,2),5/10) 2) Declare @Var1 numeric(7,2) SET @Var1 = SELECT Convert(numeric(7,2),5/10) SELECT @Var1 Thanks.
View Replies !
COUNT Expression Values
Hello, I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor. Any info is helpful.
View Replies !
Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services... I am using RS2000 I am trying to apply cint function in the expression editor for the textbox eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value). This is returning error The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow. Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
View Replies !
SUM A Column Of Values Derived From An IIF Expression
I have a table in my report which shows sales values for each month by looking at the month number so for Jan i use =IIf(Fields!period_.Value=1,Fields!nett_.Value,0) for Feb =IIf(Fields!period_.Value=2,Fields!nett_.Value,0) this is all good and I get a series of rows per customer with the correct value in the correct column JAN FEB Mar Customer A 250 Customer A 350 Customer A 5000 . However I want to create a summary a line JAN FEB Mar Customer A 250 350 5000 I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc. Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it. Cheers Steven
View Replies !
Filters && Expression With String Values, Should They Work??
Any idea why this expression would not work in SSRS? Based on a expression on a group textbox I get 0 records that match: =Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0) I've evaluated in the proc & there should be a count of 29 records. I found a work around by using this in the stored proc & I get my record count: ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END and changed the expression in SSRS to this & it works: =sum(Fields!ACCRUAL_CODE.Value) Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
How To Find Terms In A File Name: Regular Expression OR A String Function
hi, i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files. the file are named as: ff_inbound_20071008_1.csv ff_inbound_20071008_2.csv where for file ff_inbound_20071008_1.csv: ff => flat file inbound => dataset of this csv 20071008 => date 1=> file count having in mind that they are store in the variable as a full path: z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv i need to extract the dataset and the date for each file. how can i extract these terms from the file name? many thanks, nicolas
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG' I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression. Do you have a recommended implementation for this situation? Thanks, Toni Fielder
View Replies !
Getting A Return Value From A Function.
Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID: Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)I cant seem to accomplish this. It returns nothing. Please help.TIA,Stue<code>Function Get_AttendID(ByVal strAttendID As String) As SqlDataReaderDim connString As String = ConfigurationSettings.AppSettings("ClassDB")Dim sqlConn As New SqlConnection(connString)Dim sqlCmd As SqlCommandDim dr As SqlDataReader sqlConn.Open()Dim strSQL As String = "Select AttendID from attendees Where FirstName=@FirstName and LastName=@LastName and classbegdt = @classbegdt and survey = '0'" sqlCmd = New SqlCommand(strSQL, sqlConn) sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@FirstName").Value = tbFirstName.TextsqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@LastName").Value = tbLastName.TextsqlCmd.Parameters.Add("@classbegdt", SqlDbType.DateTime, 8)sqlCmd.Parameters("@classbegdt").Value = calBegDate.SelectedDate.ToShortDateStringdr = sqlCmd.ExecuteReader()dr.Close()sqlConn.Close() Return dr End Function</code>
View Replies !
Function Return Value
I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations. Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else. You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQLData" instead of "D:MSSQLData" I then supply the backslash in the SET statement. I assume that this has something to do with my question. Any suggestions? Thank you. HERE IS T-SQL FOR THE FUNCTION IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL BEGIN DROP FUNCTION fn_sqlmgr_get_mdf_filepath END GO CREATE FUNCTION fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database. ) RETURNS NVARCHAR AS BEGIN --Extract the file path for the database MDF physical file. SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename)) FROM master..sysfiles mdf WHERE mdf.groupid = 1 SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('', REVERSE(@MDF_FILE_PATH))) RETURN @MDF_FILE_PATH END HERE IS THE TEST I AM USING AGAINST THE FUNCTION SET NOCOUNT ON DECLARE @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database. SELECT @MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH ) PRINT @MDF_FILE_PATH
View Replies !
Return Values?
Using ado, the _CommandPtr::Execute method returns a recordset. When calling a storedproc with this method, does the 'return' statement return the value in a recordset ? EG: //c++ ADO _CommandPtr pCmd(__uuidof(Command)); ............ _RecordsetPtr pRs = pCmd->Execute(NULL, NULL, adCmdStoredProc); // Stored proc CREATE PROCEDURE usp_my_proc AS SELECT .... FROM tbMyTable WHERE x = something IF(@@ERROR <> 0) return -1 ..... What in the case of an error would be returned to pRs recordset? Any thoughts? Mike B
View Replies !
Return Values In SP
hello, is it possible to return a return value as a numeric datatype I can successfully return as an integer, but when i change it to numeric i get this error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) [Microsoft][ODBC SQL Server Driver]Invalid scale value /output.asp, line 15 my output value in my asp code is thus Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4) thanks Afrika
View Replies !
Return Values In SP
hello, is it possible to return a return value as a numeric datatype I can successfully return as an integer, but when i change it to numeric i get this error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) [Microsoft][ODBC SQL Server Driver]Invalid scale value /output.asp, line 15 my output value in my asp code is thus Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4) thanks Afrika
View Replies !
I Need The Function To Return A Srting
Hii every one When i use the function of (select) from the data bass it return dataset or some thing else But I need it to return string or the data element which in the query not all the query like I dont need that _____________ | Id | Name | ----------------- | 1 | Bill | -------------------- I dont need All of that to display But I need to display the name only in Label or textbox like Bill Thanks Maro
View Replies !
Return @@identity For Another Function
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this image_23.jpg (23 being the @@identity) resize it and save it to specified directory I cant seem to get the identity to return to my script. This is my SP CREATE PROCEDURE SP_Insertad ( @catid int, @subcatid int, @areaid int, @uid int, @adtitle varchar(255), @addescription varchar(1000) ) AS Insert Into Tbl_ad (ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location) VALUES (@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1) select @@identity return GO I tested in query analyser, and it works fine, so It must be my code. this is my function Sub Insert_pic(sender as object, e as eventargs) Dim catid = Request.form("ddcats") Dim subcatid = Request.form("subcatrad") Dim adtitle = Request.Form("txttitle") Dim AdDescription = Request.form("txtdescription") Dim uid = getUID(Context.User.Identity.Name) Dim areaid = Request.form("ddarea") SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB")) SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect) SQLCommander.Commandtype = Commandtype.StoredProcedure SQLCommander.Parameters.add("@adtitle", adtitle) SQLCommander.Parameters.add("@addescription", addescription) SQLCommander.Parameters.add("@catid", catid) SQLCommander.Parameters.add("@subcatid", subcatid) SQLCommander.Parameters.add("@uid", uid) SQLCommander.Parameters.add("@areaid", areaid) '// this section not working right, it wont write return id Dim paramreturn as SQLParameter paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int) ParamReturn.Direction = ParameterDirection.ReturnValue response.write(SQLCommander.Parameters("ReturnValue").Value) SQLConnect.open() SQLCommander.ExecuteNonQuery() SQLConnect.close() End sub Can anybody see anything I missing? I appreciate any imput
View Replies !
Catch The Return Value From A Function
Hi guys! the problem is this: I have a procedure that execute a function and i want to catch the return function value but i'm getting a null value. I'm using: exec @a = <function> but the @a variable is allways null... If i exec the function directly, i get the correct value. What am i doing wrong? Thanks in advance.
View Replies !
Question Regarding A Function Return
I have a function that is using a select case decision structure to arrive at a return value: [dbo].[fn_Window] (@Start AS INT, @End AS INT) RETURNS VARCHAR(5) AS BEGIN SELECT CASE /*** Pick up the extended periods here ***/ WHEN @Start = 1 AND @End = 1 THEN 'TRUE' ELSE 'FALSE' END END **** from here how do I get the function to return true or false? **** Can I assign the select case result to a variable? If so how?
View Replies !
Function Truncating The Return Value - Why?
Hi all, I have created an Sql function that returns a SqlString type. I have set the function signature and facet that should return the max size see imediately below. I am sending as well the command screen return for the return variable where there is no truncation. I have also sent the results returned from the output window and you can see the truncation there. I get the same truncation when I call from Sql Server Management Studio. When I call it with the LEN function it returns a length of 264 characters, much less than the MAX. Please tell me what I can do to resolve this and not have it truncating my return string. Thanks [Microsoft.SqlServer.Server.SqlFunction] [return: SqlFacet(MaxSize = -1)] public static SqlString GetMultiValuedMeaning(SqlXml XmlFields, SqlXml XmlMapping) { ... ... SqlStringReturn = retVal.Equals(string.Empty) ? SqlString.Null : new SqlString(retVal) ; return SqlStringReturn; } >? SqlStringReturn {€¢ Chronic cough €¢ Headaches €¢ Back pain €¢ Muscle aches €¢ Skin diseases or rashes €¢ Ringing in the ears €¢ Chest pain or pressure €¢ Dizziness, fainting, light headedness €¢ Diarrhea, vomiting, or frequent indigestion €¢ Difficulty remembering €¢ Increased irritability } CompareInfo: {CompareInfo - 1033} CultureInfo: {en-US} IsNull: false LCID: 1033 SqlCompareOptions: IgnoreCase | IgnoreKanaType | IgnoreWidth Value: "€¢ Chronic cough€¢ Headaches€¢ Back pain€¢ Muscle aches€¢ Skin diseases or rashes€¢ Ringing in the ears€¢ Chest pain or pressure€¢ Dizziness, fainting, light headedness€¢ Diarrhea, vomiting, or frequent indigestion€¢ Difficulty remembering€¢ Increased irritability" > Column1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- €¢ Chronic cough €¢ Headaches €¢ Back pain €¢ Muscle aches €¢ Skin diseases or rashes €¢ Ringing in the ears €¢ Chest pain or pressure €¢ Dizziness, fainting, light headedness €¢ Diarrhea, vomiting, or frequent indigestion €¢ Difficulty remembering €¢ Increased irrit No rows affected. (1 row(s) returned)
View Replies !
Why My Function Wont Return The Value
//this is the function double sem_3 ( double gpa_sem_1, double gpa_sem_2 ) { int back_3; char grade [2]; double score = 0; double gpa_sem_3; double cgpa_sem_3; double problem_solving_and_programming; double fundamentals_of_finance; printf("FCCS1013 Problem Solving and Programming : "); scanf("%s",&grade ); score = grade_selection( grade ); problem_solving_and_programming = score * 3; printf("FBFF1013 Fundamentals of Finance : "); scanf("%s",&grade ); score = grade_selection( grade ); fundamentals_of_finance = score * 3; gpa_sem_3 = ( ( problem_solving_and_programming + fundamentals_of_finance ) / 6 ); cgpa_sem_3 = ( ( gpa_sem_1 + gpa_sem_2 + gpa_sem_3 ) / 3 ); printf("GPA = %.4f", gpa_sem_3); printf("CGPA = %.4f", cgpa_sem_3); printf("press 0 to go back to option menu: "); scanf("%d", &back_3); if( back_3 == 0 ) { system("cls"); main(); } else { printf("invalid option "); } return gpa_sem_3; } -------------------------------------------------------------------------------------------------------------------------------------------- //this is the main program(part of it only) case 1: system ( "cls" ); gpa_sem_1 = sem_1( ); break; -------------------------------------------------------------------------------------------------------------------------------------------------- // why doesn't the error promp when the user enters an invalid entry? in the codes below. double grade_selection ( char grade[] ) { double score = 0; if (strcmp( grade, "A+" ) ==0 || strcmp( grade, "a+" ) == 0) { score += 4.00; } else if (strcmp( grade, "A-" ) == 0 || strcmp( grade, "a-" ) == 0 ) { score += 3.70; } else if (strcmp( grade, "B+" ) == 0 || strcmp( grade, "b+" ) == 0 ) { score += 3.30; } else if (strcmp( grade, "B" ) == 0 || strcmp( grade, "b" ) == 0 ) { score += 3.00; } else if (strcmp( grade, "B-" ) || strcmp( grade, "b-" ) == 0) { score += 2.70; } else if (strcmp( grade, "C+" ) || strcmp( grade, "c+" ) == 0 ) { score += 2.30; } else if (strcmp( grade, "C" ) || strcmp( grade, "c" ) == 0 ) { score += 2.00; } else if (strcmp( grade, "D" ) || strcmp( grade, "d" ) == 0 ) { score += 1.00; } else if (strcmp( grade, "F" ) || strcmp( grade, "f" ) == 0 ) { score += 0.00; } else { printf( "invalid grade" ); printf( "please enter again: " ); getchar( grade[2] ); } return score; } ------------------------------------------------------------------------------------------------------------- thanks.
View Replies !
Table Return Function
Dear all, can i write a table return function like this? create function my_function( returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME) insert into @mytable select col1,col2,col3,col4 from tab1 go select col1,col2,col3,col4 from tab2 go select col1,col2,col3,col4 from tab3 go select col1,col2,col3,col4 from tab4 go return end am i doing correct? what i'm expecting from this function is, i need all the data from select statements should be inserted into one table. please guide me in this regard Vinod Even you learn 1%, Learn it with 100% confidence.
View Replies !
Multiple Return Values
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000) Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method. I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter. However, I feel this solution is kludgy. Is there a more correct way to accomplish this? Thanks in advance for your comments.
View Replies !
Catching Return Values Of A SP
I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable. Here is a piece of my SP inside SQL Server that shows the returned values: … SELECT @Id = SCOPE_IDENTITY() SELECT @Id AS user_id SELECT 1 AS Value END GO In my aspx page I am trying to call the first value like this: Dim nID CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "user_id")) CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue CmdInsert.Parameters("@RETURN_VALUE").Value = nID And to check if the right value is returned I use: strConnection.open() cmdInsert.ExecuteNonQuery 'Set the value of a textbox ident.text = nID strConnection.close() But now no value appears in the textbox, How can I achieve it? What is wrong?
View Replies !
Return Values From DTS Wizard
I want to launch the DTS wizard through a C# application. Myapplication simply launches the wizard using Process.Start() and thenwaits for the process to finish. When the control comes back to myform, from where DTS wizard was launched, all I need to know is whetherthe wizard was finished or whether the user pressed the cancel buttonon the wizard. Is there any way to know this info?TIA
View Replies !
READTEXT And Return Values
First question : How can I set the return value of READTEXT to a variable of type nvarchar. Second question : I have a table t1 with a ntext column n1. The ntext column has has words separated by empty space. Each word can be assumed to be of size <= 255 characters. How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255). Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type. Any help on this is greatly appreciated. Please do provide a sample code. Alok.
View Replies !
Return DISTINCT Values
Hi, How do I ensure that DISTINCT values of r.GPositionID are returned from the below?? Code Snippet SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType FROM @GResults r LEFT OUTER JOIN ReconComments cm ON cm.GPositionID = r.GPositionID WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g) ORDER BY GCustodian, GCustodianAccount, GAssetType; Thanks.
View Replies !
Select Does Not Return Values
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View Replies !
Return Values Like Other Values
Hi, I have two tables. Both tables have a FirstName and a LastName field. I can return the results where (LastName = LastName) and (FirstName = FirstName). However in one table some of the FirstName values are shortened ie Pat is P etc. Is there some way (its prob using LIKE but i can't get it right) I can write the below query so that it returns values where the first letter of FirstName in one table equals the first letter of FirstName in the second table. SELECT tbl2.FirstName, tbl2.LastName, SALARY.[Employee No] FROM SALARY INNER JOIN tbl1 ON (SALARY.LastName = tbl2.LastName) AND (SALARY.FirstName = tbl2.FirstName) Cheers for all help.
View Replies !
Can A Sql 2005 Function Return More Than A Variable
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
View Replies !
MS SQL Function Return String - What Am I Doing Wrong?
Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB. CREATE FUNCTION [dbo].[getNextProjectID] () RETURNS varchar(10) AS BEGIN '''''''''''''''''''........................... DECLARE @vNextProjectID varchar(10) RETURN @vNextProjectID END Sub LoadNextProjectNumber() Dim vProjectID As String Dim cmd As New SqlClient.SqlCommand() cmd.Connection = sqlConn cmd.CommandText = "getNextProjectID" cmd.Parameters.Add("@vNextProjectID", vProjectID) cmd.Parameters("@vNextProjectID").Direction = ParameterDirection.ReturnValue cmd.ExecuteScalar() vProjectID = cmd.Parameters("@vNextProjectID").Value txtProjectID.Text = vProjectID cmd.Dispose() End Sub
View Replies !
How To Bind Return Value From Function Call
I have a function that I need to call from an execute sql task. I want to bind the return value from the function to an ssis variable. Can someone please show me an example of what the function syntax needs to look like in order for this to work? I know that with sp's, you need to explicitly state the column names. I have tried many things without success. Thanks
View Replies !
How Can A Function Return Multiple Rows
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is [dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK' now can i make a function which returns 'NL','UK','IN','GN','JK'
View Replies !
User Function Return Value Being Rounded
I have created the following user function :- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[GetBillPrice](@BillLineID int) returns float as begin DECLARE @BillPrice float SET @BillPrice = ( SELECT ((CAST(T_BillValueAndTax.BillValueAndTaxBillValue as float))+(CAST(T_BillTax.BillTaxTax1Value as float)) +(CAST(T_BillTax.BillTaxTax2Value as float))) FROM T_BillLine INNER JOIN T_BillValueAndTax on T_BillValueAndTax.BillValueAndTaxID = T_BillLine.BillValueAndTaxID_Price INNER JOIN T_BillTax on T_BillTax.BillTaxID = T_BillValueAndTax.BillTaxID_Bill WHERE T_BillLine.BillLineID = @BillLineID) return ABS(@BillPrice) end No matter how I try to return the @BillPrice value, the value is rounded to the nearest whole number. For instance if the value is calculated as 198.75 the value returned is 199.0 When I execute the function in Query Anlayser the correct value of 198.75 is returned. Any help in solving this would be greatly appreciated.
View Replies !
How To Return The Result Of An EXEC From A Function
Hi, I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute. This is my function: ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max) IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END RETURN @RTN END Each time I try " RETURN EXEC(@RTN) " or something similar I get an error. I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error. Anyone with any ideas?
View Replies !
Dynamic Sql Inside Function And Return Value
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working... Thanks ______________________________________________________________________ set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey from rt_'+@platform+'_CallLegs where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5)) exec @sql return @CallLegKey
View Replies !
Function To Return Greater Of Two Numbers
Greetings, Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing? Thanks
View Replies !
|
TRACKER
