dynimic SQL With IN Clause - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

dynimic SQL With IN Clause

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-05-24 : 15:53:11

I have store procedure below:

Alter Procedure "ProcEXPendingReport"
@OptDate integer,
@OptAgent integer,
@OptProvider integer,
@OptAll integer,
@DateBeg varchar(30),
@DateEnd varchar(30),
@ComCode varchar(10),
@SubAgent varchar(10),
@SpecProv varchar(5)

As
Set nocount on
Declare @SQL varchar(1000)
Begin

Select @SQL='Select * from vwPendingReport Where'
If @OptDate=2
Select @SQL=@SQL + 'dtmEntered IN (Select dtmEntered from tblMaster Where dtmEntered>=(' + @DateBeg + ') And dtmEntered<=( ' + @DateEnd + '))'
If @OptAgent=2 And @ComCode<>''
Select @SQL=@SQL +'And vchrAgentCode=' + @ComCode
If @OptAgent=2 And @SubAgent<>''
Select @SQL=@SQL + 'And vchrSubAgent=' + @SubAgent
If @OptProvider=2 And @SpecProv <> ''
Select @SQL=@SQL+ 'AND vchrInsCode=' + @SpecProv
If @OptAll=1
Select @SQL=@SQL + 'And boolPrintInfo=1'
End

EXEC (@SQL)

When I execute it, I get following error message:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IN'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'And'.

What's the right syntax?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-24 : 16:03:41

Comment out EXEC (@SQL). Put PRINT @SQL after that. What does it print out to the screen?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 16:26:50

Why use dynamic?

Why not just write 3 queries?

And don't use BETWEEN either....

Brett

8-)

stephe40
Posting Yak Master

218 Posts

Posted - 2004-05-24 : 16:35:20

Why not use BETWEEN? The server just converts it to a date<=end and date >=start expression... or am I wrong?

- Eric

stephe40
Posting Yak Master

218 Posts

Posted - 2004-05-24 : 16:40:00

You need a space after the WHERE keyword.

- Eric

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-05-25 : 09:17:22

Now, I change the store procedure like this:

Alter Procedure "ProcEXPendingReport"
@OptDate integer,
@OptAgent integer,
@OptProvider integer,
@OptAll integer,
@DateBeg varchar(30),
@DateEnd varchar(30),
@ComCode varchar(10),
@SubAgent varchar(10),
@SpecProv varchar(5)

As
Set nocount on
Declare @SQL varchar(1000)
Begin

Select @SQL='Select * from vwPendingReport Where'
If @OptDate=2
Select @SQL=@SQL +' '+ 'dtmEntered IN (Select dtmEntered from tblMaster Where dtmEntered BETWEEN (' + @DateBeg + ') And (' + @DateEnd + ')'
If @OptAgent=2 And @ComCode<>''
Select @SQL=@SQL +' '+'And vchrAgentCode= '+ @ComCode
If @OptAgent=2 And @SubAgent<>''
Select @SQL=@SQL +' '+ 'And vchrSubAgent=' + @SubAgent
If @OptProvider=2 And @SpecProv <> ''
Select @SQL=@SQL+' '+ 'AND vchrInsCode='+ @SpecProv
If @OptAll=1
Select @SQL=@SQL +' '+ 'And boolPrintInfo=1)'
End

EXEC(@SQL)

If I replace EXEC (@SQL) with PRINT(@SQL), it gives me this:

Select * from vwPendingReport Where dtmEntered IN (Select dtmEntered from tblMaster Where dtmEntered BETWEEN (11/15/2003) And (11/27/2003) And vchrAgentCode= AG AND vchrInsCode=AA And boolPrintInfo=1)

If I EXEC(@SQL), it gives me:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'AG'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'AA'.

It seems it requires '' on the parameter. What's the right syntax to add ''on the parameter?

Kristen
Test

22859 Posts

Posted - 2004-05-25 : 09:27:30

For example change:


Select @SQL=@SQL +'And vchrAgentCode=' + @ComCode


Select @SQL=@SQL +'And vchrAgentCode=' + '''' + @ComCode + ''''


Select @SQL=@SQL +'And vchrAgentCode=''' + @ComCode + ''''

Kristen

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-07-19 : 14:15:43

Thanks, Kristen.
Problem again.
I modified the store Procedure as kristen suggested. It's no problem to save the store procedure. But I got an error when I executed it.

The following is the SP after modified:

Alter Procedure ProcEXPendingReport
@OptDate integer,
@OptAgent integer,
@OptProvider integer,
@OptAll integer,
@DateBeg varchar(30),
@DateEnd varchar(30),
@ComCode varchar(10),
@SubAgent varchar(10),
@SpecProv varchar(5)

As
Set nocount on
Declare @SQL varchar(1000)
Begin

Select @SQL='Select * from vwPendingReport Where'
If @OptDate=2
Select @SQL=@SQL +' '+ 'dtmEntered IN (Select dtmEntered from tblMaster Where dtmEntered BETWEEN ('+'''' +Convert(char(20), @DateBeg,101) +''''+') And ('+'''' + Convert(char(20), @DateEnd,101) +''''+')'

If @OptAgent=2 And @ComCode<>''
Select @SQL=@SQL +' '+'And vchrAgentCode= '+ '''' + @ComCode + ''''
If @OptAgent=2 And @SubAgent<>''
Select @SQL=@SQL +' '+ 'And vchrSubAgent=' + '''' + @SubAgent + ''''
If @OptProvider=2 And @SpecProv <> ''
Select @SQL=@SQL+' '+ 'AND vchrInsCode=' + '''' + @SpecProv + ''''
If @OptAll=1
Select @SQL=@SQL +' '+ 'And ((tintDistType=1) OR (tintDistType=2) OR (tintDistType=3)))'
End

EXEC(@SQL)

When I execute it:

ProcEXPendingReport 2,1,2,2,'05/10/2004','07/14/2004',null,null,'AA'

I got an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'AA'.

Anyone can help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-19 : 14:26:48

Sure...

This is what it creates:


Select * from vwPendingReport 
Where dtmEntered IN 
(Select dtmEntered from tblMaster 
Where dtmEntered BETWEEN ('05/10/2004          ') And ('07/14/2004          ') 
AND vchrInsCode='AA'

And dynamic sql with a select *

gotta love it....

I still say there is no reason to do it this way....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-19 : 14:31:01

varchar for datetime data?

Tara

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-07-19 : 15:01:40

Thanks,Brett & Tara.

I change Convert(char(20),@DateBeg,101) to Convert(varchar(20),@DateBeg,101), it works.

By taking the suggestion of Tara, I redeclare @DateBeg,@DateEnd As smalldatetime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources