stored procedure to add values

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 2012 Forums

Transact-SQL (2012)

stored procedure to add values

Author

Topic

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 08:26:30

I need a stored procedure to get the numeric value of a field (X123InStock) where Department is Dept1, add sum numeric value to the value(ie 150) and then update the field(X123InStock) where Department is Dept1 with the new value. The field Data type is decimal(18,0)

Department, X123InStock
Dept1,100
Dept2,120

Greetings
Gert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 08:34:10

is dept and value fixed? if not make it parametrised.

Other ways you could simply do this


CREATE PROC DataModify
AS
UPDATE Table
SET [X123InStock] = [X123InStock] + 150
WHERE Department = 'Dept1'
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 08:45:06

I first need to get the value of X123InStock, then add a specific value (parameter) to it and then update the table field X123InStock with the new value. So, the answer is yes, the values will be parametrised

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 08:54:45

PROCEDURE [dbo].[sp_UpdatePrinters]
(
@TableName varchar(50),
@PartNo varchar(50),
@Received int,
@AreaName varchar(50)

)
AS

BEGIN
SET NOCOUNT ON;
DECLARE @OldValue varchar(max)
SET @OldValue = 'SELECT ' + @TableName + '.' + @PartNo + ' FROM ' + @TableName + ' WHERE ' + @TableName + '.Department = ''' + @AreaName + ''''
EXEC(@OldValue)
END

BEGIN
DECLARE @NewValue int
DECLARE @SQL varchar(MAX)
SET @NewValue = @OldValue + @Received

SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @TableName + '.' + @PartNo + ' = ' + @NewValue + ' WHERE ' + @TableName + '.Department = ''' + @AreaName + ''''
EXEC(@SQL)
END

I first need to get the value of X123InStock, then add a specific value (parameter) to it and then update the table field X123InStock with the new value. So, the answer is yes, the values will be parametrised.

I tried this

If I execute the SP with the following parameters
EXEC [sp_UpdatePrinters] @TableName = 'tbl_HP_4515', @PartNo = 'CC364X_IS', @Received = 100, @AreaName = 'FHA'

Conversion failed when converting the varchar value 'SELECT tbl_HP_4515.CC364X_IS FROM tbl_HP_4515 WHERE tbl_HP_4515.Department = 'FHA'' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 10:00:21

why is tablename passed through a parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 10:27:08

The tablename must be dynamic. The same procedure must be execute on other tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 13:57:52

quote:
Originally posted by gjja

The tablename must be dynamic. The same procedure must be execute on other tables

What? do you mean to say you've multiple tables with same metadata?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 14:58:09

No, I have multiple tables, 1 table per printer. The field names are components of the printers, so each table will have different structure and data

gjja
Starting Member

17 Posts

Posted - 2013-11-04 : 22:27:15

to put it simple, I need to get the value of a field, add an amount to it and then update the same field with the new value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 01:00:46

Why cant you write separate updates for the tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-05 : 02:37:46

Your method is subject to SQL Injection. Use separate procedure to update values using Static SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources