StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I declare and use T-SQL variables across multiple SqlCommands using the same SqlConnection object to perform multiple inserts?
text
Body
copied!<p>I want to load a list of records given a possibly lengthy list of usernames (anywhere from one to thousands of usernames). Disregard how the name(s) are chosen, and assume they cannot be determined from any existing data in the database. This applies to SQL Server 2005.</p> <p>I specifically want to <strong><em>avoid</em></strong> using a <strong>single select statement with thousands of expressions in the where clause</strong>, which would result in an excessively lengthy command text for the SqlCommand object (e.g. <code>...where n='bob000001' or n='bob000002' or ... or n='bob003000'</code>). Sounds reasonable?</p> <p>I have decided to perform the select by populating a simple table variable with the usernames, then performing a select/join between the table variable and the table with the user data.</p> <p>So, the first thing I need to do is populate the table variable. I have some problems here:</p> <ul> <li>T-SQL syntax prior to SQL Server 2008 is verbose for inserting multiple rows into a table in a single statement, requiring something like <a href="https://stackoverflow.com/questions/857120/how-to-insert-multiple-rows-at-once-to-a-table-in-sql-server-2005/857160#857160">multiple selects and union alls</a>.</li> <li>Rather than use verbose syntax of SS2005, or even the terse syntax available in SQL Server 2008, I am avoiding lengthy command texts altogether and just using multiple commands over a single connection.</li> <li>Declaring a table variable in one SqlCommand, produces a "must declare the scalar variable" error when I try to use it in subsequent SqlCommands.</li> <li>Involving stored procedures in any way may still involve passing huge strings, or may prevent variables from persisting outside the scope of the stored procedure. Assume creating stored procedures is not an option.</li> </ul> <p>That third point is really the problem I'm trying to solve now. I've seen examples where people (claim to) successfully declare and use a variable in a single SqlCommand without an error. How can this be achieved when using multiple SqlCommand instances? I read that variables will persist for a single connection across multiple commands. Might involving a transaction help in some way?</p> <p>Finally, keep in mind that I don't want to use temporary tables; doing so would offer a simple solution, but it also avoids the question I'm asking concerning variables and multiple SqlCommands; however, if you truly think that's the best option, feel free to say so.</p> <p>Here is a code snippet that shows what's happening:</p> <pre><code>public static List<Student> Load( SqlConnection conn, List<StudentID> usernames ) { //Create table variable SqlCommand command = new SqlCommand( "declare @s table (id varchar(30))", conn ); command.ExecuteNonQuery(); //Populate a table variable with the usernames to load command = new SqlCommand( "insert into @s (id) values (@p)", conn ); command.Parameters.Add( "@p", SqlDbType.VarChar ); int len = usernames.Count; for (int i = 0; i < len; i++) { command.Parameters["@p"].Value = usernames[i].ToString(); command.ExecuteNonQuery(); //ERROR: must declare scalar variable @s } //Select all students listed in the table variable command = new SqlCommand( "select StudentID, FName, LName, [etc.] from Student inner join @s on StudentID = @s.id order by StudentID", conn ); //Execute the query to get the student info from the database. List<Student> students = new List<Student>() using(SqlDataReader reader = command.ExecuteReader()) { //code to load results and populate students list } return students; } </code></pre> <p>Note: I'm aware that an SqlCommand involving parameters internally calls a stored procedure, which would normally prevent persisting variables across multiple SqlCommands, but the first query that declares the table variable doesn't involve parameters (i.e. no reference to command.Parameters.AddWithValue is made), so it should be in scope for later commands that may call stored procedures.</p> <p>Edit: To use a temp table, one just has to change <code>@</code>s to <code>#</code>s and <code>declare @</code>s table" to <code>create table #</code>s, which is nice. One may also want to drop the temp table at the end, but it's not necessary.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload