Solved

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

Posted on 2007-04-02
1
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore 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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

626 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