One useful trick in SQL is the ability use @var = function(…) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects: declare @badStrings table (item varchar(50)) INSERT INTO @badStrings(item) SELECT ‘>’ UNION ALL SELECT ‘<‘ UNION ALL SELECT ‘(‘ UNION ALL SELECT ‘)’ … Read more
You could use the ‘CASE .. WHEN .. THEN .. ELSE .. END’ syntax in SQL.
yes it does. See this
You should give alias to your table. So try this: SELECT A FROM ( SELECT A, B FROM TableA UNION SELECT A, B FROM TableB ) AS tbl WHERE B > ‘some value’
Rather than grouping, go about it like this… select * from account a join ( select account_id, row_number() over (order by account_id, id) – rank() over (order by account_id) as row_num from user ) first on first.account_id = a.id and first.row_num = 0
The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren’t equivalent to what’s stored. Perhaps BCP is writing some kind of header. I’m not sure. I found the following code here at the … Read more
If your dates are no more than 2047 days apart: declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ‘P’ ) n where dateadd(day, number, @dt) < @dtEnd I updated my answer after several requests to do … Read more
Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one; SELECT TOP 0 * INTO new_table FROM old_table Or to act a as source for column names Or as a way to return column details but no data to a client … Read more
Combining your advice and a script I found online, I made a procedure that will produce SQL you can run to perform a cascaded delete regardless of ON DELETE CASCADE. It was probably a big waste of time, but I had a good time writing it. An advantage of doing it this way is, you … Read more
OK, we have had similar issues like this before. The way we fixed this, was by making local parameters inside the SP, such that DECLARE @LOCAL_Contract_ID int, @LOCAL_dt_From smalldatetime, @LOCAL_dt_To smalldatetime, @LOCAL_Last_Run_Date datetime SELECT @LOCAL_Contract_ID = @Contract_ID, @LOCAL_dt_From = @dt_From, @LOCAL_dt_To = @dt_To, @LOCAL_Last_Run_Date = @Last_Run_Date We then use the local parameters inside the SP … Read more