SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 stored procedure to add values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  08:26:30  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/04/2013 :  08:34:10  Show Profile  Reply with Quote
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
Go to Top of Page

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  08:45:06  Show Profile  Reply with Quote
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
Go to Top of Page

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  08:54:45  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2013 :  10:00:21  Show Profile  Reply with Quote
why is tablename passed through a parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  10:27:08  Show Profile  Reply with Quote
The tablename must be dynamic. The same procedure must be execute on other tables
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2013 :  13:57:52  Show Profile  Reply with Quote
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
Go to Top of Page

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  14:58:09  Show Profile  Reply with Quote
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
Go to Top of Page

gjja
Starting Member

17 Posts

Posted - 11/04/2013 :  22:27:15  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/05/2013 :  01:00:46  Show Profile  Reply with Quote
Why cant you write separate updates for the tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/05/2013 :  02:37:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Your method is subject to SQL Injection. Use separate procedure to update values using Static SQL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000