Help with a Stored Procedure..

Can anybody help me with this stored procedure? Basically I need to do
something like do:
Total = Premium + (WHEN AddOn1 != -1) + (WHEN AddOn2 != -1) + (WHEN
AddOn3 != -1)
To clarify, the value 'Total' needs to consist of a field called
'Premium' plus the AddOn1, AddOn2, AddOn3 IF they are not equal to -1.
Hope this is clear.
Can anyone help?
Regards,
ChrisHi Chris,
Total = Premium + (CASE WHEN AddOn1 != -1 THEN AddOn1 ELSE 0 END)
(CASE WHEN AddOn2 != -1 THEN AddOn2 ELSE 0
END)
(CASE WHEN AddOn3 != -1 THEN AddOn3 ELSE 0
END)
HTH, jens Suessmeyer.|||Maybe some more plus signs might be in order, Jens:
Total = Premium + (CASE WHEN AddOn1 != -1 THEN AddOn1 ELSE 0 END)
+ (CASE WHEN AddOn2 != -1 THEN AddOn2 ELSE 0 END)
+ (CASE WHEN AddOn3 != -1 THEN AddOn3 ELSE 0 END)
:)
ML

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Help with 1toMany update/case stmt?

Hi all,
Looking for a way to do this in the minimum # of stmts I can.
Basically, this is doing a table1(1) to table2(many) update join. When
the t2 row has myindex = 0, then update column t1.c1.
The problem is that for each row in t2, it's index only matches 1 column
out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
should not be updated in that case.
I tried to accomplish this my defaulting back to the orignal t1.cx value,
but I find that it is putting the ORIGINAL pre-update stmt value in
there, rather than the last value it was updated to during the update
operation.
Is there an option, or a way in a case stmt to say 'in this case, leave
the value alone and don't change it?'
Thanks!
UPDATE table1
SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
FROM table1 t1 FULL JOIN table2 t2 ON
( t1.mykey = t2.mykey
AND t1.mydate >= t2.fromdate
AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
)
WHERE t1.status = 'x'
AND t2.status = 'y'you need to aggregate the values in table2 before you do the update. becaus
e
you're replacing the values, you need to essentially select the correct valu
e
in table2 before updating the row in table1. make it a 1=1 relationship
before doing the update. The problem with your current design is that there
is no definitive way to guarantee the order of the updates, so each time the
query runs, you could get different results.
In a side note, the predicate of your update statement doesn't make sense.
Why are you using a full join in an update statement? I'm not even sure what
happens to the rows in which the table1 columns are null. Are they ignored?
"xnews user" wrote:

>
> Hi all,
> Looking for a way to do this in the minimum # of stmts I can.
> Basically, this is doing a table1(1) to table2(many) update join. When
> the t2 row has myindex = 0, then update column t1.c1.
> The problem is that for each row in t2, it's index only matches 1 column
> out of the 5 (c1..c5) in t1 for a given tuple. The other 4 columns
> should not be updated in that case.
> I tried to accomplish this my defaulting back to the orignal t1.cx value,
> but I find that it is putting the ORIGINAL pre-update stmt value in
> there, rather than the last value it was updated to during the update
> operation.
> Is there an option, or a way in a case stmt to say 'in this case, leave
> the value alone and don't change it?'
> Thanks!
>
> UPDATE table1
> SET c1 = CASE WHEN t2.myindex = 0 THEN t2.newvalue ELSE t1.c1 END,
> c2 = CASE WHEN t2.myindex = 1 THEN t2.newvalue ELSE t1.c2 END,
> c3 = CASE WHEN t2.myindex = 2 THEN t2.newvalue ELSE t1.c3 END,
> c4 = CASE WHEN t2.myindex = 3 THEN t2.newvalue ELSE t1.c4 END,
> c5 = CASE WHEN t2.myindex = 4 THEN t2.newvalue ELSE t1.c5 END
> FROM table1 t1 FULL JOIN table2 t2 ON
> ( t1.mykey = t2.mykey
> AND t1.mydate >= t2.fromdate
> AND (t1.mydate <= t2.thrudate OR t2.thrudate IS NULL)
> )
> WHERE t1.status = 'x'
> AND t2.status = 'y'
>

Help with "start job" error

good morning everyone,
I have created some scheduled jobs which basically are transfer data from Oracle DB to SQL server through DTS package. I created DTS pacakages and right click to schedule the job for daily run.
I was able to execute DTS package to transfer data w/o problems at all. However it seemed scheduled job could not run automatically nor I kick off the job manually.
I have attached the error message below. It seemed I need some components be installed. Would someone take a look and let me know what is it, name of the components, probably cost information? Thank you very much for the help in advance.When you manually execute a DTS job, it runs under your login and with the resources of your local machine. When the scheduled job is run by the SQLServer Agent, it runs under the permissions of the login property that was specified in the Administrative Services tab with the physical resources (disk drives, folder mappings, etc.) of the SQL Server that the agent resides on.

The attached error looks like the Oracle components are missing from the SQL Server. Once you have those installed, you might want to also verify that the login used by SQL Server Agent has permisisons on the Oracle server.|||check to make sure your Sqlagent startup acct has proper permission. If the job is invoked by non-admin, agent proxy acct will be used. Take a look at the following to set the proxy acct.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Help stored procedures

Hello ,

I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

Procedure looks like so:

ALTER Procedure newUser (
/* Param List */

@.fname varchar(20),
@.lname varchar(20),
@.address1 varchar(20),
@.address2 varchar(20),
@.city varchar(20),
@.province varchar(20),
@.postalCode varchar(7),
@.country varchar(20),
@.phone varchar(10),
@.email varchar(30),
@.receiveNews bit,
@.archive bit,
@.business varchar(10),
@.fax varchar(10)
)
AS

BEGIN TRANSACTION
INSERT INTO customer (
fname,
lname,
address1,
address2,
city,
province,
postalCode,
country,
phone,
email,
receiveNews,
archive,
business,
fax
)
VALUES (
@.fname,
@.lname,
@.address1,
@.address2,
@.city,
@.province,
@.postalCode,
@.country,
@.phone,
@.email,
@.receiveNews,
@.archive,
@.business,
@.fax
)
COMMIT
RETURN

The syntax has been verified correct and does populate my customers table with valid data.

the column that i wish to return is called "pk_customerId".
I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

Any ideas.

Thanks in advancein short, you can do this:

return (scope_identity())

but i'd declare a @.variable and an @.error before your begin tran, then right before your commit do this:

select @.error=@.@.error, @.variable=scope_identity()
if @.error != 0 begin
raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
rollback tran
end
commit
return @.variable