sql server programming:
First create this number table CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY) DECLARE @intLoopCounter INT SELECT @intLoopCounter =0 WHILE @intLoopCounter <=999 BEGIN INSERT INTO NumberPivot VALUES (@intLoopCounter) SELECT @intLoopCounter = @intLoopCounter +1 END then your proc will be this, change YourTable to your table name CREATE PROC sp_addfavstocustomer @id INT, @favs NVARCHAR(200) AS SET NOCOUNT ON INSERT INTO YourTable SELECT @id ,SUBSTRING(',' + @favs + ',', NumberID + 1, CHARINDEX(',', ',' + @favs + ',', NumberID + 1) - NumberID -1)AS Value FROM NumberPivot WHERE NumberID <= LEN(',' + @favs + ',') - 1 AND SUBSTRING(',' + @favs + ',', NumberID, 1) = ',' SET NOCOUNT OFF GO Denis the SQL Menace http://sqlservercode.blogspot.com/ [quoted text, click to view] Aussie Rules wrote:
> Hi,
>
> To save numerous round trips between the server and client, I want to pass
> in a string that contains a number of values, that the store proc will split
> up into the single values and insert into a table.
>
> For example, the client will execute the following
>
> exec sp_addfavstoCustomer
> 1,'1,5,33,21,454,22,12,432,443,2,1,3,4,5,3,2,4,,8,9,9,0,8,7,99)
>
> The stored proc is
>
> create proc sp_addfavstocustomer
> @id int,
> @favs nvarchar(200)
>
> as.....
>
> The idea would be insert into a table the following records
>
> 1,1
> 1,5
> 1,33
> 1,21
> etc
>
> The first value (1) is the customer number, the second is the next value
> from the collection of numbers held in the string... the second value is a
> int field..
>
> Is there an easy way to do this ?
>
> Thanks
Hi,
To save numerous round trips between the server and client, I want to pass
in a string that contains a number of values, that the store proc will split
up into the single values and insert into a table.
For example, the client will execute the following
exec sp_addfavstoCustomer
1,'1,5,33,21,454,22,12,432,443,2,1,3,4,5,3,2,4,,8,9,9,0,8,7,99)
The stored proc is
create proc sp_addfavstocustomer
@id int,
@favs nvarchar(200)
as.....
The idea would be insert into a table the following records
1,1
1,5
1,33
1,21
etc
The first value (1) is the customer number, the second is the next value
from the collection of numbers held in the string... the second value is a
int field..
Is there an easy way to do this ?
Thanks
Aussie Rules (AussieRules@nospam.nospam) writes: [quoted text, click to view] > To save numerous round trips between the server and client, I want to
> pass in a string that contains a number of values, that the store proc
> will split up into the single values and insert into a table.
>
> For example, the client will execute the following
>
> exec sp_addfavstoCustomer
> 1,'1,5,33,21,454,22,12,432,443,2,1,3,4,5,3,2,4,,8,9,9,0,8,7,99)
Never call your procedures sp_something. That prefix is reserved for system procedures, and SQL Server will first look in master for these. [quoted text, click to view] > The idea would be insert into a table the following records
>
> 1,1
> 1,5
> 1,33
> 1,21
> etc
>
> The first value (1) is the customer number, the second is the next value
> from the collection of numbers held in the string... the second value is a
> int field..
>
> Is there an easy way to do this ?
See http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers. Read the full article if you want to try other methods. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
[quoted text, click to view] Aussie Rules wrote:
> Hi,
>
> To save numerous round trips between the server and client, I want to pass
> in a string that contains a number of values, that the store proc will split
> up into the single values and insert into a table.
>
> For example, the client will execute the following
>
> exec sp_addfavstoCustomer
> 1,'1,5,33,21,454,22,12,432,443,2,1,3,4,5,3,2,4,,8,9,9,0,8,7,99)
>
> The stored proc is
>
> create proc sp_addfavstocustomer
> @id int,
> @favs nvarchar(200)
>
> as.....
>
> The idea would be insert into a table the following records
>
> 1,1
> 1,5
> 1,33
> 1,21
> etc
>
> The first value (1) is the customer number, the second is the next value
> from the collection of numbers held in the string... the second value is a
> int field..
>
> Is there an easy way to do this ?
>
> Thanks
>
>
>
http://realsqlguy.com/serendipity/archives/4-All-Strung-Out.html -- Tracy McKibben MCDBA
Don't see what you're looking for? Try a search.
|
