November 6, 2020 at 8:36 pm
Need to turn below in to @tsqlvar that I can execute. In native format runs great can't get quotes to work. Want to be able to do declare @tsql nvarchar(max), @tql = statement. and then exec @tsql.
Here is native sql which runs fine
DECLARE @startdate datetime, @enddate datetime;
SET @startdate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0);
SET @enddate = DATEADD(ms, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0));
SELECT
CASE Station_Type
WHEN 'ACART'
THEN 'Anesthesia-Rx'
WHEN 'DOSE'
THEN 'AcuDose-Rx'
ELSE 'AcuDose-Rx'
END AS Station_Type,
Station_Id,
Station_Desc,
Unit_Desc,
Event_Dttm,
Event_Name,
CONVERT(varchar(120), REPLACE(Full_Site_Drug_Desc, '"', '')) Full_Site_Drug_Desc,
Begin_Inventory_Level,
Expected_Inventory_Level,
End_Inventory_Level,
Trans_Qty,
Cabinet_Num,
Drawer_Num,
Pocket_Num,
Displayed_Cabinet_Num,
Displayed_Drawer_Num,
Displayed_Pocket_Num,
Site_User_Id,
User_Name,
Pat_Id,
Site_Patient_Id,
Pat_Name,
Waste_Method,
Witness_Name,
Override_Flag,
ConnectRn_Flag,enter code here
Event_Id,
Alias_Id,
Drug_Dose_Id
FROM CRX_DATA.dbo.AHI_CAB_EVENT
WHERE Event_Dttm >= @startdate
AND Event_Dttm < @enddate;
DECLARE @startdate datetime, @enddate datetime;
SET @startdate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0);
SET @enddate = DATEADD(ms, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0));
SELECT
CASE Station_Type
WHEN 'ACART'
THEN 'Anesthesia-Rx'
WHEN 'DOSE'
THEN 'AcuDose-Rx'
ELSE 'AcuDose-Rx'
END AS Station_Type,
Station_Id,
Station_Desc,
Unit_Desc,
Event_Dttm,
Event_Name,
CONVERT(varchar(120), REPLACE(Full_Site_Drug_Desc, '"', '')) Full_Site_Drug_Desc,
Begin_Inventory_Level,
Expected_Inventory_Level,
End_Inventory_Level,
Trans_Qty,
Cabinet_Num,
Drawer_Num,
Pocket_Num,
Displayed_Cabinet_Num,
Displayed_Drawer_Num,
Displayed_Pocket_Num,
Site_User_Id,
User_Name,
Pat_Id,
Site_Patient_Id,
Pat_Name,
Waste_Method,
Witness_Name,
Override_Flag,
ConnectRn_Flag,enter code here
Event_Id,
Alias_Id,
Drug_Dose_Id
FROM CRX_DATA.dbo.AHI_CAB_EVENT
WHERE Event_Dttm >= @startdate
AND Event_Dttm < @enddate;
DECLARE @startdate datetime, @enddate datetime;
SET @startdate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0);
SET @enddate = DATEADD(ms, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0));
SELECT
CASE Station_Type
WHEN 'ACART'
THEN 'Anesthesia-Rx'
WHEN 'DOSE'
THEN 'AcuDose-Rx'
ELSE 'AcuDose-Rx'
END AS Station_Type,
Station_Id,
Station_Desc,
Unit_Desc,
Event_Dttm,
Event_Name,
CONVERT(varchar(120), REPLACE(Full_Site_Drug_Desc, '"', '')) Full_Site_Drug_Desc,
Begin_Inventory_Level,
Expected_Inventory_Level,
End_Inventory_Level,
Trans_Qty,
Cabinet_Num,
Drawer_Num,
Pocket_Num,
Displayed_Cabinet_Num,
Displayed_Drawer_Num,
Displayed_Pocket_Num,
Site_User_Id,
User_Name,
Pat_Id,
Site_Patient_Id,
Pat_Name,
Waste_Method,
Witness_Name,
Override_Flag,
ConnectRn_Flag,enter code here
Event_Id,
Alias_Id,
Drug_Dose_Id
FROM CRX_DATA.dbo.AHI_CAB_EVENT
WHERE Event_Dttm >= @startdate
AND Event_Dttm < @enddate;
November 6, 2020 at 9:06 pm
If you want to throw the query into a variable, you need to double up all of your quotes. So all ' characters become ''. All '' become ''''. and so on. Then you can store it in a variable and do EXEC (@tsql ).
Otherwise I don't see any reason (quick eyeball... that's a lot of text) why it wouldn't run.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 7, 2020 at 3:09 am
As a bit of a sidebar, your WHERE clauses for the start (>=) and end dates (<) are CORRECT. Your calculation for end date is not. You could miss some entries that occur at 23:59:59.997. You can also take advantage of "on the fly assignment" in the DECLARE. Like this...
DECLARE @startdate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) --Beginning of yesterday
,@enddate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) --Beginning of today
;
I also tweaked your start date just to get rid off one more mathematical operation.
Shifting gears to your request, since there's absolutely nothing in any of the queries that you posted that require dynamic SQL, why do you want to convert them to dynamic SQL? I ask because, depending on the reason, there may be a better way.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2020 at 5:04 am
This was removed by the editor as SPAM
November 27, 2020 at 1:25 am
As a bit of a sidebar, your WHERE clauses for the start (>=) and end dates (<) are CORRECT. Your calculation for end date is not. You could miss some entries that occur at 23:59:59.997. You can also take advantage of "on the fly assignment" in the DECLARE. Like this...
DECLARE @startdate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) --Beginning of yesterday
,@enddate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) --Beginning of today
;I also tweaked your start date just to get rid off one more mathematical operation.
Shifting gears to your request, since there's absolutely nothing in any of the queries that you posted that require dynamic SQL, why do you want to convert them to dynamic SQL? I ask because, depending on the reason, there may be a better way.
Convoluted method, don't do it. Stick to your original method of the standard-calc-for-today then minus 1 rather than using a starting day of 1. Better still, do the -1 outside the standard calc, so it's very clear:
@startdate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) --keep the standard calc the same, do the adjustment afterward
In this specific case, it would be clearer to me to just add 1 day to the startdate to get the enddate:
@enddate = DATEADD(DAY, 1, @startdate)
This lets anyone easily see that the code is being run for one day, without having to verify the calc for enddate is for today, although since it is the standard calc I admit it should be recognized easily enough.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2020 at 5:21 am
I'm clueless after trying everything and searching for others with this experience still getting the same errors again and again.
Please help me out with this error.
December 10, 2020 at 8:45 pm
I don't see anything in your query that needs to be done dynamically - can you show the actual code you are attempting to create as a dynamic query and what parts need to be dynamic and why?
From what you have posted - there isn't anything we can help with because we cannot see how you have put together the string and where it is failing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 11, 2020 at 2:53 am
Convoluted method, don't do it.
Only to those that think so.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2020 at 2:57 am
I'm clueless after trying everything and searching for others with this experience still getting the same errors again and again.
Please help me out with this error
Like I said earlier and others have said, there's nothing in your query that requires dynamic SQL. Why are you trying to use dynamic SQL here? If we knew that, we might be able to help better.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2020 at 4:11 pm
No. The human brain is designed to recognize patterns. Sticking with a familiar patterns drastically speeds up recognition of the code when someone looks at it later. Breaking that pattern to "save one mathematical operation" on a machine with 12+ processors each capable of performing trillions of mathematical operations per sec is just wrong.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 11, 2020 at 4:14 pm
No. The human brain is designed to recognize patterns. Sticking with a familiar patterns drastically speeds up recognition of the code when someone looks at it later. Breaking that pattern to "save one mathematical operation" on a machine with 12+ processors each capable of performing trillions of mathematical operations per sec is just wrong.
I AM sticking to a familiar pattern, Scott. Your brain just doesn't recognize it and that's OK with me.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2020 at 5:25 pm
When I was on the ANSI X3 H2 standards committee, we introduced the idea of single quotes for character strings in the SQL language. I personally think this was a bad idea. We really should have had a "BEGIN QUOTE" to be matched with "END QUOTE" from the ANSI ISO standard character sets. This would have led us to embedded quotations and save a lot of problems. But instead, we use the double quote mark to show that a string with a literal, think of the Microsoft convention of the square bracket.. At the time we felt that our use of the double quote mark would let us do really weird column names be really cool, blah blah blah. In fact, a few other language standards committees ask us about it and were considering it. Looking back on it after all these decades of ANSI/ISO standards, we were wrong. Decades ago, we really didn't understand quoted strings, lambda calculus and all those other things logicians were trying to tell us (see Quline's quasi-quotes and the problems thereof).
The best way to avoid all of the conceptual, practical and logical problems of embedding code within code within code within quote ad infinitum is to write really simple, direct simple programs. All of the complicated relational work should be done in the DDL.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 11, 2020 at 5:28 pm
ScottPletcher wrote:No. The human brain is designed to recognize patterns. Sticking with a familiar patterns drastically speeds up recognition of the code when someone looks at it later. Breaking that pattern to "save one mathematical operation" on a machine with 12+ processors each capable of performing trillions of mathematical operations per sec is just wrong.
I AM sticking to a familiar pattern, Scott. Your brain just doesn't recognize it and that's OK with me.
The standard pattern uses 0 as the base, not 1 or -1 or some other "cute" trick.
And using +1 to back up/subtract a day is convoluted, you just refuse to acknowledge that. Positive numbers aren't intuitively seen as reducing a value. If there were some real performance gain, it might be justified. But here the "gain" here is so miniscule it's unmeasurable.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 11, 2020 at 9:00 pm
The standard pattern uses 0 as the base, not 1 or -1 or some other "cute" trick.
No sir... There are a couple of "standards". The "standard" that YOU and many others follow is as you say and I have no objection to it. If you look at code from people like Peter Larsson and other heavy hitters, they use the same "cute" trick that I did as a "standard". And, guess what... neither of those will work for a "PERSISTED" computed column because there's different "standard" that is needed for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2020 at 10:59 pm
ScottPletcher wrote:The standard pattern uses 0 as the base, not 1 or -1 or some other "cute" trick.
No sir... There are a couple of "standards". The "standard" that YOU and many others follow is as you say and I have no objection to it. If you look at code from people like Peter Larsson and other heavy hitters, they use the same "cute" trick that I did as a "standard". And, guess what... neither of those will work for a "PERSISTED" computed column because there's different "standard" that is needed for that.
Naturally you can those tricks if you want to. Just don't then pretend that you have a "standard". A standard is based on consistency, after all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply