How can I pass string parameter with table-value for stored procedure in SQL Server (using c#).  My code:  C#  SqlParameter tvpParam = cmd.Parameters.AddWithValue( "@inoper", "'New York','Boston'");         tvpParam.SqlDbType = SqlDbType.NVarChar;   Stored procedure:  ... WHERE     City IN (@inoper)...   What am I missing here?
3396
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?  SELECT * FROM Tags  WHERE Name IN ('ruby','rails','scruffy','rubyonrails') ORDER BY Count DESC   In this query, the number of arguments could be anywhere from 1 to 5.  I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to , I am open to that.
103578
I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTime as a value to a SqlParameter.  The SQL type in the stored procedure is 'datetime'.  Executing the sproc from SQL Management Studio works fine.  But everytime I call it from C# I get an error about the date format.    When I run SQL Profiler to watch the calls, I then copy paste the exec call to see what's going on.  These are my observations and notes about what I've attempted:  1)  If I pass the DateTime in directly as a DateTime or converted to SqlDateTime, the field is surrounding by a PAIR of single quotes, such as  @Date_Of_Birth=N''1/8/2009 8:06:17 PM''   2)  If I pass the DateTime in as a string, I only get the single quotes  3)  Using SqlDateTime.ToSqlString() does not result in a UTC formatted datetime string (even after converting to universal time)  4)  Using DateTime.ToString() does not result in a UTC formatted datetime string.  5)  Manually setting the DbType for the SqlParameter to DateTime does not change the above observations.  So, my questions then, is how on earth do I get C# to pass the properly formatted time in the SqlParameter?  Surely this is a common use case, why is it so difficult to get working?  I can't seem to convert DateTime to a string that is SQL compatable (e.g. '2009-01-08T08:22:45')  EDIT  RE: BFree, the code to actually execute the sproc is as follows:  using (SqlCommand sprocCommand = new SqlCommand(sprocName)) {     sprocCommand.Connection = transaction.Connection;     sprocCommand.Transaction = transaction;     sprocCommand.CommandType = System.Data.CommandType.StoredProcedure;     sprocCommand.Parameters.AddRange(parameters.ToArray());     sprocCommand.ExecuteNonQuery(); }   To go into more detail about what I have tried:  parameters.Add(new SqlParameter("@Date_Of_Birth", DOB));  parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime()));  parameters.Add(new SqlParameter("@Date_Of_Birth",      DOB.ToUniversalTime().ToString()));  SqlParameter param = new SqlParameter("@Date_Of_Birth",      System.Data.SqlDbType.DateTime); param.Value = DOB.ToUniversalTime(); parameters.Add(param);  SqlParameter param = new SqlParameter("@Date_Of_Birth",      SqlDbType.DateTime); param.Value = new SqlDateTime(DOB.ToUniversalTime()); parameters.Add(param);  parameters.Add(new SqlParameter("@Date_Of_Birth",      new SqlDateTime(DOB.ToUniversalTime()).ToSqlString()));   Additional EDIT  The one I thought most likely to work:  SqlParameter param = new SqlParameter("@Date_Of_Birth",       System.Data.SqlDbType.DateTime); param.Value = DOB;   Results in this value in the exec call as seen in the SQL Profiler  @Date_Of_Birth=''2009-01-08 15:08:21:813''   If I modify this to be:  @Date_Of_Birth='2009-01-08T15:08:21'   It works, but it won't parse with pair of single quotes, and it wont convert to a DateTime correctly with the space between the date and time and with the milliseconds on the end.  Update and Success  I had copy/pasted the code above after the request from below.  I trimmed things here and there to be concise.  Turns out my problem was in the code I left out, which I'm sure any one of you would have spotted in an instant.  I had wrapped my sproc calls inside a transaction.  Turns out that I was simply not doing transaction.Commit()!!!!!  I'm ashamed to say it, but there you have it.  I still don't know what's going on with the syntax I get back from the profiler.  A coworker watched with his own instance of the profiler from his computer, and it returned proper syntax.  Watching the very SAME executions from my profiler showed the incorrect syntax.  It acted as a red-herring, making me believe there was a query syntax problem instead of the much more simple and true answer, which was that I need to commit the transaction!    I marked an answer below as correct, and threw in some up-votes on others because they did, after all, answer the question, even if they didn't fix my specific (brain lapse) issue.
70360
Is there a way to get stored procedures from a SQL Server 2005 Express database using C#? I would like to export all of this data in the same manner that you can script it our using SQL Server Management Studio, without having to install the GUI.  I've seen some references to do thing via the PowerShell but in the end a C# console app is what I really want.  To clarify....  I'd like to script out the stored procedures. The list via the Select * from sys.procedures is helpful, but in the end I need to script out each of these.
5648
Declared a string and assigned SOQL value as shown below. While running I am getting an error.   String country = 'United States'; String accountQuery = 'SELECT Id,BillingPostalCode,County_Name__c '+      'FROM Account WHERE zipCodeLen__c =9 AND AND BillingCountry = '+ Country;   System.QueryException: unexpected token: 'AND'  System.QueryException: expecting a colon, found 'United'
110974
I am just wondering if MariaDB or MySQL implements the &lt;table value constructor&gt; in the SQL Spec. In  and ?  SELECT * FROM ( VALUES (1) ) AS t(x);  x  ---  1 (1 row)   (syntax from ).
14635
private string? typeOfContract {   get { return (string?)ViewState["typeOfContract"]; }   set { ViewState["typeOfContract"] = value; } }   Later in the code I use it like this:  typeOfContract = Request.QueryString["type"];   I am getting the following error at the declaration of typeOfContract line stating:     The type 'string' must be a non-nullable value type in order to use   it as parameter 'T' in the generic type or method   'System.Nullable&lt;T>'   Any ideas? Basically, I want to make sure that "type" exists in the QueryString before performing an action.
42281
I have a controller with an action method as follows:  public class InventoryController : Controller {     public ActionResult ViewStockNext(int firstItem)     {         // Do some stuff     } }   And when I run it I get an error stating:     The parameters dictionary does not contain a valid value of type 'System.Int32' for parameter 'firstItem'. To make a parameter optional its type should either be a reference type or a Nullable type.   I had it working at one point and I decided to try the function without parameters.  Finding out that the controller was not persistant I put the parameter back in, now it refuses to recognise the parameter when I call the method.  I'm using this url syntax to call the action:  http://localhost:2316/Inventory/ViewStockNext/11   Any ideas why I would get this error and what I need to do to fix it?   I've tried adding another method that takes an integer to the class it it also fails with the same reason.  I've tried adding one that takes a string, and the string is set to null.  I've tried adding one without parameters and that works fine, but of course it won't suit my needs.
110185
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.  A number of the Stored Procedures have a pattern like this:   Create Temp table Build up dynamic SQL query to insert a bunch of records, e.g.  DECLARE @sql VARCHAR(MAX) SET @sql = 'INSERT INTO @tempTable SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'  IF @someParam = [SomeValue]     SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';  IF @someOtherParam = [SomeOtherValue]     SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';  Execute this dynamic sql  EXEC(@sql);  Select from the temp table and bring in a bunch of additional information to return to the client.  SELECT     ...  FROM     @tempTable    INNER JOIN ...    My immediate thoughts are:   There is dynamic SQL, so no cache plans, meaning plans generated every time. There is an INSERT SELECT pattern, so table locking is more likely to be an issue.   I have re-written some of the Stored Procedures in this way instead:      SELECT          ...      FROM          MyTable          INNER JOIN ...      WHERE      (         @someParam != SomeValue         OR         SomeColumn = SomeValue     )     AND     (         @someOtherParam != SomeOtherValue         OR         SomeOtherColumn = SomeOtherValue     )           From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.  I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.  Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?  The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.
120547
What is meant by nvarchar?  What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
52427
How do I escape a string in SQL Server's stored procedure so that it is safe to use in LIKE expression.  Suppose I have an NVARCHAR variable like so:  declare @myString NVARCHAR(100);   And I want to use it in a LIKE expression:  ... WHERE ... LIKE '%' + @myString + '%';   How do I escape the string (more specifically, characters that are meaningful to LIKE pattern matching, e.g. % or ?) in T-SQL, so that it is safe to use in this manner?  For example, given:  @myString = 'aa%bb'   I want:  WHERE ... LIKE '%' + @somehowEscapedMyString + '%'   to match 'aa%bb', 'caa%bbc' but not 'aaxbb' or 'caaxbb'.
87267
I am trying to check how the table size is going to be increased after adding new column with default values.  I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.   I am using the following statements:  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] ADD [Loops] NVARCHAR(900) CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] DROP CONSTRAINT [DF_Loops]  ALTER TABLE [dbo].[Table_A] DROP COLUMN [Loops];   but the sp_spaceused always returns:  name                                               rows                 reserved           data               index_size         unused ------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------ Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB   Could anyone tell what I am doing wrong?
108487
I have the code below (I've included what I believe are all relevant sections):  private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;"; public bool read(string id) {     level = -1;     MySqlCommand m = new MySqlCommand(readCommand);     m.Parameters.Add(new MySqlParameter("", val1));     m.Parameters.Add(new MySqlParameter("", val2));     MySqlDataReader r = m.ExecuteReader();     if (r.HasRows)         level = Convert.ToInt32(r.GetValue(0).ToString());     r.Close();     return true; }   When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?
108771
In the below scenario  i am Accepting the ID of object in the string form : What will be the best way to avoid the SQL injection for ASId.  public static List&lt;AB__c&gt;ABC(string ASId)         records = [select id, name,abc__c,der__f, from AB__c WHERE der__f = :ASId WITH SECURITY ENFORCED ORDER BY AB__c ASC NULLS LAST];
68349
In MS SQL Server, I create my scripts to use customizable variables:  DECLARE @somevariable int   SELECT @somevariable = -1  INSERT INTO foo VALUES ( @somevariable )   I'll then change the value of @somevariable at runtime, depending on the value that I want in the particular situation. Since it's at the top of the script it's easy to see and remember.  How do I do the same with the PostgreSQL client psql?
1422
I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program.  These statements need to be separated by GO statements, but SqlCommand.ExecuteNonQuery() does not like GO statements. My solution, which I suppose I'll post for reference, was to split the SQL string on GO lines, and execute each batch separately.  Is there an easier/better way?
18694
We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ).   The same script works fine, if I just execute it through SQL Server Management Studio, but through Powershell it's not rendering these special characters properly.  Here how we access the script file to execute on all databases on instance  Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 invoke-Sqlcmd -InputFile "C:\DailyPatch\run.sql" | Out-File -filePath "C:\DailyPatch\dbname.txt"  $text=Get-Content C:\DailyPatch\dbname.txt $a = $text.length $a=$a-3  $logFile = 'C:\DailyPatch\dblog.txt'  for ($i=3 ; $i -le $a ;$i++) {        write-host $text[$i]     $error.clear()      Invoke-sqlcmd -inputfile "C:\DailyPatch\Script4AllDBs.sql"-database $text[$i]-OutputSqlErrors $true|Out-File -filePath "C:\DailyPatch\dbname.txt"      foreach ($rr in $error) {         $text[$i] &gt;&gt; $logFile         $rr &gt;&gt; $logFile     } }   How to set collation in Powershell to avoid such kind of issues?  ps. Unicode thing is already handled on script side  Added: Special characters are being changed to � (Replacement Character)
31999
We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).    Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in).  This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.  Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.  Would really appreciate any thoughts/feedback people might have.  Thanks Dave