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 Distance
In 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 Complete Forum Thread with Replies
Related Forum Messages:
'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 !
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes. Here are the task steps. [Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step. [Execute SQL Task] - Log an entry to a table indicating that the import has started. [Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works. [Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile. If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS. If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction. CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate] /* The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate. If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset. Otherwise it returns a FALSE value in the IsNewFile column. Example: exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0 */ @ProcessName varchar(50) , @FileCreateDate datetime , @IsNewFile bit OUTPUT AS SET NOCOUNT ON --DECLARE @IsNewFile bit DECLARE @CreateDateInTable datetime SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName) BEGIN -- The process exists in tbl_ImportFileCreateDate. Compare the create dates. IF (@FileCreateDate > @CreateDateInTable) BEGIN -- This is a newer file date. Update the table and set @IsNewFile to TRUE. UPDATE tbl_ImportFileCreateDate SET FileCreateDate = @FileCreateDate WHERE ProcessName = @ProcessName SET @IsNewFile = 1 END ELSE BEGIN -- The file date is the same or older. SET @IsNewFile = 0 END END ELSE BEGIN -- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE. INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate) VALUES (@ProcessName, @FileCreateDate) SET @IsNewFile = 1 END SELECT @IsNewFile The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings. General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1 Result Set is empty. Expressions is empty. When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure. When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives? The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing. Thanks for your help.
View Replies !
Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer. Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2) It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2. Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks! Here is the code of the function in the book: /* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
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 !
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 !
Accessing The Return Variable Of An Insert
When I create a tableadapter to do an insert it added SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY()) at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field. How would I reference the variable in my .cs file? My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter(); ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id); Thanks for any help
View Replies !
How To Return No Rows If A Variable Is NULL
Hello, I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure. I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out). I'm not very familiar with writing stored procedures, so any help you can give me is appreciated. Thanks!
View Replies !
Stored Procedure Return Value And A Variable
hi everybody, I have two stored procedures lets say A and B and a variable inside stored procedure A lets say @BReturn, now, what is the syntax to execute B inside A and to store its returned value in @BReturn? any help is greatly appriciated. devmetz.
View Replies !
Return Recordset And A Variable From Procedure
Hi I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP?? Waiting for someone to shed a light of hope. Thanx a lot
View Replies !
Dynamic SQL Return Table Variable
Is it possible in SQL 2000 to return a table variable from dynamic sql? We need to have some code that looks kind of like this: declare @qry varchar(8000) declare @sourcetable varchar(100) -- name of source table declare @mytable table (ID_Num int identity(1,1), Child_Key varchar(100)) set @sourcetable = (select tablename from tblConfig where app = 1) set @qry = 'insert into @mytable select * from @sourcetable' execute (@qry) if (select count(*) from @mytable) > 0 begin 'insert into myFinalTable select * from @mytable where blah blah blah I can get the first part to work by declaring the table variable (mytable) in the dynamic sql, but I can't figure out how to return the table object.
View Replies !
Setting SQL_VARIANT_PROPERTY Return To A Variable
Why does this not work? declare @ret varchar(50) DECLARE @X SQL_VARIANT set @X=10 SET @ret=select SQL_VARIANT_PROPERTY(@X,'BaseType') Just trying to assign the SQL_VARIANT_PROPERTY return value to @ret and it issues an error: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. But I don't want to convert I just want to assign the result to the variable. what's confusding is that you can do this: if(select SQL_VARIANT_PROPERTY(@X,'BaseType'))='int' So I assume I should b able to do the above. --PhB
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 !
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 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 !
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 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 !
Return Variable Name As Part Of Select Statement.
hey all, I have the following query: ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle] AS BEGIN DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC ) DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id ) SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count FROM article JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name HAVING article_id = @article_id END GO as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable. I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id. But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id. And i cant add a join, because it would return the amount of rows of the comment... unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement? Cheers
View Replies !
How To Get A Variable Value Return Back From A Child Package ?
I have a parent package (i.e. P) and a child package (C). I was able to easily pass a variable value from P to C. And C did computations and get a result (i.e. integer) and stored this integer to a variable (x). Now I want the value of "x" to be passed back to P and store in P's variable (say Px). Anyone knows how to ? Thanks Steve
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 !
Creating Sql-clr Function With Void Return Type
hi to all, i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void) i am creating sql function using SQL script CREATE FUNCTION dbo.Amex () RETURNS NULL AS CALLER WITH EXECUTE AS EXTERNAL NAME [AMEX].[UserDefinedFunctions].[Function1]; GO but i am getting error. there is something wrong in my sql syntax..can anybody help me? when creating funtion having signature public static void Function1() i am getting error....................... Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1 can anybody help me? thanks in advance Chetan S. Raut
View Replies !
Return NULL From A CLR Scalar-Valued Function
Hi, I have an assembly that contains the following function: Public Class Lookup <SqlFunction()> _ Public Shared Function MyTest() As Integer Return System.Data.SqlTypes.SqlInt64.Null End Function End Class Then in SSMS: CREATE ASSEMBLY IRS_MyTest FROM '\machine empmyAssembly.dll' GO CREATE FUNCTION dbo.MyTest() RETURNS INT AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest GO when I run: SELECT dbo.MyTest() the following is returned: Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest': System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. System.Data.SqlTypes.SqlNullValueException: at System.Data.SqlTypes.SqlInt64.get_Value() at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x) at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest() Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal. Thanks, Jan.
View Replies !
Table-valued Function Return Error
I have this tsql Declare @IDtable table(id uniqueidentifier, [Value] varchar(50)) Declare @iDoc Int exec sp_xml_preparedocument @iDoc Output, @Data insert into @IDtable(id,[Value]) Select * From OpenXML(@iDoc, '/listing/Item', 2) With (Id VarChar(40), Value varchar(20)) exec sp_xml_removedocument @iDoc but when I tried to right table value function of it so I can use it many place with just change paremeter value like this ALTER FUNCTION [dbo].[splitXMLvalue](@editors xml) RETURNS @etable table(id uniqueidentifier, [name] varchar(50)) AS BEGIN Declare @iDoc Int exec sp_xml_preparedocument @iDoc Output, @Data insert into @IDtable(id,[Value]) Select * From OpenXML(@iDoc, '/listing/Item', 2) With (Id VarChar(40), Value varchar(20)) exec sp_xml_removedocument @iDoc --select id as parentId, [Value] as parentValeu from @IDtable RETURN END it gave me this error Msg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function. can any one help me what I'm doing wrongthanks
View Replies !
Function For Return Element List From A Query
Hello, I have do a sql function for return a list of element from a query send in variable. When I test the function on self I have no problem. But when I use the function in a sql query I have problem. example : Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL, dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') AS V_PROCESS_ITEMS FROM APPLI_SUPPLIER INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6)) This is the error : Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24 Only functions and extended stored procedures can be executed from within a function. When I do an exec of the function I have this problem : Code Snippet exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))') Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))' This is the function Code Snippet CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM] (@QUERY AS VARCHAR(3900)=null) RETURNS varchar(8000) AS BEGIN -- Insert statements for procedure here declare @v_List_ITEM as varchar(8000) set @v_List_ITEM='' if @QUERY is not null Begin declare @cur_Lect_ITEM CURSOR; declare @FUNCTION AS NVARCHAR(4000); Declare @ITEM as VARCHAR(255); SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor' EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output fetch next from @cur_Lect_ITEM into @ITEM while @@fetch_status=0 begin set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM fetch next from @cur_Lect_ITEM into @ITEM end deallocate @cur_Lect_ITEM SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '') set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3) End RETURN @v_List_ITEM END Can you help me please? Thank you
View Replies !
Need To Return X Rows But Select Top Doesn't Work With Variable
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of. Thanks!
View Replies !
Code A Function To Return A Dataset In Which There Are Two Tables And Relationship
I used a function to create dataset as below: Public Function GetSQLDataSet(ByVal SQL As String) As DataSet ...... MyConnection = New SqlConnection(MyConnectionString) MyCommand = New SqlCommand(SQL, MyConnection) MyDataSet = New DataSet MySQLDataAdapter = New SqlDataAdapter(MyCommand) MySQLDataAdapter.Fill(MyDataSet) ...... End function It works fine. How to code a function to return a dataset in which there are two tables and relationship?
View Replies !
How To Write A Function That Will Return True If User Is In A Certain Role?
I have a SQL database with an Access front end. In the database Ihave a read only and a read write role. When a read only user opensthe database I want all the fields on the form to be locked so thatthe user will not try to change data and get an error from the server.Right now I am doing that with a table. But it's a hassle to have tomaintain a table when if I could answer the question is the currentlylogged in user in the read write role?My server is running SQL Server 2000. So I was wondering if I couldwrite a function to do this? The function would take the role beingchecked as a text parameter and return true if the currently logged inuser is in that role or false if he/she isn't.
View Replies !
|
TRACKER
