sql stored procedure question

sql stored procedure question

• f_scholer

  btw, the "hard coded" stored procedure (the one with the table name specified in it) is used with the ado.net 2.0 bulk insert capability (by setting the UpdateBatchSize to a specified amount other than 1). The dataset associated with the dataadapter, then does all the inserts at one shot (rather than calling the sproc numerous times 1 by 1 for each record).

  I just need to find a way for the sproc to recognized the dynamic table name I'm passing down to it for the insert.

  Derek, thanks for the reply, but I've used dyanamic sql before to execute the sproc once, but the above sproc will get executed thousands of times with one shot (see above). The dynamci option, I believe is not possible, because all the values (for the insert), will change on every iteration of executing the sproc.

  Any other ideas

  
  
  

• dholt

  at a very basic level, you have to supply your procedure with some inputs. Be it a table name and the values. If you pass it a table name and the values then why couldnt you loop through the values/records and generate an insert statement for each record of values

  Put the code in here and I assure well solve it.

  Derek

  
  

• ika

  btw, the "hard coded" stored procedure (the one with the table name specified in it) is used with the ado.net 2.0 bulk insert capability (by setting the UpdateBatchSize to a specified amount other than 1). The dataset associated with the dataadapter, then does all the inserts at one shot (rather than calling the sproc numerous times 1 by 1 for each record).

  I just need to find a way for the sproc to recognized the dynamic table name I'm passing down to it for the insert.

  Derek, thanks for the reply, but I've used dyanamic sql before to execute a sproc, but the above sproc will get executed thousands of times with one shot (see above). The dynamci option, I believe is not possible, because all the values (for the insert), will change on every iteration of executing the sproc.

  Any other ideas

  
  
  

• Kaktusbluete

  note sp_exectuesql requires unicode (nvarchar) input
  

• Marc68

  dynamic sql

  • sp_executesql @sqlString
  • exec @sqlString

  CREATE PROC dbo.DynamicInsert @Table varchar(50) = ''

  AS

  BEGIN

  SET NOCOUNT ON

  declare @sql nvarchar(100)

  set @sql = 'insert ' + @Table + ' select ' + [values go here]

  exec sp_executesql @sql

  END

  
  

• audreynsarah

  You could use dynamic SQL but it is not advisable. There are security implications. You need to grant insert permission on the tables for all users calling the stored procedure. It looks like you are trying to use this from ADO.NET and it will be good to post in the Visual Studio Data Access forums or similar one about this. This doesn't look like a TSQL problem. With .NET 2.0 there is a new BulkCopy class that you can use to bulk insert rows from a stream efficiently. It depends on your requirements and it is not clear what you are trying to do but it seems like a data access API question not TSQL.
  
  

• BSHOE

  dynamic sql

  • sp_executesql @sqlString
  • exec @sqlString

  CREATE PROC dbo.DynamicInsert @Table varchar(50) = ''

  AS

  BEGIN

  SET NOCOUNT ON

  declare @sql nvarchar(100)

  set @sql = 'insert ' + @Table + ' select ' + [values go here]

  exec sp_executesql @sql

  END

  
  
• sql stored procedure question