Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Passing a recordset to sproc (from ASP.net)

Author  Topic 

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-07 : 19:15:18
In another thread, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58702, I was told that a table parameter can't be passed to a sProc or UDF as a parameter.
Several people suggested using techniques such as passing a string and parsing values out of it.

If varchar wasn't limited to 8000 chars in SS2k, I wouldn't hesitate to use the pass-everything-as-a-string method.
Unfortunately, in our app data of just one field could be a memo, and we could have 200 fields to pass, so this isn't an answer until ss2005.

I worked with a colleague today on a hunch that creating a temporary table in asp.net may provide an answer. We built a prototype, which I would like you to comment on.
My questions are below the code.

What are the advantages of this technique? First, you can pass a string longer than 8000 characters this way (as ntext, for example).
Second, you can pass a complete record including BLOBs such as ntext. And third, you can pass a complete multirow recordset with unlimited no of fields and rows.

Why do we need this? Our form object in asp.net can have any number of linkboxes, which list linked records via a linking table (we manage a lot of many to many links).
These linkbox values (IDs of linked records) are stored in session variables. When the user clicks Save, we want to insert or delete records in all linking tables as needed in SS, not in the middle tier.
The motivation for this is that we intend to perform operations in SS that will require the same functionality, perhaps as triggers that manage link table data, perhaps a relational import utility or something else.
Also it seems that keeping this functionality close to data will give us the greatest flexibility in improving performance down the road.

Our application supports a fully customizable schema and dynamic forms defined in our meta data. The whole app is meta data-driven.
Any solution we build has to be generic. That's why I use WITH RECOMPILE in the sProc.

If you want to run this, replace 'OurDBName' in all code below with the name of the database in which you will run this.
You will need to define a connection string in function TempTable() in ConfigurationManager.ConnectionStrings("OurConnectionString").ConnectionString.

Here is the SQL code. I create the procedure and then test it by creating a temp table and reading it through my sproc.

IF EXISTS (SELECT name 
FROM sysobjects
WHERE name = N'spTestTempTable'
AND type = 'P')
DROP PROCEDURE spTestTempTable
GO

------------------ spTestTempTable ----------------------

CREATE PROCEDURE spTestTempTable
@sTableName varchar(200)

-- AUTHOR: MI 12/7/05
-- PURPOSE:
-- Testing the scope of local and global temporary tables.
-- This sProc captures the temp table and returns it back
-- as a recordset. This is just a proof of concept.
-- The sp could process the data and write to real tables or
-- return a recordset with formatted data, new columns, etc.
-- In this example I use global temp tables, but in the
-- asp.net code below we use local tables.
-- PARAMETERS:
--
-- RETURNS:
--
-- EXAMPLE:

WITH RECOMPILE
AS
-- Here we can perform formatting, populate linking tables,
-- perform validation, etc, then either INSERT/UPDATE
-- the data in the real tables or return the recordset.
EXEC('SELECT * FROM ['+@sTableName+']')
GO

-- =============================================
-- Example to execute the store procedure
-- =============================================
-- Delete the temp table if it exists
USE tempdb
GO
PRINT object_id(N'[##table]')
if exists (select * from dbo.sysobjects where id = object_id(N'[##table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table being dropped'
drop table [##table]
END
else
BEGIN
PRINT 'Table not dropped'
END
GO
USE OurDBName
GO

CREATE TABLE ##table
(ID int,
Text varchar(20),
MMO_Memo ntext)
INSERT INTO ##table SELECT 1, 'some text 1', 'text for the memo field 1'
INSERT INTO ##table SELECT 2, 'some text 2', 'text for the memo field 2'
INSERT INTO ##table SELECT 3, 'some text 3', 'text for the memo field 3'

EXEC spTestTempTable
@sTableName = '##table'
GO

-------------------------
RESULT:

1 some text 1 text for the memo field 1
2 some text 2 text for the memo field 2
3 some text 3 text for the memo field 3


Here is the asp.net code (VS2005). Here we create a temp table named with a hyphenless GUID, populate it and run our sProc.
We send the table name via a parameter. Then we count the rows in the data the reader gets from the sProc to prove that the sproc returned them.
Finally we drop the table.

    Public Function TempTable() As String

Dim settings As String = ConfigurationManager.ConnectionStrings("OurConnectionString").ConnectionString
'OurConnectionString is:
'Data Source=SERVERNAME;Initial Catalog=OurDBName;User ID=someuser;Password=somepassword
Dim sqlConnection1 As New Data.SqlClient.SqlConnection(settings)

Dim cmd As New SqlClient.SqlCommand
Dim reader As SqlClient.SqlDataReader
Dim mColl As New Collection

Dim lRet As Long
Dim TempTableName As String = "#" & Replace(GenGuid(), "-", "")

Dim sBigString As String
Dim i As Long

sBigString = ""

For i = 1 To 10000
sBigString = sBigString & "BigString "
Next


cmd.Connection = sqlConnection1
sqlConnection1.Open()
cmd.CommandType = CommandType.Text

'cmd.CommandText = "USE tempdb if exists (select * from dbo.sysobjects where id = object_id(N'[" & TempTableName & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [" & TempTableName & "]"
'lRet = cmd.ExecuteNonQuery

cmd.CommandText = "USE OurDBName " & _
"CREATE TABLE " & TempTableName & " (ID int, Text varchar(20), MMO_Memo ntext) " & _
"INSERT INTO " & TempTableName & _
" SELECT 1, 'some text 1', '1 " & sBigString & "' " & _
"INSERT INTO " & TempTableName & _
" SELECT 2, 'some text 2', '2 " & sBigString & "' " & _
"INSERT INTO " & TempTableName & _
" SELECT 3, 'some text 3', '3 " & sBigString & "' "
lRet = cmd.ExecuteNonQuery

cmd.CommandText = "USE OurDBName EXEC spTestTempTable @sTableName = '" & TempTableName & "'"
reader = cmd.ExecuteReader

lRet = 0
If reader.HasRows Then
Do While reader.Read()
lRet = lRet + 1
Loop
Else
TempTable = 0
End If
reader.Close()

cmd.CommandText = "USE TempDB DROP TABLE " & TempTableName
cmd.ExecuteNonQuery()

TempTable = lRet.ToString

sqlConnection1.Close()


End Function


Private Function GenGuid() As String

Dim guidValue As Guid = Guid.NewGuid
Return guidValue.ToString

End Function





I have the following questions:

Will it be a problem to run this a lot? A form in which the user has modified 20 linkboxes for example will require writing a temp table from asp.net 21 times
(1 time for the main fields and 20 times for linking tables). The sProc would read the first table and perform an INSERT or UPDATE
in the main real table, then it would read the other 20 temp tables and perform INSERT/DELETEs in the linking tables as needed.
Typically only a few values are modified in linkboxes, but someone can add 10,000 Contacts to a marketing mailing activity, for ex.
This will require performing 10000 inserts. But this is no different than doing the same thing directly except that our sProc
is not compiled. Can we do some kind of a bulk insert instead?

How well will this scale? We typically have 50-100 users and up to 1M records in main tables. Some linking tables can have millions of records.

What are the implications of this technique on security? How are permissions managed for temp tables? We tested this under sa logon.
Can non-admin users create a temp table and read from it? If not, what are the implications of asp.net creating temp tables under a logon with adequate permissions?

We've been able to use local temp tables (#tablename). I think this is preferred to global (##tablename) because they get cleaned up after the connection ends. Is there an advantage to using global temp tables instead?

Anything else? TIA.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-12-07 : 19:31:13
You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.
Go to Top of Page

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-07 : 19:43:15
quote:
Originally posted by DustinMichaels

You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.



How can I overcome the 8000 byte limit?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 00:21:18
>>How can I overcome the 8000 byte limit?

In this topic search for 8000
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-12-08 : 12:08:41
quote:
Originally posted by Marioi

quote:
Originally posted by DustinMichaels

You could pass in a text parameter that contains XML and use the OPENXML function to convert your xml into a table. Maybe this approach would work out better for you.



How can I overcome the 8000 byte limit?



Text fields can contain up to 2 gigabytes of space. You would have plenty of room if you tried using the XML text parameter.
Go to Top of Page

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-12 : 10:12:45
quote:
Originally posted by DustinMichaels
Text fields can contain up to 2 gigabytes of space. You would have plenty of room if you tried using the XML text parameter.



I can't declare a parameter of ntext type, so how can I pass 2GB to the proc? The largest parameter type that I see available is 8000 chars. I am in SS2k.
Go to Top of Page

Marioi
Posting Yak Master

132 Posts

Posted - 2005-12-12 : 10:21:37
quote:
Originally posted by madhivanan
In this topic search for 8000
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210 height="1" noshade id="quote">


I saw your suggestion about declaring multiple varchars(8000) and splitting a big string between them. However, my proc is being called by asp.net, and I don't know how big the string might be. To support only 10MB, I would have to declare 1250 parameters. Using a temp table, I can pass a recordset of any size.
Go to Top of Page
   

- Advertisement -