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
WHERE name = N'spTestTempTable'
AND type = 'P')
DROP PROCEDURE spTestTempTable
------------------ spTestTempTable ----------------------
CREATE PROCEDURE spTestTempTable
-- AUTHOR: MI 12/7/05
-- 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.
-- 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+']')
-- Example to execute the store procedure
-- Delete the temp table if it exists
if exists (select * from dbo.sysobjects where id = object_id(N'[##table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
PRINT 'Table being dropped'
drop table [##table]
PRINT 'Table not dropped'
CREATE TABLE ##table
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'
@sTableName = '##table'
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
'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 "
cmd.Connection = sqlConnection1
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
TempTable = 0
cmd.CommandText = "USE TempDB DROP TABLE " & TempTableName
TempTable = lRet.ToString
Private Function GenGuid() As String
Dim guidValue As Guid = Guid.NewGuid
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.