Solved

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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…

820 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