Stored Procedure Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Help

Author

Topic

tbailey922
Starting Member

5 Posts

Posted - 2004-07-19 : 09:55:54

I have been stuck on this for some time now. Could someone please point me in the right direction? Here is my SP:
CREATE PROCEDURE [breakeven]
(@job_1 [char])

AS UPDATE [Test].[dbo].[jcpmst]
if [costtogo] > 1000 then
SET [costtogo] = (estamount - cocost) - actualcost
else
set [costtogo] = actualcost
WHERE
( [job] = "100356")
end
GO

I keep getting an error saying incorrect syntax near 'if'
Very frustrating.

Thanks in advance
Tim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-19 : 09:59:53

You can't put the if between the update and the set...

CREATE PROCEDURE [breakeven]
(@job_1 [char])

AS

if [costtogo] > 1000 then
Begin

UPDATE [Test].[dbo].[jcpmst]
SET [costtogo] = (estamount - cocost) - actualcost
WHERE
( [job] = "100356")

End
else
Begin

UPDATE [Test].[dbo].[jcpmst]
set [costtogo] = actualcost
WHERE
( [job] = "100356")

end
GO

Corey

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 10:03:14

[code]
CREATE PROCEDURE [breakeven]
(@job_1 [char])

AS
UPDATE [Test].[dbo].[jcpmst]
SET [costtogo] =
CASE WHEN [costtogo] > 1000 THEN (estamount - cocost) - actualcost
ELSE actualcost
END
WHERE
( [job] = "100356")
GO
[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-19 : 10:10:14

yeah that would be a bit better... (I don't think i even read the query, just swapped the pieces around)

Corey

tbailey922
Starting Member

5 Posts

Posted - 2004-07-19 : 10:33:23

Thanks for all the help, I couldn't figure it out for nothing. It works now!!

tbailey922
Starting Member

5 Posts

Posted - 2004-07-19 : 11:33:59

STILL NEED SOME HELP!!!
I want the user to be able to pick which job needs updating here is my SP

CREATE PROCEDURE [breakeven]
(@job_1 [char])

AS
UPDATE [Test].[dbo].[jcpmst]
SET [costtogo] =
CASE WHEN [actualcost] > (estamount + cocost) THEN 0
ELSE (estamount + cocost) - actualcost
END
WHERE
( [job] = @job_1)
GO

When I run this nothing happens.

Any ideas?

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-19 : 12:02:24

How are you running the procedure? The field [job] has to be of char datatype and you would call the procedure like this:

EXEC breakeven @job_1 = '100356'

tbailey922
Starting Member

5 Posts

Posted - 2004-07-19 : 12:22:04

Here is my code in vb.net
Dim cmd As SqlCommand = New SqlCommand
Dim con1 As SqlConnection
Dim irowsaffected As Integer

con1 = New SqlConnection
con1.ConnectionString = con.ConnectionString

cmd.CommandText = "breakeven"

cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con1

cmd.Parameters.Add("@job_1", SqlDbType.Char)
cmd.Parameters.Item("@job_1").Value = jobnum

con1.Open()
irowsaffected = cmd.ExecuteNonQuery
con1.Close()

dajcpmst.Fill(DsJcpmst1, "jcpmst")

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 13:37:46

What happens if you jsut run the "guts" of the SProc in Query Analyser? How many rows are updated? (Substitute the actual jobnum at 'nnnnnn' below)


UPDATE [Test].[dbo].[jcpmst] 
SET [costtogo] = 
CASE WHEN [actualcost] > (estamount + cocost) THEN 0
ELSE (estamount + cocost) - actualcost
END
WHERE 
( [job] = 'nnnnnn')

(I noticed that you have
CREATE PROCEDURE [breakeven]
(@job_1 [char])
which probably ought to have a Size after char:
CREATE PROCEDURE [breakeven]
(@job_1 char(99))

Kristen

tbailey922
Starting Member

5 Posts

Posted - 2004-07-19 : 13:45:37

If I put in the job number as you say, it works fine.

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 14:24:16

Well, that's a good start! So the problem must be at the VB.net end, or somewhere between the two.

I don't know VB well enough to know if your syntax is spot-on, or not. Might be worth double checking that "jobnum" is the value you expect when it gets into:
cmd.Parameters.Item("@job_1").Value = jobnum
- perhaps put a Response.Write to check it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources