Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 05:05:38
Dear Experts,
I'm trying this procedure, but unable to create it.

alter PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON
DECLARE @A VARCHAR(8000)
set @A = '(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)'
print @A

/*IF( @TYPE = 'C' )
BEGIN
set @A = '(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)'
print @A
END

ELSE IF( @TYPE = 'V' )
BEGIN
set @A = '(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''))))'
print @A
END

ELSE IF( @TYPE = 'E' )
BEGIN
set @A = '(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)'
print @A
END*/

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM +'(' + @A + ')'+ TAB order by chk

SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
SET NOCOUNT OFF

END

where is the problem?

thank you verymuch for the time for me

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-17 : 05:17:41
Let me stress this:

AVOID DYNAMIC SQL AS MUCH AS POSSIBLE.

In your case, I don't see any reason to use D-sql and it is the D-Sql that is unnecessarily complicating things.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 05:22:01
Dear Harsh,
can you please tell me way how can i do that with out using dynamic sql

CREATE PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON
DECLARE @A VARCHAR(8000)
set @A = '(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)'
print @A

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM +'(' + @A + ')'+ TAB order by chk

SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
SET NOCOUNT OFF

END

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-17 : 05:25:53
What's so difficult in that?

CREATE PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5)) 
AS 
BEGIN 
SET NOCOUNT ON 
-- DECLARE @A VARCHAR(8000)
-- set @A = '(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)'
-- print @A

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T 
FROM
	(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 <> 1)TAB 
order by chk 

SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND 
DROP TABLE #T 
SET NOCOUNT OFF 

END



Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 05:29:08
Exactly I've tried this one

CREATE PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1) TAB-- order by chk

SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
SET NOCOUNT OFF

END

am i correct harsh?

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-17 : 05:32:18
Are you getting any error?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 05:54:31
Yes Harsh,
I'm getting the problem with column CHK
actually I'm now posting the complete procedure without the comment

alter PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON
DECLARE @A VARCHAR(8000)
set @A = '(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)'
print @A

IF( @TYPE = 'C' )
BEGIN
set @A = '(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)'
print @A
END

ELSE IF( @TYPE = 'V' )
BEGIN
set @A = '(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''))))'
print @A
END

ELSE IF( @TYPE = 'E' )
BEGIN
set @A = '(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)'
print @A
END

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM +'(' + @A + ')'+ TAB order by chk

SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
SET NOCOUNT OFF

END

now please tell me can i do this without using dynamic SQL.....
please guide me

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-17 : 06:00:46
As long as table name or column name is not dynamic, you can do it without dynamic sql. What is the error you are getting?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 06:07:02
the error is invalid column name chk

--drop procedure [dbo].[CONTROLACC_PAGINACTION]
CREATE PROCEDURE [dbo].[CONTROLACC_PAGINACTION] (@ROWNUMBEGIN INT ,@ROWNUMEND INT,@BRANCHID VARCHAR(100),@CNTACCID VARCHAR(100),@TYPE CHAR(5))
AS
BEGIN
SET NOCOUNT ON
DECLARE @A VARCHAR(8000)
--set @A = '(SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 != 1)'
--print @A

IF( @TYPE = 'C' )
BEGIN
set @A = '(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)'
print @A
END

ELSE IF( @TYPE = 'V' )
BEGIN
set @A = '(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''))))'
print @A
END

ELSE IF( @TYPE = 'E' )
BEGIN
set @A = '(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)'
print @A
END

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 <> 1) TAB order by chk


SELECT * FROM #T WHERE ROWNUM BETWEEN @ROWNUMBEGIN AND @ROWNUMEND
DROP TABLE #T
SET NOCOUNT OFF

END


Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

shallu1_gupta
Constraint Violating Yak Guru

394 Posts
Posted - 2007-05-17 : 06:12:20
Hi,
this is bcs u dont have a column called [chk] over here there is just one column COLUMN001 that is selected from subquery

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM (SELECT COLUMN001 FROM OATABLE030 WHERE COLUMN010 = @TYPE AND COLUMN007 <> 1) TAB order by chk
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 06:22:09
Dear gupta,
the chk column is here after false

set @A = '(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)'

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-17 : 06:43:47
What good is that variable if it is not used in your final query?

Change your query to the following:

SELECT *,ROWNUM = IDENTITY (INT, 1, 1) INTO #T FROM 
(
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
)
TAB order by chk



Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-17 : 07:02:18
This is Great Harsh, the procedure is created
thank you for the valuble time given for me

thank you Gupta for the valuble time

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-05-17 : 08:18:33
As advised, dont use Dynamic sql in this case

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2007-05-18 : 01:47:24
Dear Madhi,Harsh,Gupta
thank you for the way you helped me to create the procedure, but still I'm not getting the desired result.I've posted a new thread with more details. please help me to execute the Sp with the desired result

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site