DDL via Command.ExecuteNonQuery

[zdrakec]

Hello all:

Consider the following:

CREATE PROCEDURE procname
@input1 int,
@input2 int
AS
execute some stuff here --this is a comment
END

Suppose this were entered in a text box, including the line feeds and
carriage returns. If I pass this to the ExecuteNonQuery method of a
command object, it will fail. If I strip out the comment and also
replace the line feeds/carriage returns with spaces, it will succeed,
but if I review the stored procedure with, say, Enterprise manager, the
procedure looks like this:

CREATE PROCEDURE procname @input1 int, @input2 int AS execute some
stuff here END

in other words, not having included line feeds in the command, they do
not end up in the script either.
What I would like to do is have the script execute, as it appears, just
as though I had entered it in, say, Query Analyzer.

Suggestions?

Thanks much,
zdrakec

 

[CT]

zdrakec,

If I assign the value of the TextBox.Text property to the
SqlCommand.CommandText property, I get the linefeeds and crs as entered.

A word of caution though; if you let your users enter the DDL like this,
they can severely damage your data and or structure.