Line No 8 Incorrect syntax near ')'. 1

[sqldevrequiretektips]

CREATE PROCEDURE terrredesign.RetriveDownloadData
@strWhere varchar(8000) -- This is the input parameter.
AS

Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A
Inner Join (select distinct (cust_life_num) from CUSTTB where " & @strWhere & " )B
On A.CustLifeNo = B.Cust_Life_num

Go

Anyone can please help me out why am i getting an error while i am trying to create this procedure..

@strWhere It's string...
Value will be
@StrWhere = Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)

 

[cLFlaVA]

you need a leading parentheses.

[tt]@StrWhere = [red]([/red]Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 1) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 2) OR (Region_Num = 50 AND Team_Num = 128 AND Territory_Num = 6)[/tt]

*cLFlaVA
----------------------------
[tt]tastes great, less filling.[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[sqldevrequiretektips]

Thanks for your reply but
rigth now i am just trying to click on Parse query button with above code..


and i am getting an error Near by B

Am I doing something wrong??

CREATE PROCEDURE terrredesign.RetriveDownloadData
@strWhere varchar(8000) -- This is the input parameter.
AS

Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A
Inner Join (select distinct (cust_life_num) from CUSTTB where " & @strWhere & " )B
On A.CustLifeNo = B.Cust_Life_num

Go

 

[Chopstik]

Also, you could probably more easily write your @strWhere like this:

@StrWhere = (Region_Num = 50 AND Team_Num = 128 AND Territory_Num IN (1,2,6))

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[JamesLean]

Your method of concatenating the WHERE clause is the problem here - you cannot just use the & operator like that.

You have two choices:

1) If you really want to pass a WHERE string like that you need to build the SQL dynamically and execute it:

CREATE PROC test_proc
  @where varchar(1000)
AS

DECLARE @sql varchar(8000)

SET @sql = 'SELECT c1, c2, c3 FROM t1 WHERE ' + @where
EXEC(@sql)
GO

2) A far better way is to have optional parameters for each column you want to filter on and build the query like this:

CREATE PROC test_proc
  @c1 varchar(10) = NULL,
  @c2 int = NULL
AS

SELECT c1, c2, c3
FROM t1
WHERE (@c1 IS NULL OR c1 = @c1)
  AND (@c2 IS NULL OR c2 = @c2)
GO

Dynamic SQL, as per option 1, is best avoided wherever possible so I would advise using option 2 if you can.

--James

 

[sqldevrequiretektips]

CREATE PROCEDURE [terrredesign].[P_Retrive_Dowload_data]
(@strWhere varchar(8000))AS
SET NOCOUNT ON
DECLARE @Error AS Integer

DECLARE @sql AS text(8000)

set @sql = "Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A Inner Join
(select distinct (cust_life_num) from CUSTTB where + @strWhere) B
On A.CustLifeNo = B.Cust_Life_num "

exec (@sql)

Go
Error :

The identifier that starts with 'Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A Inner Join
(select distinct (cust_life_num) ' is too long. Maximum length is 128.

 

[JamesLean]

Did you read my previous post??

SET @sql = 'Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo
from WLActCat_WLDay A Inner Join
(select distinct (cust_life_num) from CUSTTB where ' + @strWhere + ') B On A.CustLifeNo = B.Cust_Life_num'

EXEC(@sql)

And again - avoid dynamic SQL where possible. I'm not sure it's necessary here.

--James

 

[sqldevrequiretektips]

Thank you so much...

AD