| Author |
Topic |
|
Trist
Starting Member
4 Posts |
 Posted - 2008-08-28 : 04:54:24
|
| Hi, new here and haven't really used sql much.I'm trying to set up a stored procedure but keep getting an error around my variable.USE [Ausrecord] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[spstoreproc] as DECLARE @nprice1 float select Sum(Price1 * 1.03) as @nprice1 from Product UPDATE [Ausrecord].[dbo].[Product] SET Price1 = @nprice1 WHERE [SelectionGroupName] = 'FILES' The error I get says line 5 near @nprice1. I'm sure I don't need all the brackets but that shouldn't produce the error.Thanks in advanceTrist |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 05:26:38
|
quote:
Originally posted by Trist
Hi, new here and haven't really used sql much.I'm trying to set up a stored procedure but keep getting an error around my variable.USE [Ausrecord] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[spstoreproc] as DECLARE @nprice1 float select @nprice1=Sum(Price1 * 1.03) from Product UPDATE [Ausrecord].[dbo].[Product] SET Price1 = @nprice1 WHERE [SelectionGroupName] = 'FILES' The error I get says line 5 near @nprice1. I'm sure I don't need all the brackets but that shouldn't produce the error.Thanks in advanceTrist
modify as above |
 |
|
|
Trist
Starting Member
4 Posts |
Posted - 2008-08-28 : 08:18:12
|
| Great, I'm almost there. It seems to be adding all the rows together. How do I get it to do the calculation for each row seperatly? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-28 : 08:38:33
|
| Do you mean running total?Post some sample data with expected resultMadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-28 : 09:44:10
|
quote:
Originally posted by Trist
Great, I'm almost there. It seems to be adding all the rows together. How do I get it to do the calculation for each row seperatly?
what do you mean each row seperately? what you've posted is a set based code which does calaculation for all rows together. Can you explain with some data what you're expecting to get. |
|
|
|
Trist
Starting Member
4 Posts |
Posted - 2008-08-28 : 21:44:48
|
| Ok, sorry. I'll give an example what I'm trying to achieve.Say I've got 3 columns:ProductCode | SelectionGroupName | Price1 ABC | Labels | 165DEF | FILES | 274GHI | FILES | 338JKL | FILES | 100So basically what I'm trying to do is increase the Price1 field of each row where SelectionGroupName=FILES by 3%, so row 'DEF' will be 282.22, GHI will be 348.14, JKL will be 103, etc. So I want a query to cycle through each of the FILES rows and increase that individual price. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-29 : 02:37:13
|
quote:
Originally posted by Trist
Ok, sorry. I'll give an example what I'm trying to achieve.Say I've got 3 columns:ProductCode | SelectionGroupName | Price1 ABC | Labels | 165DEF | FILES | 274GHI | FILES | 338JKL | FILES | 100So basically what I'm trying to do is increase the Price1 field of each row where SelectionGroupName=FILES by 3%, so row 'DEF' will be 282.22, GHI will be 348.14, JKL will be 103, etc. So I want a query to cycle through each of the FILES rows and increase that individual price.
UPDATE [Ausrecord].[dbo].[Product] SET Price1 = Price1 * 1.03WHERE [SelectionGroupName] = 'FILES' MadhivananFailing to plan is Planning to fail |
|
|
|
Trist
Starting Member
4 Posts |
Posted - 2008-08-29 : 03:04:58
|
| ahhh, so I dont' actually need the variable then? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-29 : 04:06:03
|
quote:
Originally posted by Trist
ahhh, so I dont' actually need the variable then?
No need, you could just update all of them together using code Madhi provided. |
|
|
|
|
variable error