Update Query with variable in Where clause

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

Update Query with variable in Where clause

Author

Topic

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-11-06 : 12:58:47

I am trying to run the following query and it keeps telling me that zcomp is not a valid column name. zcomp is supposed to be the variable I am using to tell it which ComponentID I wish to update. It works fine if I put a value in place of zcomp, like 3089 - the ComponentID is and Integer, PK and Auto-increment. But it won't take the variable. Do I need to do something special to use the variable in place of the actual number?

Thanks,
Stacy

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[MaxDatePart]    Script Date: 11/06/2012 11:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[WOPartsUpdate]
(
@zamnt Integer,
@zcomp Integer
)
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.sparts
SET dbo.sparts.QtyonHand = dbo.sparts.QtyonHand + zamnt WHERE ComponentID=zcomp;
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-06 : 13:39:34

you're missing the "@":

WHERE ComponentID=@zcomp;

EDIT:
and here too:
+ @zamnt

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 13:40:28

it should be


USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[MaxDatePart]    Script Date: 11/06/2012 11:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[WOPartsUpdate]
(
@zamnt Integer,
@zcomp Integer
)
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.sparts
SET dbo.sparts.QtyonHand = dbo.sparts.QtyonHand + zamnt WHERE ComponentID=@zcomp;
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources