Case statement always skipping to ELSE in stored procedure
sql,sql-server-2008,stored-procedures,case,tvp
Comparisons to NULL are always false. Your table contains no sales dates. The Sales period check Getutcdate() >= tvp.SaleFromDate AND Getutcdate() < tvp.SaleToDate will always fail. This means that if @Price is NULL and SalePrice isn't, only the ELSE statement is valid....
Dapper Table Valued Parameter OleDB returns Unspecified Error
You don't indicate what the backend is here. If the backend is SQL Server, frankly: use SqlConnection. If the backend is something else, it may or may not even work. ADO.NET providers in general are not required or expected to support table-valued-parameters. Note that at the moment your code isn't...
Procedure inserts only one record
sql,sql-server-2008,tsql,stored-procedures,tvp
Basically, since your variable cannot hold more than one value at the same time, with this statement: SELECT @OfferId = a.OfferId ,@CountryId = a.CountryId ,@VatRateId = a.VatRateId ,@SalePrice = a.SalePrice ,@SaleFromDate = a.SaleFromDate ,@SaleToDate = a.SaleToDate ,@DefaultPrice = a.DefaultPrice FROM @OfferPriceTVP a; you are holding only one record of your...
Table valued parameter with primary keys changes
sql-server,composite-primary-key,table-valued-parameters,tvp
Table value parameters is used to pass multiple rows of data through parameters to, for example, stored procedure. In your case the use case could be the next: CREATE TYPE [dbo].[tvp_Prices] AS TABLE( [ID] [int] NOT NULL, [Date] [smalldatetime] NOT NULL, [Value] [float] NOT NULL ); CREATE TABLE [dbo].[Prices]( [ID]...
Sql Server TVP Merge with Where/Case Statement
sql,sql-server,sql-server-2008,tvp
You don't need a MERGE. You need conditional SET ColumnX = (this or that) statements. Something like this. Update Listings Set L.OriginalSubdivisionName = case when L.OriginalSubdivisionName IS NULL Then L.SubdivisionName else L.OriginalSubdivisionName / * a little trick to keep it the same value */ end , L.SubdivisionName = case when...