Viewing 15 posts - 2,836 through 2,850 (of 7,619 total)
There's really not a better way. You'll need to repeat the column list for both the inserted and deleted tables for any method you use.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 9, 2019 at 5:08 pm
I'd use CROSS APPLYs to assign alias names to make the main SELECT statement and conditions easier to read and maintain. For example:
--...prior code same as before...
DELETE...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 7, 2019 at 2:18 pm
I'd use a function. You don't have to put in every db, you can call a function from another db. Let's say you created a "Shared_Functions" db to store the...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 2, 2019 at 5:39 pm
Interesting. It seems as if there is no variable data at all on a row, the entire "variable data" section of the row header is left out. But when the...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2019 at 7:50 pm
The 2 bytes for varchar len are always present, even if the column is NULL.
NULL itself is set as one bit per column, i.e., a "NULL bitmap". Every column will...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 30, 2019 at 3:28 pm
Maybe try CASTing to an explicit length?:
,CASE
WHEN T.[PD_ID] in (@Part)
THEN CAST('PD_ID_Match' AS varchar(30))
WHEN T.[Flat_PD_ID] in (@Part)
THEN CAST('FLAT_PD_ID_Match' AS varchar(30))
WHEN T.[Like_PD_ID] in (@Part)
THEN CAST('Like_PD_ID_Match' AS varchar(30))
WHEN T.[Flat_Like_PD_ID]...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 29, 2019 at 5:53 pm
Off the top, just streamline it as much as you can:
BEGIN
RETURN (
SELECT product = CASE...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2019 at 3:24 pm
What row was just FETCHed from, that is the row that SQL will delete, since that is the current position of the cursor.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 24, 2019 at 6:58 pm
Don't force SQL to fully reprocess the conditions before DELETEing each row, instead use WHERE CURRENT OF in the DELETE:
DECLARE @user VARCHAR(50)
DECLARE @role VARCHAR(50)
DECLARE db_cursor CURSOR LOCAL...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 24, 2019 at 6:41 pm
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 19, 2019 at 8:20 pm
FYI, just in case you care, I was able to get my code fully corrected.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 18, 2019 at 8:33 pm
I personally wouldn't use "+2", as it's nebulous unless you already know what it's doing; instead, I'd use the actual base date. And, if you shift from Excel, the base...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 18, 2019 at 6:51 pm
Under no circumstances should you use the actual SSN to link accounts. You might have to store the SSN in one place, but you sure don't have to store it...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 18, 2019 at 4:40 pm
Sorry, I thought you only wanted it down to the minute based on your initial description
duration in terms of minutes
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 17, 2019 at 5:52 pm
SELECT SUM(DATEDIFF(MINUTE, '1899-12-30', duration)) AS duration_in_mins
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 17, 2019 at 4:39 pm
Viewing 15 posts - 2,836 through 2,850 (of 7,619 total)