Solved

Sql Server Strored Procedure updating multiple rows that meet the same criteria

Posted on 2007-04-02
1
185 Views
Last Modified: 2010-03-19
I want to pass in a variable to a stored procedure such that it can satisfy multiple records using the IN clause.  For instance,

create procedure XXX
@tradeList varchar(max)
,@price float
as
update tradedetail set price = @price
where tradeId in @tradeList

with @tradeList = a buffer that contains multiple comma separated trade ids, i.e., (1, 3, 4, 8), etc.
Sql Server doesn't like this syntax.  Is there a way to accomplish this?

The reason I am doing this is because multiple trades can contain a reference to the same cusip, etc. But when information in the cusip changes I want to update all trades that have that cusip.  The problem is, the tradeDetail table does not have a reference to the cusip as that is contained in the TradeMaster.  
0
Comment
Question by:techhound
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18837882

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(max))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    


and use it like this:

create procedure XXX
@tradeList varchar(max)
,@price float
as
update tradedetail set price = @price
where tradeId in ( select value form dbo.ParmsToList(@tradeList))


note: I see that you use FLOAT for a PRICE, which is highly disrecommended. use DECIMAL ( 20,2 ) or DECIMAL(24,4) instead, float is unprecise data type
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question