Solved

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

Posted on 2007-04-02
1
188 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

734 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