SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T 
FROM 
	(SELECT DISTINCT 
		CUSTOMER_ID ID, BRANCH_ID BRANCH_ID, Customer_Code Customer_Code, Customer_Name Customer_Name, 
		Alias_Name Alias_Name, Customer_Type_ID Customer_Type_ID, GROUP_ID Customer_Grp_ID, Company_Name Company_Name, 
		Contact_Person Contact_Person, CPer_Desig CPer_Desig, Partial_Ok Partial_Ok, Cust_Service_Rep Cust_Service_Rep, Refered_by Refered_by, 
		Incoterms_Payment Incoterms_Payment, Payment_Mode Payment_Mode, Sales_type Sales_type, Head_Office Head_Office, 
		CUSTOMER_STATUS status, 'true' [chk] 
	FROM VCUSTOMER, OATABLE030 
	WHERE CUSTOMER_ID = OATABLE030.COLUMN001 AND OATABLE030.COLUMN002 = @CNTACCID AND BRANCH_ID = @BRANCHID 
	UNION 
	SELECT DISTINCT 
		CUSTOMER_ID ID, BRANCH_ID BRANCH_ID, Customer_Code Customer_Code, Customer_Name Customer_Name, 
		Alias_Name Alias_Name, Customer_Type_ID Customer_Type_ID, GROUP_ID Customer_Grp_ID, Company_Name Company_Name, 
		Contact_Person Contact_Person, CPer_Desig CPer_Desig, Partial_Ok Partial_Ok, Cust_Service_Rep Cust_Service_Rep, Refered_by Refered_by, 
		Incoterms_Payment Incoterms_Payment, Payment_Mode Payment_Mode, Sales_type Sales_type, Head_Office Head_Office, 
		customer_status status , 'false' [chk] 
	FROM VCUSTOMER 
	WHERE CUSTOMER_ID NOT IN 
		(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1) 
		AND BRANCH_ID = @BRANCHID AND VCUSTOMER.CUSTOMER_STATUS = 1) 
	order by chk 
	) tab

quote:

---

Originally posted by sunsanvin

Dear Experts, Here is the stored procedure, while executing this, I'm getting the query instead of the result.if I've provided @type='c', or @type='v', or @type='e', the respective select statement I'm getting instead of the result of the query. please help me in this regard.



CREATE PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #DD (D01 VARCHAR(8000))
IF( @TYPE = 'C' )
BEGIN
INSERT INTO #DD VALUES('(SELECT DISTINCT CUSTOMER_ID ID, BRANCH_ID BRANCH_ID, Customer_Code Customer_Code, Customer_Name Customer_Name, Alias_Name Alias_Name, Customer_Type_ID Customer_Type_ID, GROUP_ID Customer_Grp_ID, Company_Name Company_Name, Contact_Person Contact_Person, CPer_Desig CPer_Desig, Partial_Ok Partial_Ok, Cust_Service_Rep Cust_Service_Rep, Refered_by Refered_by, Incoterms_Payment Incoterms_Payment, Payment_Mode Payment_Mode, Sales_type Sales_type, Head_Office Head_Office, CUSTOMER_STATUS status,''true'' [chk] FROM VCUSTOMER, OATABLE030 WHERE CUSTOMER_ID = OATABLE030.COLUMN001 AND OATABLE030.COLUMN002 = @CNTACCID AND BRANCH_ID = @BRANCHID UNION SELECT DISTINCT CUSTOMER_ID ID, BRANCH_ID BRANCH_ID, Customer_Code Customer_Code, Customer_Name Customer_Name, Alias_Name Alias_Name, Customer_Type_ID Customer_Type_ID, GROUP_ID Customer_Grp_ID, Company_Name Company_Name, Contact_Person Contact_Person, CPer_Desig CPer_Desig, Partial_Ok Partial_Ok, Cust_Service_Rep Cust_Service_Rep, Refered_by Refered_by, Incoterms_Payment Incoterms_Payment, Payment_Mode Payment_Mode, Sales_type Sales_type, Head_Office Head_Office, customer_status status ,''false'' [chk] FROM VCUSTOMER WHERE CUSTOMER_ID NOT IN (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1) AND BRANCH_ID = @BRANCHID AND VCUSTOMER.CUSTOMER_STATUS = 1) order by chk ')
END
ELSE IF( @TYPE = 'V' )
BEGIN
INSERT INTO #DD VALUES('(SELECT DISTINCT VENDOR_ID ID, BRANCH_ID BRANCH_ID, VENDOR_CODE Customer_Code, VENDOR_NAME Customer_Name, Alias_Name Alias_Name, CONTACT_PERSON CONTACT_PERSON, CONTACT_TIMINGS CONTACT_TIMINGS, CREDIT_DAYS CREDIT_DAYS, CREDIT_LIMIT CREDIT_LIMIT, WEBSITE WEBSITE, EMAIL_ID EMAIL_ID, BANK_DETAILS BANK_DETAILS, REMARKS REMARKS, VENDOR_CAT VENDOR_CAT, VEN_GRP_ID VEN_GRP_ID, ONE_TIME ONE_TIME, VAT_No VAT_No, BLOCK BLOCK, PREFERD_VENDOR PREFERD_VENDOR, ''true'' [chk] FROM VVENDOR , OATABLE030 WHERE VENDOR_ID = OATABLE030.COLUMN001 AND OATABLE030.COLUMN002 = @CNTACCID AND BRANCH_ID = @BRANCHID UNION SELECT DISTINCT VENDOR_ID ID, BRANCH_ID BRANCH_ID, VENDOR_CODE Customer_Code, VENDOR_NAME Customer_Name, Alias_Name Alias_Name, CONTACT_PERSON CONTACT_PERSON, CONTACT_TIMINGS CONTACT_TIMINGS, CREDIT_DAYS CREDIT_DAYS, CREDIT_LIMIT CREDIT_LIMIT, WEBSITE WEBSITE, EMAIL_ID EMAIL_ID, BANK_DETAILS BANK_DETAILS, REMARKS REMARKS, VENDOR_CAT VENDOR_CAT, VEN_GRP_ID VEN_GRP_ID, ONE_TIME ONE_TIME, VAT_No VAT_No, BLOCK BLOCK, PREFERD_VENDOR PREFERD_VENDOR, ''false'' [chk] FROM VVENDOR WHERE VENDOR_ID NOT IN (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1 ) and VVENDOR.BLOCK = ''Y'' AND (BRANCH_ID = @BRANCHID OR BRANCH_ID in (select Sub_Org_Id from ftblHasNoModuleBranch (@BRANCHID, ''FIN'')))) order by chk ')
END
ELSE IF( @TYPE = 'E' )
BEGIN
INSERT INTO #DD VALUES('(SELECT DISTINCT KA2TABLE010.COLUMN001 ID, KA2TABLE010.COLUMND01 BRANCH_ID, KA2TABLE010.COLUMN002 Customer_Code, KA2TABLE010.COLUMN004 Customer_Name, KA2TABLE010.COLUMN005 Alias_Name,''true'' [chk] FROM KA2TABLE010,OATABLE030 WHERE KA2TABLE010.COLUMN001 = OATABLE030.COLUMN001 AND OATABLE030.COLUMN002 = @CNTACCID AND KA2TABLE010.COLUMN017 = @BRANCHID UNION SELECT DISTINCT EMP_ID AS ID,BRANCH_ID AS BRANCH_ID, EMP_CODE AS Customer_Code, EMP_NAME AS Customer_Name,'' Alias_Name , ''false'' [chk] from VEMPLOYEE WHERE (EMP_ID NOT IN (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)) AND BRANCH_ID = @BRANCHID) order by chk ')
END

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM (select D01 from #DD ) TAB
SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
DROP TABLE #DD
SET NOCOUNT OFF
END

---

quote:

---

Originally posted by harsh_athalye

Can't you see I commented out the extra parenthesis and highlighted this change in RED??

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

quote:

---

Originally posted by harsh_athalye

And I think the error cause and its resolution is pretty clearly defined in the error description itself.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---