ExecuteNonQuery with parameters.

Question

I have to process a file that contains a large number of sql statements. The issue is that the sql statement contain parameters.

E.g. the file looks like this

declare @var1 as nvarchar;
set @var1 = 'value';
insert into table (field1, field2, field3) 
  values ('value1', 'value2', @var1);

repeates with the next set of three lines.

I was hoping that I would just be able to parse the file into three line blocks and fire them off using the ExecuteNonQuery, however the problem is that the sql statement contains a parameter.

Note: In my example @var1 is value in the script it picks up a server variable.

Answer 1

Sounds like a SQL Script file to me.

Can't you just use ISQL or SQLCmd?

[Addendum]

Sorry, I assumed a MSSQL back end since you referred to ExecuteNonQuery.

If these statements are consistent, you can inline the value.

* read and ignore first line. 
* read 2nd line and strip out value between quotes.
* read 3rd line, replace the @x string with the value from line 2.
* execute modified 3rd line.
* repeat

if they differ in data type, you will need to use the declared type to read and insert the value (i.e. don't use quotes for numeric).

At root, this really is a bad design and/or organizational issue.

Answer 2

Normally, when you run an existing SQL script you would split on GO and execute those big chunks. I haven't tried doing that with DECLARE statements though, have you tried to execute the script as is - i.e. the chunk that you posted?