RE: Passing checked items into a sql statement

Hello Michael,

From your description, you're developing a data access ASP.NET page which
will use Gridview to let users select some certain records to update, and
when performing the udpate, you will need to dynamically put all those
selected GridView Row(keys) into your SQL query statement (in the "in
(.....)" block), correct?

Based on my experience, for such scenario, you need to use dynamic SQL
execution. Fixed compiled store-procedure can not fully meet the
requirement here. For dynamic SQL Execution, you have the following options
here:

1. programmatically use string concatenate in your .net data access code to
build the SQL query statement(includes all the items choosed in gridview)
and then execute it through SQLCommand.

2. If you have other paramters in addition to the dynamic key list and you
still want to use a stored-procedure, you can create a stored-procedure
with an additional parameters, and provide a VarChar parameter which used
to hold the var-length key list(such as "('key1','key2',....)" ). And you
can concatenate these expression as a complete SQL statement in your
store-procedure and use "sp_executesql").

#Using sp_executesql
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

BTW, for such dynamic SQL execution scenario, using stored procedure won't
help much on performance.

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks for your reply Michael,

SQL Server version is not a limitation here, both SQL Server 2000 an 2005
support sp_executesql(actually most modern DBMS should support such dynamic
sql execution mechanism).

The reason why I said for dynamic SQL(sql statement be determined at
runtime dynamically before execute) is as below:

** One of the big advantage of using Stored Procedure is it can help
precompile the sql statement and avoid parse and compile the SQL statement
at server-side every time we execute it.

** However, for sp_executeSql, it help execute sqlstatement which is
dynamically built. The SQL Server can not precompile it in advance. So when
we use stored-procedure to execute dynamic SQL, the server engine still
need to compile and parse the "dynamic sql" everytime we execute it. That's
why using SP or not doesn't matter so much for dynamic sql execution.

If you have any other question or anything we can help, please feel free to
let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.