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 spTestTempTableGO------------------ 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 RECOMPILEAS -- 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 existsUSE tempdbGOPRINT 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]ENDelseBEGIN PRINT 'Table not dropped'ENDGOUSE OurDBNameGOCREATE 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 12 some text 2 text for the memo field 23 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 UPDATEin 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 sProcis 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.