I've got a user-defined function that I'd like to call and return a string to a textbox that is in my header.
The rows of the report are working with with a select statement.
I have something that I'd like to have in the header and am not sure how to get it.
In the properties of the textbox, I tried =dbo.udf_myfunction(), but that didn't work.
So how do you assign the textbox value from a user-defined function?
I'm trying to create a reprot with 30+ datasets/querys. My report is made up of tables (one for each dataset), and it starts a new page whenever a new dataset is being displayed. I would like to have a header on every page, and have it display a custom name for the dataset on that page. How do I approach this?
Thanks for any answers or leads.
Hi,
View 1 Replies View RelatedWe run 2014 enterprise. Can parts of the phrase that goes into a text box be forced to go to designated lines in that box? For instance if I have the phrase aaaaa bbbbb ccccc ddddd going into my text box , can I force a carriage return after each word (eg aaaaa) within the textbox?
View 7 Replies View Related
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,
Hi,
I need to add carriage returns into a text box of 3 lines.
The data is pulled from the BD so the "cariage return" symbol need to be in the database field.
I know that some reporting software give the option to add HTML code into textbox but I did'nt find out about ms reporting 2000.
Thanks,
Marco
Hi Newbie here,
i am trying to make a highscore table and i would like when the user enters a score for them to be able to see what position they are in the database without having to take out all the data. i have looked at a few methods and was wondering which one is the best solution and how i would go about implementing it
count()
rank()
Indexes
Any response would be greatly appreciated
Puzzled
Good morning, all,
OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.
I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.
My dataflow has the following components:
An Excel Source, containing my test data, consisting of just one row of data,
which points to a
Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.
which points to a
Data Conversion Transformation, which takes care of all the datatype conversions
which points to a
OLE DB Command, which contains the following as the SQL Command:
exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
In the OLE DB Command, I have mapped returnValue, my user variable to @RETURN_VALUE.
Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.
How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.
Thanks,
Kathryn
I'm trying to create a flat file that has a header like:
/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data
where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.
Below is a copy of the the property as it is written in the package xml:
<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>
Any help is appreciated.
-dotnetwiz
eh guys ma kind of newbie n tis ... k like wrote a function like
CREATE FUNCTION [dbo].[GetDetailText]
{
@PackingID int,
@PackingType int
}
RETURNS varchar(100)
AS
BEGIN
DECLARE @DetailText varchar(100)
IF (@PackingType = 1)
BEGIN
SET @DetailText = (SELECT
'Tickness' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.thickness) + '' + CONVERT(varchar, KF_Unit_3.Symbol)
+ ',' + 'Width' + ': ' + CONVERT(varchar, dbo.KF_PackMaterial.width) + '' + CONVERT(varchar, KF_Unit_1.Symbol)
+ ',' + 'InnerDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.india) + '' + CONVERT(varchar, KF_Unit_5.Symbol)
+ ',' + 'OuterDia' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.outdia) + '' + CONVERT(varchar, KF_Unit_6.Symbol)
+ ',' + 'Recycle' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.recycle) + ', ' + 'Shade' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.shade)
+ ',' + 'Weight' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.weight) + '' + CONVERT(varchar, KF_Unit_4.Symbol)
+ ',' + 'Perforation' + ':' + CONVERT(varchar, dbo.KF_PackMaterial.perforation) + ' (' + CONVERT(varchar, dbo.KF_PackingMaterial.PackingTypeName)
+ ')'
FROM dbo.KF_CartonImageList RIGHT OUTER JOIN
dbo.KF_PackMaterial INNER JOIN
dbo.KF_PackType ON dbo.KF_PackMaterial.type = dbo.KF_PackType.PackID INNER JOIN
dbo.KF_Unit KF_Unit_1 ON dbo.KF_PackMaterial.widthunit = KF_Unit_1.UnitID INNER JOIN
dbo.KF_PackingMaterial ON dbo.KF_PackMaterial.packmaterialid = dbo.KF_PackingMaterial.Packingid LEFT OUTER JOIN
dbo.KF_Color ON dbo.KF_PackMaterial.ColorId = dbo.KF_Color.ColorId LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_6 ON dbo.KF_PackMaterial.outdiaunit = KF_Unit_6.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_5 ON dbo.KF_PackMaterial.indiaunit = KF_Unit_5.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_4 ON dbo.KF_PackMaterial.weightunit = KF_Unit_4.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_3 ON dbo.KF_PackMaterial.thicknessunit = KF_Unit_3.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_2 ON dbo.KF_PackMaterial.lengthunit = KF_Unit_2.UnitID LEFT OUTER JOIN
dbo.KF_Unit KF_Unit_7 ON dbo.KF_PackMaterial.heightunit = KF_Unit_7.UnitID ON
dbo.KF_CartonImageList.CDImageId = dbo.KF_PackMaterial.CDImageId
WHERE (dbo.KF_PackMaterial.id=@PackingID AND dbo.KF_PackMaterial.packmaterialid=@PackingType))
END
return @DetailText
END
buts its throwing syntax error like
"[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
"
... ne idea ????????
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>
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
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
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
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
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.
//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.
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
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.
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?
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 RelatedCannot 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
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...
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
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.
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] ....
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
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.
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'
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.
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
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?
I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?
fields!textbox1.value doesn't work..what does?