sql string problem - 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)

sql string problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-13 : 06:31:15

Hi i get a ayntax error when i'm trying to pass a sql string into a stored proc. I'm trying to do the following and i get a syntax error. can anyone tell me what i'm doing wrong. Thanks

I'm passing in two date variables as a string

@datefrom varchar(255) = null,
@dateto varchar(255) = null

the above get passed in as a string as follows
@datefrom = 'October 13, 2003'
@dateto = 'November 13, 2003'

if not @datefrom is null
select @sql = @sql + ' t1.requestEnteredTime BETWEEN convert (datetime, ''' + '''' + cast(@datefrom as varchar(255)) + '''' + ')' + ' and dateadd(ms,-2, convert(datetime, ' + '''' + cast(@datefrom as varchar(255)) + '''' + ')' + '+1'

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-13 : 06:41:38

The @sql portion of the string you are creating here has two ' in front of the date and is missing the end bracket for the dateadd :-

t1.requestEnteredTime BETWEEN convert (datetime, ''October 13, 2003') and dateadd(ms,-2, convert(datetime, 'October 13, 2003')+1

Try this:-

select @sql = ' t1.requestEnteredTime BETWEEN convert (datetime, ''' + cast(@datefrom as varchar(255)) + ''')' + ' and dateadd(ms,-2, convert(datetime, ' + '''' + cast(@datefrom as varchar(255)) + ''') +1)'

Raymond

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-13 : 06:47:11

Hi Raymond,

thanks for the reply. I tried it and now i get the folloing erro message
'Incorrect syntax near the keyword 'BETWEEN'. ?

I can't see whats wrong. Any more help appreciated

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-13 : 06:54:11

This is the @sql we are creating now:-

t1.requestEnteredTime BETWEEN convert (datetime, 'October 13, 2003') and dateadd(ms,-2, convert(datetime, 'October 13, 2003') +1)

This looks Ok as the end part of a SQL statement. It must be the preceding part of @sql.

What I normally do to debug is instead of executing it, 'PRINT' @sql and check the entire statement. If necessary copy the statement into a new window and execute it from there to find the problem.

If you have no luck, post the entire @sql.

Raymond

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-13 : 08:17:55

hi,

thanks again for getting back. still can't work it out. I've pasted my entire stored proc. Its a bit lengthy but i hope you can help. thanks

ALTER procedure dbo.fileDeliverySearch

/* declare input variables */
@sqlFilter varchar(1000) = null,
@ftpdestinationfilter varchar(1000) = null,
@studio varchar(50) = null,
@country varchar(1000) = null,
@element varchar(1000) = null,
@media varchar(10) = null,
@ftpdestination varchar(40) = null,
@filmtitle varchar(255) = null,
@requestid varchar(6) = null,
@foldername varchar(100) = null,
@status varchar(20) = null,
@user varchar(255) = null,
@datefrom varchar(255) = null,
@dateto varchar(255) = null
as

begin

select
s.archiveRequestId,
s.targetpathfilename,
substring(reverse(SUBSTRING ( reverse(s.sourcePathFileName) , 1 , charindex('\',reverse(s.sourcePathFileName))-1 ) ), 1, charindex('_',reverse(SUBSTRING ( reverse(s.sourcePathFileName) , 1 , charindex('\',reverse(s.sourcePathFileName)) -2 ) ))) as jobnumber,
s.requestEnteredTime,
s.processStartTime,
s.processEndTime,
s.noofTimesProcessed,
s.destinationid,
s.failedMessage,
s.requestComplete,
datediff(ss, s.processStartTime, s.processEndTime ) as starttilldeliverytime,
datediff(ss, s.requestEnteredTime, s.processEndTime ) as requesttilldeliverytime,
datediff(ss, s.requestEnteredTime, s.processStartTime ) as requesttilldeliverystart,
f.[name],
a.requestId,
u.fullname
into
#temp1
from
sendrequest s inner join
ftpdestination f on s.destinationid = f.destinationid left outer JOIN
CSA.dbo.ArchiveRequest a ON s.archiveRequestId = a.archiveRequestId left outer join
[user] u on a.userid = u.userid

DECLARE @sql varchar(3000)

SELECT @sql = '

select
t1.archiveRequestId,
t1.targetpathfilename,
t1.jobnumber,
t1.requestEnteredTime,
t1.processStartTime,
t1.processEndTime,
t1.noofTimesProcessed,
t1.destinationid,
t1.failedMessage,
t1.requestComplete,
t1.starttilldeliverytime,
t1.requesttilldeliverytime,
t1.requesttilldeliverystart,
t1.[name],
t1.requestId,
t1.fullname,
j.tmjobnumber,
b.bomid,
f.film,
f.studio,
c.country,
e.element,
cl.classification,
b.template,
b.CatalogueNoCalc,
b.PackingCodeCalc
from
#temp1 t1 left outer join
CSA.dbo.jobs j on t1.jobnumber = j.tmjobnumber left outer join
CSA.dbo.bom b on j.bomid = b.bomid left outer join
CSA.dbo.films f on b.filmid = f.filmid left outer join
CSA.dbo.CountriesLookup c ON b.CountryID = c.CountryID left outer join
CSA.dbo.ElementLookup e ON b.ElementID = e.ElementID left outer JOIN
CSA.dbo.ClassificationLookup cl ON b.ClassificationID = cl.ClassificationID'

if not @sqlFilter is null
select @sql = @sql + ' ' + @sqlFilter

if not @ftpdestinationfilter is null
--select @sql = @sql + ' and t1.destinationId in (''A2B24A37-55D0-4314-98E0-12BD44B92ED4'',''792FE5B0-0F8F-46D0-8BC7-0BBBD29B970E'')'
--select @sql = @sql + ' and t1.destinationId in ( ''' + replace( @ftpDestinationFilter, '"', '''' ) + ')'
select @sql = @sql + ' and t1.destinationId in ( select ArrayValue from dbo.ArrayToTable( ''' + @ftpDestinationFilter + ''', '','' ) )'

if not @studio is null
select @sql = @sql + ' AND f.studio = ''' + @studio + ''''

if not @country is null
select @sql = @sql + ' and b.CountryID in ( select ArrayValue from dbo.ArrayToTable( ''' + @country + ''', '','' ) )'

if not @element is null
select @sql = @sql + ' and b.ElementID in ( select ArrayValue from dbo.ArrayToTable( ''' + @element + ''', '','' ) )'

if not @media is null
select @sql = @sql + ' and b.Template = ''' + @media + ''''

if not @ftpdestination is null
select @sql = @sql + ' and t1.destinationid = ''' + @ftpdestination + ''''

if not @filmtitle is null
select @sql = @sql + ' and f.Film like ' + '''%' + cast( @filmtitle as varchar(255) ) + '%'''

if not @requestid is null
select @sql = @sql + ' and t1.requestId = ''' + @requestid + ''''

if not @foldername is null
select @sql = @sql + ' and t1.targetpathfilename like ' + '''%' + cast( @foldername as varchar(255) ) + '%'''

if not @status is null
if @status = 'delivered'
select @sql = @sql + ' and (t1.failedMessage = ''' + '''''' + ''' or t1.failedMessage is null) and t1.requestComplete = 1 '
if @status = 'failed'
select @sql = @sql + ' and (t1.failedMessage <> ''' + '''''' + ''' or t1.failedMessage <> null) and t1.requestComplete = 1 '
if @status = 'In Process'
select @sql = @sql + ' and t1.requestComplete = 0 '

if not @user is null
select @sql = @sql + ' and t1.fullname like ' + '''%' + cast( @user as varchar(255) ) + '%'''

if not @datefrom is null
--select @sql = @sql + ' t1.requestEnteredTime BETWEEN convert (datetime, ''' + '''' + cast(@datefrom as varchar(255)) + '''' + ')' + ' and dateadd(ms,-2, convert(datetime, ' + '''' + cast(@datefrom as varchar(255)) + '''' + ')' + '+1'
select @sql = ' t1.requestEnteredTime BETWEEN convert (datetime, ''' + cast(@datefrom as varchar(255)) + ''')' + ' and dateadd(ms,-2, convert(datetime, ' + '''' + cast(@datefrom as varchar(255)) + ''') +1)'

--t1.requestEnteredTime BETWEEN convert (datetime,'@datefrom') AND dateadd(ms,-2, convert(datetime,'@dateto')+1)

execute (@sql)

end
go

grant execute on dbo.fileDeliverySearch to public
go

execute dbo.fileDeliverySearch
@sqlFilter = 'WHERE ( ( f.studio in (''Disney'')) OR ( f.studio in (''DreamWorks'')) OR ( f.studio in (''MGM'')) OR ( f.studio in (''Paramount'')) OR ( f.studio in (''TCFHE'')) OR ( f.studio in (''Universal'')) ) ',
--@ftpdestinationfilter = '"6DF6A6D1-5A4F-44B9-B776C-47310DC015F9","792FE5B0-0F8F-46D0-8BC7-0BBBD29B970E","1876EDC7-CBF9-4DDD-94A0-1155524E4B1D","B1A080F3-92BF-48E8-AD98-E028147D2863","91AD1CE5-A269-4D64-9C51-BB6F5F8514EE","7B15CFC9-743C-490B-A5B3-21FB536AE204","8D0B11FC-8614-43BC-974B-44E352071CFA","80EB52EF-C48A-4F5D-A571-BD2B24EF4AB2","2EB5AF65-B82F-40C6-962F-FA32CF207867","CA811A89-8FC4-4334-AD67-398F5B388E67","A2B24A37-55D0-4314-98E0-12BD44B92ED4","30BCE6AC-C2BC-4F80-978D-73336D440C86"',
@ftpdestinationfilter = 'A2B24A37-55D0-4314-98E0-12BD44B92ED4,792FE5B0-0F8F-46D0-8BC7-0BBBD29B970E',
@studio = 'mgm',
@country = '183,257,237,120,97,85',
@element = '381,172,97,70',
@media = 'dvd',
@ftpdestination = 'A2B24A37-55D0-4314-98E0-12BD44B92ED4',
@filmtitle = 'bond',
@requestid = 'GSBWRW',
@foldername = 'gary_test',
@user = 'gary',
@status = 'delivered',
@datefrom = 'October 13, 2003',
@dateto = 'November 10, 2003'

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-13 : 08:27:20

Is the problem that your final addition into @sql starts with:

set @sql = ' t1.requestEnteredTime ..........

instead of

set @sql = @sql + ' and t1.requestedTimeEntered ........

??

Raymond

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-13 : 12:19:59

yep you were right. got it working. cheers for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources