Custom Function In SQL Statement?
Mar 24, 2005
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work?
All help is greatly appreciated!
Lynnette
View 7 Replies
ADVERTISEMENT
May 9, 2006
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
View 1 Replies
View Related
Mar 16, 2007
I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?
Thank You!!
View 4 Replies
View Related
Apr 4, 2006
hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.
eg
Table MemberDetail
MemberID WorkingDay
M1234 12
M1234 10
M1555 02
M1555 15
View 5 Replies
View Related
Feb 13, 2008
Greetings,
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
Rob
View 5 Replies
View Related
Apr 26, 2007
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double
if pFields !FA00902_AMOUNT.Value > 0 and
pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
Thanks in advance,
Buster
View 1 Replies
View Related
Mar 18, 2014
I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).
View 9 Replies
View Related
Apr 11, 2015
I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
View 2 Replies
View Related
May 23, 2007
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
View 3 Replies
View Related
Mar 25, 2008
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View 3 Replies
View Related
May 12, 2007
Hi All,I need help in creating a function in VB for my ASP.NET application where I want to add records to database on the first day of every month.I have got no idea about what I have to do for achieving this goal.Its basically for a customer based application where Interest will be paid into customers' account and I need to implement this for every customer on 1st day of every monthThe thing I am not sure about is how can I get the application to add a record for each customer on the first day of each month, i.e. how can I get the application to check that its 1st day of month and then the application adds records automatically for each customer based on my specified rules.If any of you could help me with this, I'll really appreciate it.Thank you.
View 5 Replies
View Related
Feb 16, 2007
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
View 7 Replies
View Related
Aug 4, 2007
Hello,
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Can anyone help me out? I am not that familiar with VB.
Thanks,
Deb
View 2 Replies
View Related
Jul 20, 2006
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this:
SELECT filenameFROM pictableWHERE (@searchby LIKE @searchwords)
It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works:
SELECT filenameFROM pictableWHERE (keywords LIKE @searchwords)
Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail.
Thanks in advance.
View 6 Replies
View Related
Jan 25, 2008
Hi there
I'm relatively new to Reporting Services and .Net development so bear with me if I've missed something obvious.
I've set up Reporting Services to run under forms authentication successfully.
I want to filter the data (both within Report Builder Models and .rdl files) to the company the user belongs to.
To avoid joining the users table to all other tables on the companyID I'm wondering if it's possible to create a custom funciton which could use the UserID to lookup the companyID and then pass this through as a parameter, and have this "GetCompanyID" function available when building Models and/or reports...???
When building a model the Information Functions available are GETUSERID and GETUSERCULTURE. Is it possible to modify how these work and/or create additional functions here???
Thanks in advance for your help.
Cheers,
Todd.
View 3 Replies
View Related
Aug 10, 2015
I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:
SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code
The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.
For example:
Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365
Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.
Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.
I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.
People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…
The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.
View 5 Replies
View Related
May 8, 2008
Hi,
I have a table called emp, having 2 field name & sex
values are:
name sex
a 1
b 2
c 1
now i want to display the values in above table as like below...
name sex
a Male
b Female
c Male
How to do that...?
View 11 Replies
View Related
Aug 12, 2015
I want to add a custom column in a select statement that has a value to true or false based on other criteria.
SELECT [ID], [Name], [Description], [EmpID], [Employed] FROM [Employees]
Now, in the above example there is no [Employed] Column in my table but I want it to show true or false based on whether or not [EmpID] equals a certain value.
View 6 Replies
View Related
Jul 28, 2007
Hi,
I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier."
Here is my sql statement:
SELECT TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME, TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST, TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNITFROM TBLPROJECTS INNER JOIN TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID
I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!
View 5 Replies
View Related
May 8, 2008
PLEASE PLEASE PLEASE......
I did not get a single response for the last 6 hours... And during this time I was searching and trying to understand the problem but I am really stuck. If this is the wrong forum to ask this question, please redirect me. Really begging for replies...[:'(]
If I use the custom SQL statements in SqlDataSource, the application runs fine within the development environment (VS2005) but errors out if I publish the web site and access outside of the environment. In order to find-out the problem, I made the following test:
I created a select statement in one SqlDataSource to fill-in a GridView. I used the exact same statement to create a stored procedure and used that SP in second SqlDataSource and I fill a second GridView. When I debug or run the application, both grids are filled OK and everything works fine. However, when I publish the web site and try to do same only the stored procedure works fine and when I try to fill the grid using the built-in SQL, the page gives error. The error mesage is as follows when I use the address 'localhost':
Server Error in '/' Application.
The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.
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.SqlClient.SqlException: The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322.......da da da .......
If I access the page using the IP address the message chages to below but it is not the issue, I just give it if it helps to find the problem:
Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
My SqlDataSource s are like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="TestRemoteAccess" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="Param1" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="SELECT FirstName, LastName, Business FROM Contacts WHERE (ContactID = @Param1)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox2" Name="Param1" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
Environment: SQL Server 2005, VS2005, Vista
View 1 Replies
View Related
Mar 8, 2007
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
View 5 Replies
View Related
Mar 12, 2004
i cant get this statement to run on sql server 2000.. does anyone know why?
SELECT FirstName, LastName, EmpNum, JobTitle, Discipline, City, IIf(State = 'FL', 'Florida', 'Some Other State') AS StateTest
FROM dbo.Employees
it works on MS Access. is there a new name for the IIF Function on sql server
View 1 Replies
View Related
Oct 11, 2004
I am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?
thanks
View 2 Replies
View Related
Feb 19, 2003
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
View 2 Replies
View Related
Oct 26, 2012
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code:
Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
View 13 Replies
View Related
Jan 21, 2004
CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @PersonID integer
set @PersonID=0
if @SSN>0 and @SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN
End
ELSE
if @SSN is not null
BEGIN
IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @NamePrefix and strFirstName=@FirstName
and strMiddleName=@MiddleName and strLastName=@LastName
and strNameSuffix=@NameSuffix and lngSSN=@SSN
End
ELSE
if @LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@LastName
and lngSSN = @SSN
END
END
return (@personID)
END
I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
Thanks
View 10 Replies
View Related
May 6, 2008
Hello,
I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!
The enc_timestamp is my date field.
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;
Thanks in advance,
Sherri
View 17 Replies
View Related
Jul 19, 2006
Is it possible to create a function that deletes records from a table ?
CREATE FUNCTION F_TSImported_Delete()
returns int
as
Begin
delete from ts_imported
return 0
end
GO
This throws error like this:
Invalid use of side-effecting or time-dependent operator in 'DELETE' within a function.
View 6 Replies
View Related
Oct 15, 2007
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters.
Is there any way to solve my problem??
Jaimin
View 4 Replies
View Related
May 22, 2008
Hi,
I've got a complex UDF I need to call twice in a select statement, as shown below:
SELECT
dbo.myFunction(colName),
dbo.myFunction(colName) * 2
FROM
tableName
The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).
Is there any way I can write the query so that dbo.myFunction is only executed once?
Thanks for your help,
Andrew
View 5 Replies
View Related
Feb 25, 2008
Hi,
I have a Query some thing like this,
Select Table1.Code, 'field2' = CASE
WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code)
WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code)
WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code)
END
FROM Table1
Do I need to use Function instead of CASE for better performance ?
Regards
Mujeeb
View 5 Replies
View Related
Aug 23, 1999
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
View 2 Replies
View Related
Apr 26, 2005
Dear Friends.
I m trying to use the insert statement with in the function !
and i m getting this errror !
Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16
Invalid use of 'INSERT' within a function.
Please help me how to rectify it and how i can use the Insert statement with in the function !
Here is the code for the function.
create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as
begin
Declare @valCost integer
Declare @TotService integer
Declare @TotParts integer
Declare @TotLabour integer
Declare @TotTravel integer
Declare @TotSubContract integer
select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no
insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1)
return (@valCost)
end
View 2 Replies
View Related
