Create Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Create Procedure

Author

Topic

kitfox69
Starting Member

13 Posts

Posted - 2008-05-19 : 16:13:30

I am trying to create a procedure but I am not sure how to script this correctly.

CREATE PROCEDURE spPriceChange
@newprice DECIMAL(5,2),
@margin DECIMAL(5,4),
@mincalc DECIMAL(5,2),
@maxcalc DECIMAL(5,2)
AS
UPDATE item set item_prc_3 = @newprice
where item_lst_lnd_cost/@margin BETWEEN @mincalc AND @maxcalc
and item_id in (select item_id from item where item_prc_1 like '%9.99')
and item_cat in ('AB', 'AC', 'AD', 'AH', 'AM', 'AS', 'AT', 'DB', 'DC', 'DD', 'DH', 'MB', 'MC', 'MD', 'MH', 'MM', 'MU', 'SM', 'UA', 'UC', 'UM', 'UO', 'UU', 'VM', 'VU')
and item_id not like '*%'
and item_id not like 'A%'
and item_id not like 'C%'
and item_id not like 'L%'
and item_id not like 'Q%'
and item_id not like 'R%'
and item_id not like 'Z%'

The query throws an error that only shows the @ symbol as the stop point. What did I do wrong?

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 16:21:09

For starters, UPDATE is spelled wrong.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-19 : 16:46:44

Ok I missed typed it here but it was correct in the script in SSMC Express.

Anything else wrong with it?

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 16:49:45

It is syntactically correct according to SSMS.

Please post the exact stored procedure and error. Copy/paste them. Do not free type anything.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-19 : 16:58:56

Ok I fixed it... I had a double quote in place of a single... oops.

Here is a cut and paste of the PROCEDURE, the EXEC statement and the error I am getting now.

PROCEDURE

CREATE PROCEDURE spPriceChange
@newprice DECIMAL(5,2),
@margin DECIMAL(5,4),
@mincalc DECIMAL(5,2),
@maxcalc DECIMAL(5,2)
AS
UPDATE item SET item_prc_3 = @newprice
WHERE item_lst_lnd_cost/@margin BETWEEN @mincalc AND @maxcalc
and item_id in (SELECT item_id FROM item WHERE item_prc_1 like '%9.99')
and item_cat in ('AB', 'AC', 'AD', 'AH', 'AM', 'AS', 'AT', 'DB', 'DC', 'DD', 'DH', 'MB', 'MC', 'MD', 'MH', 'MM', 'MU', 'SM', 'UA', 'UC', 'UM', 'UO', 'UU', 'VM', 'VU')
and item_id not like '*%'
and item_id not like 'A%'
and item_id not like 'C%'
and item_id not like 'L%'
and item_id not like 'Q%'
and item_id not like 'R%'
and item_id not like 'Z%'

EXEC statement

use HOMES
go

EXEC spPriceChange '99.99', '.57', '90', '114.99'

ERROR

Msg 208, Level 16, State 1, Procedure spPriceChange, Line 7
Invalid object name 'item'.

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 17:06:04

According to the error, the item table does not exist in the HOMES database or the owner needs to be included.

What does this return:

USE HOMES
GO
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'item'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-19 : 17:21:02

Comes up as successful but no schema entry.

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources