If your dates column does not contain time information, you could get away with: WHERE dates BETWEEN ‘20121211’ and ‘20121213’ However, given your dates column is actually datetime, you want this WHERE dates >= ‘20121211’ AND dates < ‘20121214’ — i.e. 00:00 of the next day Another option for SQL Server 2008 onwards that retains … Read more
This error appears when you are using SQL Server Authentication and SQL Server is not allowed to access the bulk load folder. So giving SQL server access to the folder will solve the issue. Here is how to: Go to the folder right click ->properties->Security tab->Edit->Add(on the new window) ->Advanced -> Find Now. Under the … Read more
SELECT ordr_num as num, ordr_date as date, CASE WHEN @status<>’Cancelled’ THEN ordr_ship_with ELSE NULL END as shipwith FROM order WHERE ordr_num = @ordrNum
You use transactions when the set of database operations you are making needs to be atomic. That is – they all need to succeed or fail. Nothing in between. Transactions are to be used to ensure that the database is always in a consistent state. In general, unless there is a good reason not to … Read more
I can think of two obvious ways but both of them involve processing the TVP again. The first is simply to change the DELETE condition WHEN NOT MATCHED BY SOURCE AND target.AccountId IN(SELECT AccountId FROM @Items) THEN DELETE; The second is to use a CTE to restrict the target WITH cte as ( SELECT ItemId, … Read more
You can use SET @StartDateQ1 = CASE @q1 WHEN 1 THEN ‘20130401’ END to set the value of a single variable according to a CASE expression. If your real logic is more complicated (e.g. need to set multiple variables inside a condition) look at IF … ELSE instead. CASE is an expression not a flow … Read more
There are no good ways to do that. It is a limitation of stored procedures. Your options are: Switch the procedure to a User Defined Function. All over world, today, people are making stored procedures that should be functions. It’s an education issue. You situation is a good example why. If your procedure were instead … Read more
SQL Server 2005 and later has the HashBytes() function.
Simple change to syntax is all that is needed: create table ImagenesUsuario ( idImagen int not null identity(1,1) primary key ) By explicitly using the “constraint” keyword, you can give the primary key constraint a particular name rather than depending on SQL Server to auto-assign a name: create table ImagenesUsuario ( idImagen int not null … Read more
Use a comma to separate CTEs ;WITH SomeClause1 AS ( SELECT …. ) , SomeClause2 AS ( SELECT …. )