SQL Server :: Stored Procedure With Scalar Function Slow?
Mar 11, 2011
I have the following sp that uses a scalar function to get the cost of a product
The procedure calculates profit of each bill,and using two tables
1 - Estimate Header
2- Estimate details
For each estimate invoice sp will compare items profit by comparing items rate with the rate it is purchased.I use a Scalar sql Function to get the Purchase Cost of each item in an invoice.This fuction cause the slow execution
Can this scalar function be replace with something else to get this sp execute faster....now it taking around 25 seconds there is only 1100 invoice in the database.
select BillNumber,sdate [Date],case when t1.Customer = 0 then 'Account Payable' else t2.AccountName end Customer ,case when t1.CashOrCredit = 0 then 'Cash' else 'Credit' end CashOrCredit,(select Sum(gross) from EstimateDetails where billNumber = t1.BillNumber)Gross,t1.netDisc,(select sum(TaxAmt) from EstimateDetails where billNumber = t1.BillNumber)Tax,t1.Ces, Amount,t1.roundOff,Paid Payment,
View 5 Replies
Similar Messages:
Nov 30, 2010
am new to entity frame work and having a hell of time building a stored procedure that insertsa value into a table using a Scalar stored procedure and returning the new ID which is a Unique Identifier. I am trying
[code]....
View 1 Replies
Nov 1, 2010
CREATE
FUNCTION dbo.ufnGetProductReport(@Pid
int)
RETURNS int
AS
BEGIN[code]....
This is the program. When im trying to execute it with Select * from dbo.ufnGetProductReport(1)
im getting an error Invalid object name ufnGetProductReport
View 2 Replies
Mar 22, 2010
VWD 2008 Express. Visual Basic. SQL Server 2005. I am trying to create a scalar function in SQL Server 2005 wherein I pass a string that respesents a portion of a WHERE clause in the function's SQL statement. I would be passing things like "GDate>1/1/2010 and GDate,1/1/2011" or "Year(GDate)=2010" When I try to build the function it errors out saying basically that the @WhereClause is not a boolean expression. Does anyyone know how I could accomplish this? The part it does not like is in red.
[Code]....
View 4 Replies
Jan 28, 2011
I could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried
I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011
How Would I accomplish this?
View 4 Replies
Feb 21, 2011
i want to get one value from function using case statement,i tried it but not work
CREATE FUNCTION [FATMS].[fnReturnByPeriod]
(
@Period INT
) [code]....
View 2 Replies
Oct 1, 2010
I have created stored procedure and student database and also asp.net application for asp.net page but it could not found stored procedure what is the mistake actually I don't no
View 7 Replies
Nov 23, 2010
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
View 5 Replies
Mar 28, 2011
I have 4 stored procedure returning scalar types (float, datetime) in sql Server 2005 and they're mapped using Entity Framework
When i click on add function import, no stored procedure is listed, even if they're included into model!
View 2 Replies
Apr 25, 2010
how can i use cursor in this function ?
ALTER FUNCTION [dbo].[GetCatIDChilds]
(
@CatID [code].....
View 1 Replies
Sep 1, 2010
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
RETURNS float
AS
BEGIN
RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
END
sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance
from business
[code]...
View 1 Replies
Jan 26, 2010
I have problem with a stored procedure and function that return the same shape, but as they are different methods LINQ won' t let me cast between the two. Please see code for the two SPROCs below (This is actually simplified to represent the problem, so please don't spend lots of time explaining alternative ways of writing the function/sproc.)
Table Valued Function
"getCategories()"
SELECT categoryName, categoryID FROM categories
SPROC "getUnusedCategories"
SELECT categoryName, categoryID FROM CategoryInstances cI
FROM dbo.SMAN_getCategories(@SiteMANID, @SectionID) c
LEFT JOIN @EntityResultsTable er
View 2 Replies
Apr 2, 2010
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
View 4 Replies
Dec 9, 2010
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 Replies
Jun 22, 2010
I am trying to write a function that can be called to run a stored procedure. I pass the stored procedure name, followed by as many parameters as I need to run the procedure. I am able to do this by using the params keyword, so my function looks something like this;
[Code]....
How can I determine what the data type of the parameter is? Maybe I need to alter the string[] part, above?
View 5 Replies
Feb 24, 2010
I have a table with recursive relation,a function and a sp for readig data from that table:
[Code...]
When I execute sp,I got this error:Maximum stored procedure,function,trigger or view nesting level exceeded (limit 32).
View 9 Replies
Jan 18, 2010
Does anybody if it is possible that a stored procedure returns rows which is the result of the execution of another sp? Something like..
[Code]....
View 11 Replies
May 13, 2010
i want to return output parameter from 1 storeprocedure. into another stored procedure.
View 7 Replies
Apr 6, 2010
i am calling a scalar function from a stored procedure. Does using user defined scalar finction has a negative impact on performance.
View 1 Replies
Nov 18, 2010
I have a sql query something like below.
create procedure test
(
@param1 nvarchar(max)
)
as
begin
select * from table where column1 in (@param1)
end
Now i need to pass the value of @param1 from my .net application in such a way that the above query works.
how from my vb.net code i can pass a value which is similiar to below
'1','2','3'
Main question is how do i structure value of parameter like above example from my .net application
View 3 Replies
Nov 27, 2010
Server Error in '/staff' Application. Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. 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: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Source Error:
[Code]....
Stack Trace:
[Code]....
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
View 31 Replies
Oct 29, 2010
I am trying to create a stored procedure that filters data from sql server by date but i get this error:
Conversion failed when converting datetime from character string.
my stored procedure is like:
[Code]....
the parameters are passed to the stored procedures as dateTime variables from c# code...
View 10 Replies
Feb 27, 2011
The following stored procedure updates the value in LeaveTransaction table.
[code].,...
My Question here:
I have written the above trigger,which would update the value for the corresponding records in another table ie) LeaveCumulative table. The trigger is not updating the value in the designated rows in the leavecumulative table. Whereas when I excute the same update command separately in the Query Editor window the trigger is working fine.
View 1 Replies
Aug 9, 2010
Here is my stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[sp_GetNextSeq] (
@p_NextSeqNo int = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
Generate Next Sequence Number
[code]...
View 7 Replies
Jan 14, 2010
How do I define @UserName in my function:
[Code]....
I believe "UserName" = Me.User.Identity.Name
but I don't know the proper way to add it to the function.
View 6 Replies
