New to SQL and Stored Procedure. Been reading up where I can. Trying to
create a stored procedure that does the following. Reads in a value
from an ASPX dropdown (@SST), reads in a value from a another ASPX
dropdown (@acct_no).  Takes the following user input data to the
following fields.  Contract Price @contract_pric, Contract Freq
@contract_freq, Start Date @s_date, End Date @e_date.  Now after all
that is input into the page. The procedure is supposed to calculate the
number of points the person earned based on the data input and inserts
all data including points earned into a DB (promo). Getting hung up.
Any ideas??

Procedure:
Create Procedure promocalc_sp
    @SST
    @acct_name nvarchar
    @acct_no nvarchar
    @contract_freq nvarchar
    @contract_pric decimal
    @s_date datetime
    @e_date datetime
    @points integer

    AS
    BEGIN
    insert acct_no, @acct_no, @contract_freq, @contract_pric, @s_date,
@e_date,
    CASE @contract_freq
    WHEN '1'
      THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
    When '2'
    THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
    WHEN '3'
    THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
    WHEN '4'
    THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
    WHEN 'W'
    THEN (datediff(ww,@s_date, @e_date) * @contract_pric)/200
    WHEN 'E'
    THEN ((datediff(mm,@s_date, @e_date) * @contract_pric)/2)/200
    WHEN 'O'
    THEN ((datediff(mm,@s_date, @e_date) * @contract_pric)/2)/200
    ELSE NULL
    END as @points
    from promo
    where acct_no = @acct_no
--SET @points = {Calc Value here}
    END
GO

Please post DDL (sample data would also be nice), since it's extremely
difficult to come up with usable insert/update statements for tables we've
never seen.

Anyway, judging from your post you should take a look at the INSERT...SELECT
statement in Books Online (I guess you haven't done that yet).

insert <table name>
  (
  <column list>
  )
  select <column/variable list>
           from <source object>

The "{Calc Value here}" part seems as if at the wrong place. Don't you want
to transform your data *before* inserting it into the destination?

Excuse me for being harsh, but I'm writing this with my overheated notebook
in my lap. I smell burning flesh.


ML

---
http://milambda.blogspot.com/

Thanks. I was playing around with it someone and found that Books
Online Post. Was most helpful. Here is what I came up with and thanks
for your help.

Create Procedure promocalc_sp
    @SST int,
    @acct_no nvarchar(50),
    @contract_freq nvarchar(50),
    @contract_pric decimal (18,0),
    @s_date datetime,
    @e_date datetime,
    @contest_no int
    AS
    BEGIN
    INSERT INTO Promo (sst, acct_no, contract_pric, contract_freq, s_date,
e_date, contest_no, points)
    VALUES (@sst, @acct_no, @contract_pric, @contract_freq, @s_date,
@e_date, @contest_no, CASE @contract_freq
WHEN '1'
  THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
When '2'
  THEN (datediff(mm,@s_date, @e_date) * @contract_pric)/200
WHEN '3'
  THEN (datediff(mm,@s_date,@e_date) * @contract_pric)/200
WHEN '4'
  THEN (datediff(mm,@s_date,@e_date) * @contract_pric)/200
WHEN 'W'
  THEN (datediff(ww,@s_date,@e_date) * @contract_pric)/200
WHEN 'E'
  THEN ((datediff(mm,@s_date, @e_date) * @contract_pric)/2)/200
WHEN 'O'
  THEN ((datediff(mm,@s_date, @e_date) * @contract_pric)/2)/200
ELSE 0
END )

And I don't consider anything you said as harsh. It was most helpful
and I appreciate the time you took into writing up your response. Thank
you very much.

~Roar
ML wrote:
Show quoteHide quote

You're too kind. :) And by the looks of it you've nailed the proc as a pro.

Another quick suggestion: *always* list the columns in the INSERT statement.
Not listing them spells trouble.


ML

---
http://milambda.blogspot.com/

You are right. It spells trouble with a HUGE T.  I'm still learning
though. Now time for me to dive into ASP.NET and figure out how to
create a UI for someone to input that.

Wish me luck.

~Roar
ML wrote:
Show quoteHide quote

..Net newsgroups await you...


ML

---
http://milambda.blogspot.com/

VIEW's ORDER BY columns necessary in column list
sql query - sp
How can I Rearrange my Data
FOREIGN KEY that REFERENCES a server login for a database?
connect to oracle database