Link to home
Start Free TrialLog in
Avatar of dgdfgssfdgs
dgdfgssfdgs

asked on

Builad a sql update string in a stored proc

just not getting it here...

ALTER PROCEDURE [dbo].[BackOutSoData]
      
   @StoreName nvarchar(20),
   @TableName  nvarchar(50),
    @partNumber nvarchar(50),
    @qty  int
AS
 BEGIN
       declare @CmdText nvarchar(2000)
             
       SET NOCOUNT ON;

set @CmdText =      ' update [PartsInventories].[dbo].[' + @tablename + '] set                      
        Lightspeed_Qty  =' + @qty + '   where ltrim(rtrim(store)) = ''' + @StoreName + ''''          
  select (@CmdText)
END

the problem is with the @qty variable. get this error:
-----------------------------------------------------------------
Conversion failed when converting the nvarchar value '(update [PartsInventories].[dbo].[jb6] set                      
        Lightspeed_Qty  =' to data type int.
-----------------------------------------------------------
i'm just asking for the @CmdText string back. for reason as yet unknown sql is attempting to execute the string? if i drop =' + @qty + out i get the string as expected.
?
jim



ASKER CERTIFIED SOLUTION
Avatar of lewiguez
lewiguez
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dgdfgssfdgs
dgdfgssfdgs

ASKER

hi,
well, yes.. that works. thanks for the fish!
really what i was after was doing math on the @qty variable. so this:
set @CmdText =      ' update [PartsInventories].[dbo].[' + @tablename + '] set                      
         Lightspeed_Qty   =  (Lightspeed_Qty -' + CAST(@qty AS nvarchar(20))  + ')   where ltrim(rtrim(partnumber)) = ''' + @partNumber + '''
lightspeed_qty is an int in the db. the @qty parameter is an int. silly me, i thought i could do math on ints.
guess this is the trouble with being self taught.
thank you very much.

jim