Skip to Content
0
Jun 11, 2014 at 04:05 AM

Blocking query in SSMS(SQL Server Management Studio)

32 Views

I am a Fresher in writing queries.

I wrote following a query to block the A/P invoice, if price added in A/P invoice and the Price in Price list is different.

and this invoice should not be able to add by users other than UserSign = 5,6.

IF (@transaction_type='A' OR @transaction_type = 'U') AND @OBJECT_TYPE='18'

BEGIN

If EXISTS(SELECT T1.ItemCode,

T1.Price AS Inv_Price,

T2.U_ListPrice AS Listed_Price

FROM OPCH AS T0

INNER JOIN

PCH1 AS T1 ON

T0.DocEntry = T1.DocEntry

LEFT OUTER JOIN

dbo.[@PRICELISTS] AS T2 ON

T0.CardCode = T2.U_BPCode AND

T1.ItemCode = T2.U_ItemNo

WHERE

(T0.UserSign <> 6) AND

(T0.UserSign <> 5) AND

(T1.DocEntry = @list_of_cols_val_tab_del) AND

(T1.Price <> T2.U_ListPrice)

(T2.U_ListCurrency = T0.DocCur)

BEGIN

SET @error = 123 --1234567991234567918

SET @error_message = 'Deviation in price'

END

After executing, still everyone can add the A/P invoice with price difference.

Please look at the above code and help me to find out my fault to make it correct.

Is there any need to add some condition in where clause?

Regards,

Hitul Varia