Parameterized CREATE VIEW possible?

Question

I've got a (SQL Server 2005) database where I'd like to create views on-the-fly. In my code, I'm building a CREATE VIEW statement, but the only way I can get it to work is by building the entire query string and running it bare. I'd like to use parameters, but this:

SqlCommand cmd = new SqlCommand("CREATE VIEW @name AS SELECT @body");
cmd.Parameters.AddWithValue("@name", "foo");
cmd.Parameters.AddWithValue("@body", "* from bar");

tells me there's an error "near the keyword VIEW" (presumably the "@name") -- needless to say "CREATE VIEW foo AS SELECT * FROM bar" works like a champ.

Is this just not possible? If not, is there a better way to clean up the input before running the CREATE statement? In some cases, the query body could have user input and I'd just feel safer if there was some way that I could say "treat this as the body of a single select statement". Maybe what I'm asking for is just too weird?

---

FOLLOWUP 04 Nov: OK, yes, what I want is sort of like SQL injection when you get down to it, but I would like to at least minimize (if not totally remove) the option of running this command and dropping a table or something. Granted, the user this is running as doesn't have permissions to drop any tables in the first place, but I think you get the idea. I'd love to have a way of saying, in effect, "This statement will not alter any existing data in any way{ ... }".

The way it's coded right now is to do string concatenation like in friol's answer, but that does no sanitization at all. I'd feel better if I could at least scrub it for suspect characters, like ; or -- or what have you. I was hoping there might be a library function to do the scrub for me, or something along those lines.

Answer 1

Maybe I've not understood it correctly, but what prevents you to do:

viewname="foo";
viewwhere="* from bar";

SqlCommand cmd = new SqlCommand("CREATE VIEW "+viewname+" AS SELECT "+viewwhere);

Answer 2

Parameters are not simply string substitutions. That is why your code won't work.

Its like you cant do

sql = "select * from orders where orders_id in (?)"

and pass "1,2,3,5" as parameter.

Parameters are type checked and can only contain scalar values IIRC.

Answer 3

It looks to me like you are trying to create a dynamic query using parameters, which is not how a parameterized query is intended to work. They do not simply get concatenated into the string.

If what you are trying to prevent is SQL injection, what I would do is validate that the view name only contains alphanumerics and no T-SQL keywords. I would alos be very careful about dymanically creating the body.

Answer 4

SQL injection. You want it, that's the point. You should be concatenating this stuff.