Stored Procedure To Calculate Month Salary(urgent)
Aug 30, 2005i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
View 2 Replies
ADVERTISEMENT
Update The Salary Of Each Manager To Be Double The Average Salary Of The Employees He/she Manages
Mar 23, 2006create table employee(empid int,empname varchar(20),managerid int notnull, sal int)insert into employee values(1,'ranga',22,5000)insert into employee values(2,'satish',22,8000)insert into employee values(3,'sunil',11,4500)insert into employee values(4,'sridhar',22,2000)insert into employee values(5,'ramesh',33,12000)insert into employee values(6,'srini',22,16000)insert into employee values(7,'sashi',33,54000)insert into employee values(8,'rajani',22,71000)insert into employee values(9,'praveen',11,6060)insert into employee values(10,'bhaskar',22,11120)insert into employee values(11,'baba',33,9000)create table employment (managerid int,managername varchar(20),sal int)insert into employment values(11,'rob',2500)insert into employment values(22,'babu',5000)insert into employment values(33,'ram',6000)now my problem isUpdate the salary of each manager to be double the averagesalary of the employees he/she managespls helpsati
View 3 Replies View RelatedSQL Server 2012 :: Find All Employee Whose Salary Sum Is 80% Of Sum Of Salary Of All Employees
Aug 14, 2014Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.
View 4 Replies View RelatedCalculate And Return % In Stored Procedure
Apr 22, 2007This is my SP:SELECT CAST(id AS varchar(10)) + ' - ' + UserName AS 'User List', Scanned, Scripts AS 'Total Scripts', Processed,CAST((Processed/ Scripts)* 100.0 as int) AS 'DONE (%)'FROM tblworkQueueProcessed and Scripts are both DataTypes of intMy DONE(%) column comes back with 0Any idea where I am going wrong?
View 2 Replies View RelatedHow To Calculate Expiry Date Using Stored Procedure
Oct 16, 2004Hi, i'm trying to calculate on the expiry date for product which include the start date and end date.
the product can't b displayed if the start date haven't reached the date, besides, it oso can't b displayed if it has expired.
the storedprocedure tat i've written is:
WHERE
DateDiff(day, StartDate, getDate())<=0
AND
DateDiff(day, EndDate, getDate())>=0
i wonder why it can's show the result!!! is the code correct?
Calculate Minutes In Stored Procedure Using Datediff
Apr 15, 2008I need to create a stored procedure to select records that cal_callin_tm (this is a datetime field in table) is less than 10 minutes of the current system datetime. I want to update cal_callin_tm field with current system time and pass back the cal_assign_off_p field to my VS 2008/C# windows form. I'm new at SQL code, I know the basic stuff but this requires more knowledge than I have. I need from you experts the best practice to accomplish coding the SQL. I have some junk code below I have started with, help me out.
Thanks a bunch,
CREATE PROCEDURE SP_DpOffCallIn
(
@offname varchar(40) = null OUTPUT,
)
AS
SELECT * from dpcalldtl
where cal_callstat = 'ON SCENE' and (datediff(minute,0,cal_callin_tm) < datediff(minute, 0, currentdate)
If minute > 10
BEGIN
UPDATE cal_callstat
SET
cal_callin_tm = @dt,
@offname = cal_assign_off_p
END
RETURN
Transact SQL :: Calculate Minutes In Stored Procedure Using Datediff
May 9, 2015I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user only if he makes the 3 incorrect entries within 30 minutes.
I created a field named "FirstEntryTime" of type datetime that saves the date of the first incorrect entry. I tried to make an if statement:
if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30)
Begin
Update myTable set ...
End
T-SQL (SS2K8) :: Stored Procedure - Calculate Closest Date For Manufacturing
May 5, 2015I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.
What I have:
- The BOM (bill of materials) with the needed quantity for production
DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)
SELECT 1, 10
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)
SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
How To Create Stored Procedure Change Value Once A Month From 1,2,3,4 TO 2,3,4,1
May 8, 2008help
i need to create stored procedures that run once month
change value (UPDATE) once a month in one table the values from 1,2,3,4 next month 2,3,4,1 ..... 3,4,2,1 .......4,1,2,3 ..... 1,2,3,4
i have this code
Code Snippet
SELECT empID, location, ISNULL(NULLIF (( location + DATEDIFF(mm, location_date, GETDATE())) % 4, 0), 4) AS new_location
FROM dbo.empList
like this the employee go from one location once amonth to new location
table on this month (5)
empid location location_date
--------------------------------------------------------------
1111 1
222 2
333 3
444 4
5555 3
666 2
777 3
888 4
table on next month (6) (after a month)
empid location location_date
-------------------------------------------------------------------
1111 2
222 3
333 4
444 1
5555 4
666 3
777 4
888 1
TNX
Firing An Append Query(stored Procedure) On The First Of Very Month
Mar 4, 2002I need several stored procedures to fire on the 1st of every month. I was told my another developer I can possibly use SQL sever agent.
Any ideas????
Code To Calculate Percentages By Month?
Mar 30, 2015I have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.
How can I correct this so that it shows the right % for each of the flags for each month?
Select
sk.Period Period,
sk.[Invoice Flag],
count(*) * 100.0 / Sum(count(*)) Over () Percentage,
count(*) Total1
From
(Select wh.worknumber [Work Order],
[code].....
Output at present:
Period Invoice Flag Percentage Total1
--------------------------------- ------------------------------- --------------------------------------- -----------
2015-02 Invoiced after 7 days 5.704697986577 136
2015-03 Invoiced after 7 days 2.097315436241 50
2015-02 Invoiced with Customer Approval 59.563758389261 1420
2015-03 Invoiced with Customer Approval 32.634228187919 778
Calculate The SUM For 1 Month & 5 Days -display
Dec 18, 2007Hi All,
I am new to SQL programming, i have only a fair knowledge on sql programmin.So, I apologies for any silly questions-
I have a Table1 which contains
C1-acountid
C2-date
C3-grossamount(postivie and negative decimal values)
C4-netamount
Table2
C1-groupid
C2-accountid
Table 3
C1-groupid
C2-groupname
I need create a store procedure to retrieve the following on a single table
1. top 10 losers of the day i.e. 10 AccountIDs with the greatest negative Grossamount for the day
NOTE:These 10 AccountIDs may be sam or differing each day
2.sum of Netamount for each AccountIDs listed in STEP 1 since the beginning of the month.
NOTE:These 10 AccountIDs may be same or differing each day and each day sum of netamount should be from beginning of the month till current date.
3.Sum of Netamount for the last 5 days for each accountids in STEP1
The result set must contain the columns as below
C1-accountid
C2-date
C3-net loss for 10 losers on the current date since the beginning of the month
C4-Sum of Net for last 5 days
C5-groupname
Please help me.
Below is the script that i have written, without calculating the sum
(
select top 10 a.date, a.accountid, a.gross, a.net, c.groupname
from GBSys_Sum_EOD a
join server2.dbname.dbo.table2 b on a.accontid=b.accounit=id
join server2.dbname.dbo.Table3 c on b.groupid=c.groupid
where date> getdate()-1
and gross< (floor(-00.00)) order by gross
)
Thanks in advance.
Transact SQL :: How To Calculate Number Of Employees In Each Month
Jun 17, 2015I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year
May 15, 2007hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
SQL Server 2012 :: Calculate Customers Age And Value By Month And Year?
Sep 22, 2014What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
URGENT Help --------- Stored Procedure
Oct 21, 2004Hi !!
We are in a situaltion.... and don't have any answer...
We have 3 stored procedure. We want to write output of all the 3 stored procedure into one text file.. How do we do this?
We thought about DTS but its not solving our problem...
Stored Procedure (Urgent)
Jun 14, 2006I need to display the records from a start record number to an end record number. I need a stored procedure for this. The procedure should accept the query, start record number and the end record number as parameters. The procedure should be optimized such that it would work fast with even 1 lac records or more in the database table. Please help.....
View 1 Replies View RelatedSQL Server 2012 :: Calculate Total On-peak And Off-values For A Month
Nov 25, 2014SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
Joining Two Tables - Calculate % Of Budgets Used For Each Waver Service By Month
Aug 9, 2013I have written two programs; One which sum the total cost for services provided and one which pulls in the projected budgets for the services. The budgets can be renewed once a year or every six months. I’m having trouble joining to the programs together so I can calculate the % of budgets used for each waver service by month.
Projected Budget Program
SELECT
ChildID,
WAIVER_SERVICES,
START_DT,
END_DT,
[Code] .....
The Budget data set I bring back looks something like this:
CREATE TABLE Budget (
ChildID int,
WAIVER_SERVICES int,
START_DT date,
END_DT date,
RATE_PER_UNIT_OF_SERVICE int(30),
[Code] ....
Notice the START_DT and END_DT are not set set monthly but yearly.
Services Provided Program
select
a.ChildID,
b.WAIVER_SERVICES,
month(a.DOSStart) as Month,
YEAR(a.DOSStart) as Year,
sum(b.RATE_AMOUNT)as total
[Code] ....
The Services Provided data set I bring back looks something like this:
CREATE TABLE Provided (
ChildID int,
WAIVER_SERVICES int,
Month int,
Year int,
total int,
[Code] ....
Again, I am have trouble joining these two data sets to get something that looks like this
ChildID WAIVER_SERVICES Month/Year services_provided Budget % used 10177 3 12/2012 125 125 100% 10177 3 1/2013 75 125 60%
10177 3 2/2013 100 175 57% 10177 9 1/2013 50 50 100%
10177 9 5/2013 800 950 84%
Passing A Value To Another Stored Procedure...need Urgent Help
Dec 10, 2007Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
How To Use The Return Value Of A Stored Procedure (URGENT)
May 11, 2008Hi everyone, I have a stored procedure as the following:1 ALTER PROCEDURE dbo.logincheck2 @ID int,3 @Password varchar(50),4 @Result int output5 AS6 IF EXISTS (SELECT * FROM users WHERE (ID=@ID AND Password=@Password))7 BEGIN8 SET @Result=19 END10 11 ELSE12 BEGIN13 SET @Result=014 END15 16 RETURN @Result What I'm trying to do is using @result in my login.aspx.vb to check if user exists in my database. If he is, the result should be 1 and if not it should be 0. Unfortunately, I don't know how to do it. I must submit my project in 2 days and I'm desperately seeking for help. Here is my code: 1 Dim sqlcon As New SqlClient.SqlConnection2 Dim sqlcmd As New SqlCommand()3 Dim sonuc As Integer = 04 If IsValid Then5 sqlcon.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersShadow ShooterWebSiteMainProjectApp_Datamydb.mdf;Integrated Security=True;User Instance=True"6 sqlcon.Open()7 sqlcmd.Connection = sqlcon8 sqlcmd.CommandType = CommandType.StoredProcedure9 sqlcmd.CommandText = "logincheck"10 sqlcmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")11 sqlcmd.Parameters("@ID").Value = txtUsername.Text12 13 sqlcmd.Parameters.Add("@Password", SqlDbType.VarChar, 50, "Password")14 sqlcmd.Parameters("@Password").Value = txtPassword.Text15 16 sqlcmd.Parameters.Add("@Result", SqlDbType.Int, 4, sonuc)17 sqlcmd.Parameters("@Result").Direction = ParameterDirection.Output18 19 20 sqlcmd.ExecuteNonQuery()21 22 If (sonuc = 1) Then23 24 Response.Write("USER FOUND :)")25 'cookie is set with SESSION ID.26 Response.Cookies("User").Value = Session.SessionID27 Response.Cookies("user").Expires = DateTime.Now.AddMinutes(30)28 'Response.Redirect("Welcome.aspx")29 Else30 Response.Write("USER NOT FOUND :(")31 'Response.Redirect("invalid.aspx")32 33 End If
View 5 Replies View RelatedSql Stored Procedure Problem(very Urgent)
Jul 6, 2000Since i am newbie to sqlserver and & stored procedure. I need some help to modify the existing stored procedure.
The values and parameters are passing to stored procedure from Asp page.
My question is in Stored procedure, there is one value is Company name (vchCompany Name) , I need to check the values
passed from the asp page, whether the company name is already exists in the table. If exists display the pop up message window.
Thanks
The code is below:
************************************************** ****
CREATE procedure wbospsiCompany
@iSiteId int,
@iCompanyId int = NULL OUTPUT,
@chLanguageCode char(4),
@vchAssignedId varchar(255),
@vchCompanyName varchar(255),
@vchAddress1 varchar(255),
@vchAddress2 varchar(255),
@vchAddress3 varchar(255),
@vchCity varchar(255),
@chRegionCode char(4),
@chCountryCode char(4),
@vchPostCode varchar(40),
@vchPhoneNumber varchar(40),
@vchEmailAddress varchar(255),
@vchURL varchar(255),
@iCompanyTypeCode int,
@iCompanySubTypeCode int,
@iFamilyId int,
@iParentId int,
@iPrimaryContactId int,
@vchContactFirstName varchar(255),
@vchContactLastName varchar(255),
@iDivisionCode int,
@iSICCode int,
@iMarketSector int,
@vchTaxId varchar(255),
@vchDunnsNumber varchar(255),
@iPhoneTypeId int,
@iAddressTypeId int,
@iSourceId int,
@iStatusId int,
@bValidAddress tinyint,
@iAccessCode int,
@bPrivate tinyint,
@vchUser1 varchar(255) = NULL,
@vchUser2 varchar(255) = NULL,
@vchUser3 varchar(255) = NULL,
@vchUser4 varchar(255) = NULL,
@vchUser5 varchar(255) = NULL,
@vchUser6 varchar(255) = NULL,
@vchUser7 varchar(255) = NULL,
@vchUser8 varchar(255) = NULL,
@vchUser9 varchar(255) = NULL,
@vchUser10 varchar(255) = NULL,
@chInsertBy char(10) = NULL,
@dtInsertDate datetime = NULL,
@tiLockRecord tinyint = 0,
@tiRecordStatus tinyint = 1,
@tiReturnType tinyint = 1
AS
/*
** ObjectName: wbospsiCompany
**
** Project: Apollo
** SubProject:
** FileName: Insert.sql
** Type: Production
**
** Description: Inserts a record into the Company table.
**
** Valid Values for @tiLockRecord Valid Values for @tiReturnType
** ------------------------------ ------------------------------
** 1 = Lock Record 1 = Result Set
** 0 = Don't Lock Record 0 = Output Only
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
** 1/26/99 RobertA Created
** 10/11/99 GregP Dion schema compatibility changes
**
*/
BEGIN
/*
** Declare & initialize Local Variables
*/
declare @iReturnCode int,
@iWBOCPExists int,
@iWBOCPReturn int
select @iReturnCode = 0,
@iWBOCPExists = 0,
@iWBOCPReturn = 0
/*
** Declare & initialize Local Constants
*/
declare @PRE_OPTION int,
@CORE_OPTION int,
@POST_OPTION int,
@INSERT_CODE char(1),
@chUpdateBy char(4),
@dtUpdateDate datetime,
@dtModifiedDate datetime
select @PRE_OPTION = 0,
@CORE_OPTION = 0,
@POST_OPTION = @tiLockRecord * 2, /* Lock if @tiLockRecord = 1 */
@INSERT_CODE = "I",
@chUpdateBy = NULL,
@dtUpdateDate = NULL,
@dtModifiedDate = NULL
exec @iReturnCode = ospsiCompanyPre @iSiteId OUTPUT,
@iCompanyId OUTPUT,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@PRE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
/*
** Determine if @vchAssignedId and @iIndividualId should be set by configs
*/
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsuCustomerIds @iSiteId,
"Company",
@chCountryCode,
@iCompanyId OUTPUT,
@vchAssignedId OUTPUT
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Determine if WBOCP configurable procedure exists
*/
exec @iWBOCPExists = ospObjectExists "wbocpscCompany", "p"
if @iReturnCode <= 0
begin
begin transaction
if @iWBOCPExists = 1
begin
exec @iWBOCPReturn = wbocpscCompany @INSERT_CODE,
@iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT
if @iWBOCPReturn <> 0
begin
exec @iReturnCode = ospCheckError "p", @iWBOCPReturn
end
if @iReturnCode <= 0
begin
exec @iReturnCode = espcpCheckCustomerId @iSiteId, @iCompanyId
end
end
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsiCompanyCore @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@CORE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Transaction Management
*/
if @iReturnCode <=0
begin
commit transaction
end
else
begin
rollback transaction
end
if @iReturnCode <= 0
begin
/*
** Call post procedure
*/
exec @iReturnCode = ospsiCompanyPost @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@POST_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
end
/*
** Manage return: return ID of record based upon @ReturnType param
*/
exec @iReturnCOde = ospManageReturn @tiReturnType,
@iReturnCode,
@iSiteID,
@iCompanyId,
"Company",
"I"
if @iReturnCode = 0
begin
select @iReturnCode = @iWBOCPReturn
end
return @iReturnCode
END
************************************************** ****
Urgent! Asp And Sql Stored Procedure Parameters
Apr 12, 2005Hi, I have a problem with input parameter which has Decimal DataType. Stored procedure works but it rounds all
values, i.e 5.555 input becomes 6 and 1.3 input becomes 1.
In table QTY has data type decimal(5) - precision(8) scale(3).
Please, suggest what's wrong with this:
newqty = Request.Form("quantity")
..........
cmd.Parameters.Append(cmd.CreateParameter("qty", adDecimal, adParamInput, 5, newqty))
cmd.Parameters("qty").Precision = 8
cmd.Parameters("qty").NumericScale = 3
Please, help!
Thanks in advance.
Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year
Mar 11, 2014I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------
DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)
SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL
[Code] .....
Create WHERE Statement That Will Calculate Values From Current Fiscal Year To Last Complete Month
Feb 3, 2015I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
How To Pass XML File To Stored Procedure (Urgent)
Dec 18, 2007i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
View 7 Replies View RelatedInsertion Data Via Stored Procedure [URGENT]!!
Oct 15, 2004Hi all;
Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??
Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??
Example:
------------
I have two tables "Customer" and "CustomerDetails"..
SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).
SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.
FYI:
----
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)
Thanks in advanced!
Stored Procedure In SELECT Statement?? Urgent
Sep 26, 2001I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.
SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2
Can I use stored proc in this way in SQL-server?
Stored Procedure Cursor Problem URGENT
Dec 14, 2000Hi,
I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks
CREATE PROCEDURE [MYSP] AS
Declare @pdesc nvarchar(30)
Declare @ssc int
Declare @myid int
Declare @name nvarchar(30)
Declare new_cur CURSOR DYNAMIC FOR
SELECT ProductDescription, SubSubCatID
FROM C2000HPB
FOR UPDATE
Open new_cur
FETCH FROM new_cur INTO @pdesc, @ssc
While @@FETCH_STATUS = 0
BEGIN
Declare new_cur2 CURSOR DYNAMIC FOR
SELECT SubSubCatID, SubSubCategory FROM SSC
FOR READ ONLY
Open new_cur2
FETCH FROM new_cur2 INTO @myid, @name
While @@FETCH_STATUS = 0
BEGIN
IF PATINDEX ('@name%',@pdesc) = 0
Set @ssc = @myid
UPDATE C2000HPB
SET SubSubCatID = @ssc
FETCH NEXT FROM new_cur2 INTO @myid, @name
END
Close new_cur2
DEALLOCATE new_Cur2
FETCH NEXT FROM new_cur INTO @pdesc,@ssc
END
Close new_cur
DEALLOCATE new_Cur
URGENT: Stored Procedure Throws Windows Error 203
May 15, 2007I am trying to execute a stored procedure on Server1 which creates an excel report on a share of Server2:
The following error message is thrown:
Saving of scheduled report(s) to Excel file failed : 203 SaveReportToExcel() in TlRptToFile.RptExcel failed in sproc_SaveReportAsFile -
TLRptXL::SaveReportToExcel - Connection to Database failed for Analytics DataBase
Server : TKTALSQL3, Application Server: and DataBase : tlAnalytics : Windows
Error - The system could not find the environment option that was entered.
A DCOM component on Server1 runs the stored procedure that creates the excel report. The account under which DCOM component runs is a member of 'Administrators' group on both Server1 and Server2. Checked permissions on the shares. Account is a local Admin on both Server1 and Server2. Account under which SQLServer and ServerAgent runs is also an Admin on these shares (implicitly as part of 'Administrators' group).
I am manually able to create an excel/text file on the Server2 share while accessing it from Server1, though.
Appreciate your help in resolving the issue.
Urgent: Whichj One Is Faster : SSIS Or Stored Procedure
Dec 17, 2007
Hi
My PM and me are on a discussion. He wants SSIS to simply upload the source excel files in staging and do rest of the ETL in SQl server stored procedure which I feel is primitive.
I have suggested him to use SSIS for whole ETL and not Stored procedure( method which indistry has discarded)
He sayd Stored procedure are more efficient than SSIS.
Please guide with some facts.
Calculate Ratio : Urgent
Nov 14, 2007I have to get the ratio like A:B with two calculated columns. A and B both are the counts of the rows.
Please help me with this, it's really urgent!
Urgent : Maximum Stored Procedure, Function, Trigger, Or View Nesting Level Exceeded
Aug 3, 2005Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END