New answers tagged

1

Because you have four tables establishing the hierarchy you can simply join all of the elements directly: SELECT level1.key1 ,level1.key2 ,level2.key3 ,level3.key4 FROM table1 level1 LEFT JOIN table2 level2 ON level2.key2 = level1.key2 LEFT JOIN table3 level3 ON level3.key3 = level2.key3


-1

An alternative is to calculate standard deviation using an equation instead of applying the built-in STDEV function to a padded result set. I've re-arranged the classic version of the equation as shown in the image below so that only one query is needed. (The classic version would require multiple sub-queries.) Assume @N = 12 (total number of months): ...


0

What does it take to "create a purchase order"? If it is something outside MySQL, then the process flow is impossible. Instead, work on gathering the data needed for the purchase order, return that data to your application, which will then "create the purchase order(s)". Try not to use "cursors". They are inefficient, clumsy, ...


0

That's not the correct syntax for using a cursor. But why even use a cursor when JOIN will do the job much faster. Or a GROUP_CONCAT. BEGIN SELECT CM.career_id AS id, CM.name AS career, AC.name AS area, GROUP_CONCAT(sd.name FROM specialization_detail WHERE sd.career_id = row.id) FROM ...


0

As others have said, there is no good way to detect if a parameter was set to its default value by the caller or by its omission. To add an extra option to the list of workarounds, you could make the default instead something that will never get used and react differently to that. For instance: CREATE PROCEDURE DefaultJiggeryPokery @InputParam INT = -...


5

Maybe something like this might work:- CREATE PROC dbo.Test @Param1 INT = NULL AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT 1 FROM sys.dm_exec_requests AS ER CROSS APPLY sys.dm_exec_input_buffer(ER.session_id, ER.request_id) AS IB WHERE ER.session_id = @@SPID AND IB.event_info LIKE '%@Param1%' ...


-1

I would go for a universal placeholder in this case Exec Test @param1 then let the code snippet that calls this stored procedure to determine whether the @param1 value is set to NULL or left as an empty string given the scenario at hand.


3

As a workaround approach: if you can modify the caller code to supply a document instead, you can both detect if a key/column exists in the supplied document to omit updates where a key is not present and allow for set-null commands to valid keys Consider the following (slightly bulky) POC (full db<>fiddle) create proc Test @payload nvarchar(255)...


2

ANSWER: There is no way of telling if parameter in called procedure was set to it's default value by user or by system. The value inside procedure looks exactly same and there is no information stored in the system if the value was passed by calling script or default value was assigned by SQL server. WORKAROUND: You can use second optional parameter like @...


4

In T-SQL, nested if statements can be achieved using the following pattern: IF (<condition(s)>) BEGIN <Statement(s)> END ELSE IF (<condition(s)>) BEGIN <Statement(s)> END ELSE BEGIN <Statements(s)> END Official documentation: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/else-if-else-...


0

This is the default feature of profiler unless you enable below by going into options of profiler capturing: RPC:Completed SP:StmtCompleted SQL:BatchCompleted SP:stmtstarting SQL:stmtstarting SQL:stmtcompleted However please note that if there is nested call of procedure inside another procedure, it may not get captured. Please go through the below ...


2

SQL Server doesn't generate results in Excel's native XLS / XLSX formats, rather it can generate them as a basic delimited text file, for example as a CSV (or include the results inline in the email). You don't have much control over the format, but check out the docs for sp_send_dbmail for details. e.g. look at @query_result_width and @...


Top 50 recent answers are included