Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-02
1
Medium Priority
?
190 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

660 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