MS SQL Function Return String - What Am I Doing Wrong?

Jun 6, 2005

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 4 Replies


ADVERTISEMENT

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


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 5 Replies View Related

Derived Column Return WRONG Datatype.

Apr 18, 2006

Hi, friend,

l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

View 4 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

Find In String And Return Part Of String

Mar 21, 2007

I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View 7 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Please Help Me To Optimize This Sql Query, It Takes 28 Seconds To Return Result. Please Give Me A Tips Where I Went Wrong?

Aug 21, 2006

SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;

View 6 Replies View Related

Analytic Function Returns Wrong Values With AVG

Feb 4, 2014

I have following select-statement:

select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2

My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].

How can i achieve that this "erroneous" values are not inserted or rather are shown as null.

View 2 Replies View Related

Table Scalar Function Syntax. . How Wrong And How Far Am I?

Aug 3, 2006

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION DetailedView

AS

BEGIN

Declare @fieldname varchar(10)

Declare @stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @stmt = 'Select pono, myid, billtype'

Open Fields

Fetch Next From Fields Into @fieldname

While @@Fetch_Status = 0 Begin

Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname

Fetch Next From Fields Into @fieldname

End

Close Fields

Deallocate Fields

Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '

return Exec(@stmt)

END

View 3 Replies View Related

Whats Wrong With This String? Getting An Error

May 12, 2008

Hello All, i am trying to connect to SQL Database using OleDb connection, but i keep getting error. here is my connection code. string strCon = "Provider=SQLOLEDB.1;Data source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"; OleDbConnection con = null; OleDbCommand cmd = null; con = new OleDbConnection(strCon); con.Open(); // deleting the exisitng one//
cmd = con.CreateCommand();
cmd.CommandText = "DELETE FROM Detail"; cmd.ExecuteNonQuery(); con.Close(); con.Dispose(); con = null;this is the error i am getting ..........No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).Source Error:


Line 43: OleDbCommand cmd = null;Line 44: con = new OleDbConnection(strCon);Line 45: con.Open();Line 46: // deleting the exisitng one//Line 47: cmd = con.CreateCommand();   can some please tell me where i am making the mistake. I really appreciate it.Thanks 

View 4 Replies View Related

Reporting Services :: Wrong Total Values Are Coming With SUM Function

Sep 7, 2015

Create report with SharePoint list. Columns are displaying on the report I used out of the group SUM function and it is group by report. one group total is showing correct total and other groups are showing wrong total and showing too much big value even there is whole column is empty.where value is not in column I am using "-" with IIF function. these columns are calculating by using date difference function between two date columns. these values coming with minus value . so ABS function is also using.

Columns calculation expression:

=IIF(ISNOTHING(Fields!DateCAPackage.Value)
OR (ISNOTHING(Fields!Date_CA_Application.Value)) ,
"-" , CINT(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value))))

Total calculation expression:

=Sum(ABS(DateDiff(
"d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value)))

View 5 Replies View Related

Serious Perfomance Problem Using UDFs As Function Arguments. (I Suspect A Bug, Show Me I'm Wrong!)

Nov 29, 2007


(From an exchange originally posted on SQLServer.com, which wasn't resolved...)

To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)

But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:

declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)

[187 CPU, 16103 Reads, 187 Duration]


Call to same table returning UDF, using scalar UDFs in parameters:

SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.

My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)

Can anyone shed some light on this strange (to me) behavior?


----- (and later, from me)---
(I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)

View 8 Replies View Related

Getting A Return Value From A Function.

Oct 6, 2005

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 4 Replies View Related

Function Return Value

Jun 13, 2004

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 11 Replies View Related

Return @@identity For Another Function

May 14, 2004

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 4 Replies View Related

I Need The Function To Return A Srting

Jul 21, 2004

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 3 Replies View Related

How To Return 0 Instead Of Null When Using A Sum Function?

May 10, 2005

Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx

View 5 Replies View Related

Table Return Function

Feb 14, 2008

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 3 Replies View Related

Why My Function Wont Return The Value

Mar 9, 2008

//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 1 Replies View Related

How To Return Varchar(MAX) From A CLR Function?

May 23, 2006

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

View 5 Replies View Related

Catch The Return Value From A Function

May 17, 2008

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 10 Replies View Related

Question Regarding A Function Return

Apr 7, 2008

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 4 Replies View Related

Return Dat In String

Jul 25, 2007

hi! how would i return the current day in string format?

the output should be sun,mon,tue,wed,thu.. etc?

pls. help.. thanks

View 3 Replies View Related

Return Into One String

May 5, 2006

I have a table with a column called AccessTypes which contains a singleletter.I want to return these accesstypes from a query into one string. e.g.if there were 3 entries of A, S and DI want to select them and instead of returning 3 rows, I want just 1string like "ASD"can it be done?

View 3 Replies View Related

Can A Sql 2005 Function Return More Than A Variable

Jul 30, 2007

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 3 Replies View Related

Return Multiple Values From A Function

Jun 19, 2007

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 4 Replies View Related

Function To Return Greater Of Two Numbers

Jun 2, 2008

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 1 Replies View Related

Return 4 Output From A Function In Sqlserver

Jun 16, 2008

Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.

View 7 Replies View Related

Function To Return Head Of Parent

Jan 19, 2014

I have an existing function and need to alter function to give result of the parent-description until its parent is reached.

--CREATE TABLE

CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]

[Code] ....

View 3 Replies View Related

Dynamic Sql Inside Function And Return Value

Apr 10, 2007

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 3 Replies View Related

Function With Expression To Return Values

May 15, 2007

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 4 Replies View Related

How Can A Function Return Multiple Rows

Nov 14, 2007

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 1 Replies View Related

User Function Return Value Being Rounded

Jan 3, 2008



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 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved