Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

passing date parameters to report

How do you pass date parameters to a report? I've been trying to pass my date parameters as strings but I keep getting errors.

Try setting the data type to DateTime, like in this article:

http://msdn.microsoft.com/msdnmag/issues/06/06/DataPoints/

Passing an array of strings to a Stored Procedure

Well, Imanaged to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROC [dbo].[gv_sp_UpdatePOs]

(

@.IDListvarchar(500),

@.ReorderLevelint,

@.ProductNamenvarchar(30)

)

AS

BEGIN

SETNOCOUNTON

EXEC('Update dbo.Products

SET ReorderLevel = ('+ @.ReorderLevel+') ,ProductName = ('''+ @.ProductName+''')

WHERE ProductID IN ('+ @.IDList+')')

END

-------

THis works fine inside Sql Server 2005 Query analyser.

But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.

Any help would be appreciated

And can someone convert it to support XML instead of list of strings. thanks.

Hello my friend,

It would be better to do the following. First, run the following SQL: -

CREATE FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(800)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN

END

Test this function via the following: -

SELECT Item FROM dbo.StringArrayIntoTable('red,blue,yellow', ',')
SELECT Item FROM dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

Now change your procedure to the following: -

ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@.IDList varchar(500),
@.ReorderLevel int,
@.ProductName nvarchar(30)
)
AS

BEGIN

SET NOCOUNT ON

UPDATE dbo.Products SET
ReorderLevel = @.ReorderLevel,
ProductName = @.ProductName

WHERE ProductID IN
(
SELECT Item FROM dbo.StringArrayIntoTable(@.IDList, ',')
)

END

Kind regards

Scotty

|||

USE [Northwind]GO/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROC [dbo].[gv_sp_UpdatePOs](@.IDListvarchar(500),@.ReorderLevelint,@.ProductNamenvarchar(30) )ASBEGINSET NOCOUNT ON EXEC('Update dbo.ProductsSET ReorderLevel = (' +CAST( @.ReorderLevelas varchar(20) ) +') ,ProductName = (''' + @.ProductName +''')WHERE ProductID IN (' + @.IDList +')')END

Hello,
Try this.|||

Scotty, nice trick thanks.

Hasan, thanks. It is working now with casting.

pass string to stored procedure

I have a table that has the follow format
PageID (integer)
Member_Id (varchar(20))
in my VB.net application, i have two strings that that take the following
format
Pages="2,2,3,4,4"
Members= "member1,member2,member3,member4,member5"
is there any way to pass the string variables to a stored procedure and
perform some type of insert into statement?
i would want to do something like this in a stored procedure
insert into AccessTablePages (Member_Id,PageID)
values (@.Members, @.Pages)You should be able to adapt the methods in Erland Sommarskog's article here:
http://www.sommarskog.se/arrays-in-sql.html
Steve Kass
Drew University
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:8EB1C476-23D7-4D56-9480-E69431317A46@.microsoft.com...
>I have a table that has the follow format
> PageID (integer)
> Member_Id (varchar(20))
> in my VB.net application, i have two strings that that take the following
> format
> Pages="2,2,3,4,4"
> Members= "member1,member2,member3,member4,member5"
> is there any way to pass the string variables to a stored procedure and
> perform some type of insert into statement?
> i would want to do something like this in a stored procedure
>
> insert into AccessTablePages (Member_Id,PageID)
> values (@.Members, @.Pages)
>|||Looks promising.
thanks1
"Steve Kass" wrote:

> You should be able to adapt the methods in Erland Sommarskog's article her
e:
> http://www.sommarskog.se/arrays-in-sql.html
> Steve Kass
> Drew University
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:8EB1C476-23D7-4D56-9480-E69431317A46@.microsoft.com...
>
>

Pass fields as array to custom function?

I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?

You would need to create a function in the code window that accepts the values and inserts them into an array.

ex.

Code Snippet

Function CreateArray(ByVal field1 As String, ByVal field2 As String) As Array
Dim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function

I haven't tested it, but I think it will work.

Simone

|||

You can use the Split function.

http://msdn2.microsoft.com/en-us/library/6x627e5f(VS.80).aspx

|||That is true but you would still need to create a string to split using the required fields. Depending on the data those fields contain, you would need to be careful if they hold the delimeter used in the split function.