Problem executing - 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

Transact-SQL (2000)

Problem executing

Author

Topic

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 18:45:27

Hi

I'm having a problem when I execute a select statement in string mode. like exec('select * from tbl1') but when I just select the select statement in string mode, like select('select * from tbl1') it is working. it's not giving me an error.

the error i get if i execute it is:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

here's my sql code:

declare @strPrefix varchar(8000)
set @strPrefix = '100,200,300'
declare @strPercent varchar(1)
set @strPercent = '%'
declare @chrComma varchar(1)
set @chrComma = ','
declare @strSQL varchar(8000)
declare @ctr int
declare @lastCharIndexPlusOne int
set @lastCharIndexPlusOne = 1
set @ctr = 3
set @strSQL = 'select StoreID, OrderNumber, ItemCode, AdjQty, ItemStatus from tblOrderDetails where'
declare @strSQLCriteria varchar(8000)
set @strSQLCriteria = ''

while @ctr > 0
begin
set @strSQLCriteria = @strSQLCriteria + ' left(ItemCode, 7) like substring(''' + @strPrefix + ''', cast('''+ cast(@lastCharIndexPlusOne as varchar) + ''' as int), (charindex(''' + @chrComma + ''', ''' + @strPrefix + ''', cast(''' + cast(@lastCharIndexPlusOne as varchar) + ''' as int))) - (cast(''' + cast(@lastCharIndexPlusOne as varchar) + ''' as int))) + ''' + @strPercent + ''' OR'
set @lastCharIndexPlusOne = (charindex(',', @strPrefix, @lastCharIndexPlusOne)) + 1
set @ctr = @ctr - 1
end

set @strSQLCriteria = left(@strSQLCriteria, (len(@strSQLCriteria) - 3))
set @strSQLCriteria = 'select StoreID, OrderNumber, ItemCode, AdjQty, ItemStatus from tblOrderDetails' + char(13) + 'where' + @strSQLCriteria
exec(@strSQLCriteria)

-------------------------------

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 18:47:26

It looks like you are missing a space after the where:

set @strSQLCriteria = 'select StoreID, OrderNumber, ItemCode, AdjQty, ItemStatus from tblOrderDetails' + char(13) + 'where' + @strSQLCriteria
exec(@strSQLCriteria)

To see what string you are generating, always run PRINT(@strSQLCriteria) prior to trying out the EXEC. Make sure it looks right, then try the EXEC.

Tara Kizer
aka tduggan

hybridGik
Starting Member

26 Posts

Posted - 2006-07-06 : 19:03:05

hi

got the problem.

it's because of the contents within the loop

im passing a negative value to a substring function.

i solved it by adding ',000' to the variable @strPrefix

thanks.:d

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources