Viewing 15 posts - 1,006 through 1,020 (of 7,613 total)
I say don't use IIF. It's a completely foreign coding approach to other SQL. And there's no genuine need for it, since CASE can do the same thing, and IIF...
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".
January 31, 2022 at 4:15 pm
If you want to rely on the existing zip always being fully padded on the left with zeros, you can do this:
CASE WHEN LEN(D.Zip) = 5 AND...
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".
January 28, 2022 at 10:04 pm
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this...
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".
January 28, 2022 at 8:21 pm
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some...
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".
January 27, 2022 at 9:00 pm
'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.
Try:
WHERE [...]
AND CONVERT(date, TransferredDate)...
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".
January 27, 2022 at 7:13 pm
And the view did not exist before the proc was called and still did not exist after it was called?
If so, put the SQL in a variable and print 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".
January 27, 2022 at 6:44 pm
The best way is to use > the next day rather than <= the end day. Like this:
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '18 Nov 2021'
SET...
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".
January 27, 2022 at 6:40 pm
I would think it would be. Why not try it with a small table and see?
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".
January 27, 2022 at 6:38 pm
SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC
How large (MB/GB) is the "History" table?
How is...
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".
January 27, 2022 at 6:37 pm
Keep in mind that the view is being created in the same db as the proc is in. Although presumably that is what you want to do.
What does "doesn't work"...
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".
January 27, 2022 at 4:07 pm
I'd clean up the prod problem first. You can use a backup and research to research the other stuff, if you think it's necessary.
Also, once you get the table reduced...
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".
January 27, 2022 at 3:53 pm
Don't rely on the DB_ID() to reference a db. Assume it could change at any time.
If you'll notice, in its system tables, MS stores the db name rather than 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".
January 27, 2022 at 3:49 pm
As Jeff noted, you should be able to do this with dynamic SQL.
But be aware that you cannot have any GO statements in the code. Just the main statement itself:
CREATE...
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".
January 27, 2022 at 3:25 pm
SELECT C1
FROM #Base
WHERE C2 IN ('122', '123')
GROUP BY C1
HAVING SUM(CASE WHEN C2 = '123' THEN 1 ELSE 0 END) > SUM(CASE WHEN C2 = '122' THEN 1...
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".
January 27, 2022 at 3:22 pm
Jeff,
You're right this is not ok. Just this morning I alse remembered myself, I have some scenes implemented, which are turning off particular lights even if the light are...
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".
January 26, 2022 at 6:18 pm
Viewing 15 posts - 1,006 through 1,020 (of 7,613 total)