In the project I am currently working on, I have a series of procedures that first query the structure of queries into a variable of type Nvarchar and then at the end of the work using the Exec command sp_executesql the desired string is executed and at the end, the desired output is displayed.
The reason for using this method is the many variables and conditions that are usually examined in these procedures.
My problem with such procedures is when I want to make the slightest change to them. This is really difficult.
My question is that there is a way to write complex queries with many threads without converting them to a string that can easily examine the code in the SQL Server environment without having to convert it to a string. Thank you.
USE [SaleReportDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SpGetAllSalesState]
@InvMainRespID INT = NULL,
@InvRespStatus_ID INT = NULL,
@DateStartFrom DATE = NULL ,
@DateEndTo DATE = NULL ,
@FPYear SMALLINT = NULL,
@Adder_MainOpCode_Code NVARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(MAX),
@Paramlist NVARCHAR(MAX),
@AllColumn NVARCHAR(MAX)
SET @Sql = N'
BEGIN TRY
DROP TABLE #MainOpCodeTest
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #MainOpCodeTest(
[MainOpCodeId] [INT] NOT NULL,
[MainOpCodeName] [NVARCHAR](301) NULL,
[MainOpCodeType_Id] [INT] NOT NULL,
[MainOpCode_Code] [NVARCHAR](201) NOT NULL,
[AccountCode] [NVARCHAR](108) NULL,
[MobileNumber] [NVARCHAR](30) NULL,
[StatueName] [NVARCHAR](50) NULL,
CONSTRAINT [PK_MainOpCodeTestMain1001' CONVERT(nvarchar(10) , @@SPID) REPLACE(CONVERT(varchar(255), NEWID()) ,'-','')
SET @Sql = '] PRIMARY KEY CLUSTERED
(
[MainOpCode_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #MainOpCodeTest
SELECT * FROM glo.MainOpCode;
'
--SELECT * FROM #MainOpCodeTest
set @Sql = N';WITH Results AS ('
set @Sql = N' SELECT Rc.FiscalPeriod_Year,
Rc.ID,
Rc.Moment,
Rc.PurchaserNameName,
Rc.PurchaserNamePhone,
Rc.PurchaserNameMobile,
Rc.PurchaserName_State_Id,
Rc.PurchaserName_City_Id,
Rc.PurchaserNameNationalCode,
Rc.PurchaserNamePostal_Code,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Rc.PurchaserName_Address,N''$$1'', N'' بخش/منطقه '') ,N''$$2'',N'' خیابان '') , N''$$3'',N''خیابان '') ,N''$$4'',N'' خیابان '') , N''$$5'' ,N'' کوچه '') , N''$$6'',N'' - '') as PurchaserName_Address,
Rc.PurchaserNameRemarks,
Rc.InvRespKind_ID,
Rc.InvRespType_ID,
Rc.ServiceLocation_ID,
Rc.Goods_Code,
Rc.ProductSerialNumber,
Rc.CallCenterResponsibleRemarks,
Rc.CallCenterExpertRemarks,
Rc.TecnicalExpertRemarks,
Rc.PublicRemarks,
Rc.TagRemarks,
Rc.RequestRemarks,
Rc.PartsAlongRemarks,
Rc.ServiceLocationRemarks,
Rc.ReceptorUnit_ID,
Rc.DefectRemarks,
Rc.GoodsGroupItem_ID,
ISNULL(Rc.Register_MainOpCode_Code, ''0-0'') as RegisterMainOpCode_Code,
ISNULL(Rc.LastEditor_MainOpCode_Code, ''0-0'') as LastEditorMainOpCode_Code,
Rc.PurchaserNamePosition,
ISNULL(Rc.ShopMember_MainOpCode_Code, ''0--1'') as ShopMemberMainOpCode_Code,
Rc.MomentAssigment,
End1.InvResp_FiscalPeriod_Year,
End1.InvRespStatus_ID,
End1.Operator_MainOpCode_Code,
End1.Adder_MainOpCode_Code,
IsNull(End1.StateName , N''ثبت اولیه'') as [InvRespStatus.StatusName],
p.MainOpCodeId,
p.MainOpCodeName as [InvRespStatus.MainOpCodeName],
p.MainOpCodeType_Id,
p.MainOpCode_Code,
p.AccountCode,
p.MobileNumber as [InvRespStatus.MainOpCodeMobileNumber],
p.StatueName,
End1.Moment as SecondMoment
--,ROW_NUMBER() OVER(PARTITION BY Rc.FiscalPeriod_Year , Rc.ID ORDER BY convert(datetime, End1.Moment, 103) desc) AS RepeatedID
FROM [css].[InvResp] rc LEFT JOIN
(
SELECT t2.MainID2, t2.FiscalPeriod_Year, t2.ID, t2.InvResp_FiscalPeriod_Year, t2.InvResp_ID,
t2.InvRespStatus_ID, t2.Moment, t2.Operator_MainOpCode_Code, t2.Adder_MainOpCode_Code, t2.StateName, t2.TotalID
FROM (
SELECT t.MainID2,
t.FiscalPeriod_Year,
t.ID,
t.InvResp_FiscalPeriod_Year,
t.InvResp_ID,
t.InvRespStatus_ID,
t.Moment,
t.Operator_MainOpCode_Code,
t.Adder_MainOpCode_Code,
t.StateName ,
ROW_NUMBER() OVER(PARTITION BY t.FiscalPeriod_Year , t.ID ORDER BY t.Moment DESC) AS TotalID
FROM
(
SELECT MD1.MainID2,
MD1.FiscalPeriod_Year,
MD1.ID,
MD1.InvResp_FiscalPeriod_Year,
MD1.InvResp_ID,
MD1.InvRespStatus_ID,
MD1.Moment,
MD1.Operator_MainOpCode_Code,
MD1.Adder_MainOpCode_Code,
MD1.[Name] as StateName FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY r.FiscalPeriod_Year ,
r.ID ORDER BY convert(datetime, m.Moment, 103) desc) AS MainID2,
r.FiscalPeriod_Year , r.ID , m.* , t1.[Name]
FROM [css].[InvResp_InvRespStatus] m
INNER JOIN [css].[InvResp] r ON r.ID = m.InvResp_ID
INNER JOIN [css].[InvRespStatus] t1 ON m.InvRespStatus_ID = t1.ID
WHERE m.Adder_MainOpCode_Code IS NOT NULL '
IF NOT @FPYear IS NULL
SET @Sql = N' AND r.FiscalPeriod_Year = @FPYear '
--IF Not @InvMainRespID Is Null
--SET @Sql = N' AND r.ID = @InvMainRespID '
SET @Sql =N'
) MD1 WHERE MD1.MainID2 = 1
UNION
SELECT MainTbl.MainID2,
MainTbl.FiscalPeriod_Year,
MainTbl.ID,
MainTbl.InvResp_FiscalPeriod_Year,
MainTbl.InvResp_ID,
MainTbl.InvRespStatus_ID,
MainTbl.Moment,
MainTbl.Operator_MainOpCode_Code,
SubTblStates1.OPCode,
MainTbl.StatName FROM(
SELECT MD1.MainID2, MD1.FiscalPeriod_Year, MD1.ID,
MD1.InvResp_FiscalPeriod_Year,
MD1.InvResp_ID, MD1.InvRespStatus_ID,
MD1.Moment, MD1.Operator_MainOpCode_Code, MD1.Adder_MainOpCode_Code, MD1.StatName FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY r.FiscalPeriod_Year , r.ID ORDER BY convert(datetime, m.Moment, 103) desc) AS MainID2,
r.FiscalPeriod_Year , r.ID , m.InvResp_FiscalPeriod_Year,
m.InvResp_ID,
m.InvRespStatus_ID,
m.Moment,
m.Operator_MainOpCode_Code,
m.Adder_MainOpCode_Code , t1.[Name] AS StatName
FROM [css].[InvResp_InvRespStatus] m
INNER JOIN [css].[InvResp] r ON r.ID = m.InvResp_ID
INNER JOIN [css].[InvRespStatus] t1 ON m.InvRespStatus_ID = t1.ID
WHERE Adder_MainOpCode_Code IS NULL '
IF NOT @FPYear IS NULL
SET @Sql = N' AND r.FiscalPeriod_Year = @FPYear '
--IF Not @InvMainRespID Is Null
--SET @Sql = N' AND r.ID = @InvMainRespID '
SET @Sql = N'
) MD1 WHERE MD1.MainID2 = 1 ) AS MainTbl LEFT JOIN
(SELECT MT2.MainRow3,
MT2.MYEAR,
MT2.MID,
MT2.OPCode
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY MT1.InvResp_FiscalPeriod_Year , MT1.InvResp_ID
ORDER BY convert(datetime, MT1.Moment, 103) DESC) AS MainRow3
, Mt1.InvResp_FiscalPeriod_Year AS MYEAR , MT1.InvResp_ID AS MID ,MT1.Adder_MainOpCode_Code AS OPCode FROM(
SELECT n.InvResp_FiscalPeriod_Year, n.InvResp_ID, n.Moment, n.Adder_MainOpCode_Code
FROM [css].[InvResp_InvRespStatus] n WHERE n.Adder_MainOpCode_Code IS NOT NULL '
IF NOT @FPYear IS NULL
SET @Sql = N' AND n.InvResp_FiscalPeriod_Year = @FPYear '
SET @Sql = N'
) MT1 )MT2 WHERE MT2.MainRow3 = 1) AS SubTblStates1 ON MainTbl.ID = SubTblStates1.MID
AND MainTbl.FiscalPeriod_Year = SubTblStates1.MYEAR
)t)t2 WHERE t2.TotalID = 1
)End1
ON End1.ID = Rc.ID AND End1.FiscalPeriod_Year = Rc.FiscalPeriod_Year
left JOIN [glo].[MainOpCode] p ON p.MainOpCode_Code = End1.Adder_MainOpCode_Code
WHERE 1 = 1 ' --and Rc.ID = 153650 RepeatedID
IF Not @Adder_MainOpCode_Code Is Null
SET @Sql = N' AND End1.Adder_MainOpCode_Code = @Adder_MainOpCode_Code '
IF Not @DateStartFrom Is NULL AND NOT @DateEndTo IS NULL
SET @Sql = N' AND CONVERT(DATE , Rc.Moment) >= @DateStartFrom AND CONVERT(DATE , Rc.Moment) <= @DateEndTo '
IF NOT @FPYear IS NULL
SET @Sql = N' AND Rc.FiscalPeriod_Year = @FPYear '
SET @Sql = N' )
SELECT isnull(Results.InvRespStatus_ID , 1) as InvRespStatus_ID
FROM Results
INNER JOIN css.InvRespKind ON Results.InvRespKind_ID = css.InvRespKind.ID
INNER JOIN tec.GoodsGroupItem AS gi ON Results.GoodsGroupItem_ID = gi.ID
INNER JOIN glo.City AS Cy ON Results.PurchaserName_City_Id = Cy.Id
INNER JOIN glo.State Sta ON Results.PurchaserName_State_Id = Sta.Id
INNER Join #MainOpCodeTest on #MainOpCodeTest.MainOpCode_Code = Results.ShopMemberMainOpCode_Code
INNER Join #MainOpCodeTest MainOpCode1 on MainOpCode1.MainOpCode_Code = Results.RegisterMainOpCode_Code
INNER Join #MainOpCodeTest MainOpCode2 on MainOpCode2.MainOpCode_Code = Results.LastEditorMainOpCode_Code
LEFT OUTER JOIN tec.Goods AS gd ON Results.Goods_Code = gd.GoodsCode
LEFT OUTER JOIN css.Agent ag ON Results.MainOpCodeId = ag.Code where 1 = 1 '
IF NOT @InvRespStatus_ID IS NULL
SET @Sql = N' AND InvRespStatus_ID = @InvRespStatus_ID '
IF Not @InvMainRespID Is Null
SET @Sql = N' AND Results.ID = @InvMainRespID '
SET @Sql = N' order by Results.ID desc ' --order by ID desc where RepeatedID = 1 , ROW_NUMBER() OVER(PARTITION BY FiscalPeriod_Year , ID ORDER BY convert(datetime, SecondMoment, 103) desc) AS RepeatedID
Set @Paramlist = '@InvMainRespID INT = NULL
,@InvRespStatus_ID INT = NULL
,@DateStartFrom DATE = NULL
,@DateEndTo DATE = NULL
,@FPYear SMALLINT = NULL
,@Adder_MainOpCode_Code NVARCHAR(100) = NULL'
Exec sp_executesql @Sql ,@Paramlist
,@InvMainRespID
,@InvRespStatus_ID
,@DateStartFrom
,@DateEndTo
,@FPYear
,@Adder_MainOpCode_Code
END
CodePudding user response：
Try the following query. It is very simple and effective and speedy way to create very large SQL queries with a lot of variables.
YourSelectCommasnd where (MUserCode = @UserCode OR @UserCode = -1)
AND (MMessageStatus IS NULL OR MMessageStatus = @MMessageStatus )
AND (MSaveDate >= @DateFrom OR @DateFrom = '')
AND (MSaveDate <= @DateTo OR @DateTo = '')
AND (MTimeSpan <= @MTimeSpan OR @MTimeSpan = '')