Menu
I have a parameterized SQL query targetted for SQL2005 which is dynamically created in code, so I used the ADO.NET SqlParameter class to add sql parameters to SqlCommand.
In the aforementioned SQL I select from a Table Valued Function with has defaults. I want my dynamic sql to sometimes specify a value for these default parameters, and other times I want to specify that the SQL DEFAULT - as defined in the Table Valued Function - should be used.
To keep the code clean I didn't want to dynamically add the SQL DEFAULT keyword and parameterize it when a non-default is to be used, I just wanted to set DEFAULT as the value of my SQLParameter.
Can I? What is best practice in such an instance?
269
The default is an input parameter. To specify an output parameter, the OUTPUT keyword must be specified in the definition of the parameter in the CREATE PROCEDURE statement. The procedure returns the current value of the output parameter to the calling program when the procedure exits.
Should use something like the following: SqlCommand cmd = new SqlCommand("INSERT INTO Product_table Values(@Product_Name, @Product_Price, @Product_Profit, @p)", connect); cmd. Parameters. Add("@Product_Name", SqlDbType.
SQL query parameters take the place of literal values only.
You can't send an SQL keyword as the value of a parameter, just as you cannot send a table identifier, column identifier, list of values (e.g. for an IN predicate), or an expression. The value of the parameter is always interpreted as a literal value, as if you had included a quoted string literal or a numeric literal in your query.
Sorry, but you have to include an SQL keyword as part of the SQL query before you prepare that query.
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
