How can I declare and use T-SQL variables across multiple SqlCommands using the same SqlConnection object to perform multiple inserts into a table variable?

Answer 1

Not sure if this is the best solution, but I believe you can batch multiple commands by wrapping them in a BEGIN/END block, and use a single call to run all of them.

Answer 2

Use a temporary table which persists for the duration of the session/connection (multiple calls). Table variables have scope only for the batch, which is basically one call.

Answer 3

I dont know how to solve your question, however my alternative approach to your problem would instead be to have a stored procedure that accepted a comma delimited list of ID's, inserted those ID's into a temporary table and then do a "SELECT WHERE IN", for example (altered from another blog):

CREATE PROC dbo.SelectStudents
(
    @StudentIdList varchar(8000)
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #StudentIdList (
     StudentId int,
    )

    DECLARE @StudentId varchar(10), @Pos int

    SET @StudentIdList = LTRIM(RTRIM(@StudentIdList)) + ','
    SET @Pos = CHARINDEX(',', @StudentIdList, 1)

    IF REPLACE(@StudentIdList, ',', '') <> ''
    BEGIN
     WHILE @Pos > 0
     BEGIN
      SET @StudentId = LTRIM(RTRIM(LEFT(@StudentIdList, @Pos - 1)))
      IF @StudentId <> ''
      BEGIN
       INSERT INTO #StudentIdList (StudentId) VALUES (CAST(@StudentId AS int))
      END
      SET @StudentIdList = RIGHT(@StudentIdList, LEN(@StudentIdList) - @Pos)
      SET @Pos = CHARINDEX(',', @StudentIdList, 1)
     END
    END 

    SELECT  * 
    FROM  dbo.Students
    WHERE StudentId IN
    (
     SELECT StudentId FROM #StudentIdList
    )
END

You can then call this stored procedure with a comma delimtied list of id's:

exec dbo.SelectStudents '1,2'

You will need to make sure that your formatted list of id's is no longer than 8000 characters (and make multiple calls to the database if it is) however this method will be far more efficient and requires only a large parameter, not a long command text - making large numbers of trips to the database to insert each of the Id's will be extremely slow.

Similar approaches exist for passing in the list of Id's as Xml (although my preference would probably be to go for the simplicity of delimited strings).

Also, it shoud be possible to use the above technique even if for whatever reason you aren't able to create stored procedures on your database.

Answer 4

What's wrong with lengthy command texts? I've executed several kilobyte big query's in one call. SQL2005 supports it, and I think it's better than round-tripping all the time.

Why not create the query like this?

select StudentID, FName, LName, [etc.] from Student 
where StudentID in ('bob000001', 'bob000002', [etc.])

Answer 5

Erland's article should address your issues:

http://www.sommarskog.se/arrays-in-sql-2005.html