Stored Procedures Tips and tricks

Here I am posting a Stored procedure created using few of the effective techniques that I have been using frequently over the past few days. Here are the ingredients of the SP given below,
  1. At certain point in time we ought to test the application for sanity over the Production machine and don't want the data to be visible later on but for very obvious reasons we can't remove that data since there are a lot of dependencies (Foreign References ) over it. We can hide it using the techniques demonstrated in the SP below in Red Color.
  2. Using the "Cases" in the SQL that can let one output understandable statements based on the given Auto or ID.
  3. It also demonstrated the fact that we try to do most of the data processing work in the SPs rather than totally relying on the code. Generally, most of us use the SP to get our datasets filled with the raw unformatted and unprocessed data whose kinks are ironed out in the code in the later stages. Trivial issues like date formatting, Text/string concatenation and modifications, passing any value in case the required value is null, aliasing . Since after building the exes and the Dlls, we are in complete limbo if all of the sudden a bug appears.

CREATE PROCEDURE [dbo].[GetAllPendingGradings]
@STATUSAUTO INT = NULL
,@sellerAuto INT
AS
SET NOCOUNT ON
BEGIN

DECLARE @IncludeTestData BIT
SET @IncludeTestData = (SELECT TOP (1) IncludeTestData FROM GFCompany)

IF StatusAuto is Not Null

BEGIN

SELECT
DISTINCT (OFFER.offerauto) as offerauto,
ISNULL(FEDEXREFNO,'') AS TRACKNO
,OFFERID
,FIRSTNAME+' ' +LASTNAME AS SELLER
,SELLERID,
OFFER.SHIPMODEAUTO,

CASE OFFER.SHIPMODEAUTO
WHEN 1 THEN 'FAST'
WHEN 2 THEN 'FASTER'
WHEN 3 THEN 'FASTEST'
WHEN 4 THEN 'SHIP MYSELF'
WHEN 5 THEN 'FEDEX 2DAY'
END AS SHIPMODE

,convert(varchar, Offer.[InsDate] ,110)+' '+ Offer.[InsTime]OfferDateTime
,REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(dbo.SellerInfo.DayPhone,'N/A'),'(',''),')',''),'-',''),' ', '') AS ContactNumber,


FROM OFFER
INNER JOIN SELLERINFO ON OFFER.SELLERAUTO =SELLERINFO.SELLERAUTO
INNER JOIN OfferStatusTrack OST ON OST.OFFERAUTO=OFFER.OFFERAUTO
WHERE OST.STATUSAUTO = @STATUSAUTO
AND
OST.trackauto = (SELECT MAX(OST1.trackauto) from offerstatustrack OST1 WHERE OST1.OFFERAUTO=OFFER.OFFERAUTO AND OST1.STATUSAUTO <> 14)
AND
(@IncludeTestData = 1 OR (@IncludeTestData = 0 AND SELLERINFO.Email NOT LIKE '%@e-Bizsoft.com%'))

END
ELSE
BEGIN
DECLARE @tempStatus TABLE(OFFERAUTO NUMERIC(22), STATUSAUTO NUMERIC(22))
INSERT INTO @tempStatus SELECT OFFERAUTO, STATUSAUTO FROM OFFERSTATUSTRACK
INNER JOIN (SELECT MAX(TRACKAUTO) TRACKAUTO FROM OFFERSTATUSTRACK WHERE OFFERAUTO IN (SELECT OFFERAUTO FROM OFFER WHERE SELLERAUTO=@sellerAuto) GROUP BY OFFERAUTO) A
ON A.TRACKAUTO = OFFERSTATUSTRACK.TRACKAUTO
END
END

Comments

Post a Comment

Popular posts from this blog

SPFx: Develop using SharePoint Framework without Installing all the dependecies.

Image
SharePoint Framework (SPFx) has been around for more than 5 years now with being Generally available since Feb 2017.  In the recent times, its been possible to develop webparts for SharePoint on-prem as well using SPFx and with that it is getting harder to maintain multiple versions of required toolchains. e.g. Node. For M365 you can use the latest version of SPFx but probably you know, if you aim to develop webparts for the SharePoint Server 2016 platform, for instance, you need to use the SharePoint Framework v1.1.0 due to the server-side version dependencies. So yet another challenge is using the correct version of the SPFx and Nodejs for the particular project . Although I personally used to like Node Version Manager which lets you manage multiple installations of node.js on a Windows computer but still one needs to isolate SharePoint Framework dependencies if you are targeting multiple platform from on PC. 2nd Problematic scenario begins when you start developing SPFx extensi...
Read more

SharePoint Online: Elevated Permissions....with love

Image
Remember the good old days! when using RunWithElevatedPrivileges(SPSecurity.CodeToRunElevated), in the SharePoint server-side object model, would let us run our code (Full trust code) with full control i.e. granting us higher permission (as System Account) and letting us performs tasks that waren't permited to do in current user context.  Now, that, we have SharePoint addin and SharePoint online webparts, one still wonders if there is still a way of achieving that. Since there are use cases where a user needs a higher permission, for instance.  Changing a status of a field in a list where the current logged in user has read only permission.  Reading member of a SharePoint group.  In Workflows  like SharePoint Designer workflow you used to have App step, in Nintex workflow you have elevated permission and in MS Flow Impersonation is possible using connectors in Flow triggered by HTTP request can run under System account as show in the video below:  Other wa...
Read more

Powershell: Filling up an Existing Excel Sheet with data from SQL Server

Situation: The client required to have data from their SQL Server 2012 Database filled in to an existing Excel Sheet. This sheet had a table with around 40 columns of which 12 where to be filled by us and rest from different departments. I tried different solution for that since the requirements were not initially clear. Forexample OPENROWSET was tried. In this regards this link really helpful. Although this requires few configuration to be made on the server or to install e.g. ACE.OLEDB driver on server which was not possible in our case. So we decided to write a Powershell script and access SQL Now about the script: Take the file TestExcel.xlsx from C:\Temp folder Create its copy with the current date appended to its name.  Start reading the Excel file from Sheet "Specified Sheet Name" From Columns 1, it will take the name of the system.  Based on the name fill the excel sheet by executing the queries, here are the columns that are going to ...
Read more