如何纠正是查询 [英] how to correct is query

问题描述

ALTER procedure [dbo].[ERP_ShowStockRegi]
(
   @input varchar(100)
)
as
begin

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand ='select DISTINCT * from 
(SELECT dbo.StockLedger.SledId, dbo.StockLedger.SledCode, dbo.StockLedger.StockDetId, dbo.StockLedger.SLedType, dbo.StockLedger.SLedQty, 
dbo.StockLedger.SLedInOut, dbo.StockLedger.SLedPrice, dbo.StockLedger.SLedDate, dbo.StockLedger.SLedRefNo, dbo.StockLedger.BranchId, 
dbo.StockLedger.CustId, dbo.StockMaster.StockId, dbo.StockMaster.StockParent, dbo.StockMaster.StockName, dbo.StockMaster.StockAlias, 
dbo.StockMaster.StockUnit, dbo.StockMaster.StockType, dbo.StockMaster.Branch, dbo.StockMaster.StockComp, 
dbo.StockMaster.StockName + ' (' + CASE WHEN dbo.StockDetails.PartNo IS NULL THEN '' ELSE dbo.StockDetails.PartNo END + ')' AS ItemPart, 
CASE WHEN sledinout = '1' AND sledtype <> 'OPN' THEN sledqty ELSE 0 END AS QtyIn, CASE WHEN sledinout = '0' AND 
sledtype <> 'OPN' THEN sledqty ELSE 0 END AS QtyOut, dbo.StockDetails.PartNo, 0 AS [OPEN], 0 AS PositiveOpeningQtyIn, 0 AS positiveClosingQty, 
0 AS [CLOSE], dbo.AcctMaster.AcctAlias AS PartyName, dbo.StockMaster.Active
FROM         dbo.StockLedger INNER JOIN
dbo.StockMaster ON dbo.StockLedger.StockDetId = dbo.StockMaster.StockId INNER JOIN
dbo.StockDetails ON dbo.StockMaster.StockId = dbo.StockDetails.StockId LEFT OUTER JOIN
dbo.AcctMaster ON dbo.StockLedger.CustId = dbo.AcctMaster.AcctId
WHERE (dbo.StockMaster.Active = N'1')) as tab1
'+ @input
 execute(@sqlCommand)
end

推荐答案

我做了很少的纠正查询中的ons：



1）请确保在进行动态查询时，如果要使用单引号，则将其放置2次（例如， '将'和'''将''''）



2）如果你想插入括号，那么它将成为字符串的一部分。所以不需要使用+符号将其分开。



请尝试以下查询：

I did few corrections in the query:

1) Please make sure when you are working on dynamic query then if you want to use single quote then put it 2 times (ex. ' will be '' and '' will be '''')

2) If you want to insert bracket then it will be part of the string. So no need to make it separate by using + symbol.

Please try with the below query:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand ='select DISTINCT * from 
(SELECT dbo.StockLedger.SledId, dbo.StockLedger.SledCode, dbo.StockLedger.StockDetId, dbo.StockLedger.SLedType, dbo.StockLedger.SLedQty, 
dbo.StockLedger.SLedInOut, dbo.StockLedger.SLedPrice, dbo.StockLedger.SLedDate, dbo.StockLedger.SLedRefNo, dbo.StockLedger.BranchId, 
dbo.StockLedger.CustId, dbo.StockMaster.StockId, dbo.StockMaster.StockParent, dbo.StockMaster.StockName, dbo.StockMaster.StockAlias, 
dbo.StockMaster.StockUnit, dbo.StockMaster.StockType, dbo.StockMaster.Branch, dbo.StockMaster.StockComp, 
dbo.StockMaster.StockName (CASE WHEN dbo.StockDetails.PartNo IS NULL THEN '''' ELSE dbo.StockDetails.PartNo END ) AS ItemPart, 
CASE WHEN sledinout = ''1'' AND sledtype <> ''OPN'' THEN sledqty ELSE 0 END AS QtyIn, CASE WHEN sledinout = ''0'' AND 
sledtype <> ''OPN'' THEN sledqty ELSE 0 END AS QtyOut, dbo.StockDetails.PartNo, 0 AS [OPEN], 0 AS PositiveOpeningQtyIn, 0 AS positiveClosingQty, 
0 AS [CLOSE], dbo.AcctMaster.AcctAlias AS PartyName, dbo.StockMaster.Active
FROM         dbo.StockLedger INNER JOIN
dbo.StockMaster ON dbo.StockLedger.StockDetId = dbo.StockMaster.StockId INNER JOIN
dbo.StockDetails ON dbo.StockMaster.StockId = dbo.StockDetails.StockId LEFT OUTER JOIN
dbo.AcctMaster ON dbo.StockLedger.CustId = dbo.AcctMaster.AcctId
WHERE (dbo.StockMaster.Active = N''1'')) as tab1
'+ @input
 execute(@sqlCommand)

如果您有任何疑虑或疑问，请告知我们。仍然面临问题。



谢谢

Advay Pandya

Please let me know if you have any concern or query or if you are still facing issue.

Thanks
Advay Pandya

这篇关于如何纠正是查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！