@sql nvarchar(4000) Max characters reached

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)

@sql nvarchar(4000) Max characters reached

Author

Topic

bsmith987
Starting Member

2 Posts

Posted - 2005-08-22 : 15:41:39

In my dynamic @sql below I am trying to add additional selects but I am receiving an error. I know what the problem is and that is the @sql has exceeded the maximum 4000 characters. Does anyone know a better way to add this to the dynamic @sql so that it won't exceed 4000 characters?

declare @sql nvarchar(4000)

(select top 0 ud.liner_year
from lnr_ad_detail ud inner join lnr_ad_header uh on uh.ad_header_id = ud.ad_header_id
where ud.vehicle_id = b.vehicle_id and uh.created_on = dd.creation_date and uh.company_id = ' + cast(@I_CompanyID as varchar(15)) + ') as liner_year,
(select top 0 ud.liner_make
from lnr_ad_detail ud inner join lnr_ad_header uh on uh.ad_header_id = ud.ad_header_id
where ud.vehicle_id = b.vehicle_id and uh.created_on = dd.creation_date and uh.company_id = ' + cast(@I_CompanyID as varchar(15)) + ') as liner_make,
(select top 0 ud.liner_model
from lnr_ad_detail ud inner join lnr_ad_header uh on uh.ad_header_id = ud.ad_header_id
where ud.vehicle_id = b.vehicle_id and uh.created_on = dd.creation_date and uh.company_id = ' + cast(@I_CompanyID as varchar(15)) + ') as liner_model,

I also need to add a select for liner_series, liner_bodystyle, liner_vinfull, liner_vinlast8, liner_miles, liner_retailprice, liner_internetprice, liner_stocknum

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-22 : 16:00:29

use varchar(8000)?

But this looks so wrong...why do you need to make a dynamic query that's over 4,000 bytes?

Seems out of control to me...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 16:03:24

What we can see so far of that query doesn't need to be dynamic. And why SELECT TOP 0?

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-22 : 16:42:25

what exactly will a TOP 0 do?

I'm too lazy to even test that right now..

-ec

bsmith987
Starting Member

2 Posts

Posted - 2005-08-22 : 16:42:42

The full stored procedure is below and I tried using varchar(8000) but it says I must use nvarchar and not varchar.

Any other ideas?
----------------------------------------

ALTER PROCEDURE spSearchLinerVehicles
@I_CompanyID int,
@I_SubPubID int,
@I_StockNum varchar(15) = '',
@I_AgeFrom int = null,
@I_AgeTo int = null,
@I_RetailFrom int = null,
@I_RetailTo int = null,
@I_InvFlag char(1) = 'N',
@I_AppFlag char(1) = 'N',
@I_SentFlag char(1) = 'N',
@I_NotSentFlag char(1) = 'Y',
@I_BeginDate varchar(20),
@I_EndDate varchar(20),
@I_SortColumn int = 4,
@I_SortDir char(1) = 'A',
@O_ErrStatus varchar(3) OUTPUT,
@O_ErrMessage varchar(500) OUTPUT
AS
Set @O_ErrStatus = 'OK'

declare @sql nvarchar(4000)
declare @varColumns varchar(1000)
declare @cName varchar(50)
declare @typeCode int
declare @sortDirection varchar(5)

if @I_SortDir = 'D'
set @sortDirection = 'desc'
else
set @sortDirection = 'asc'

declare target_cursor CURSOR FOR
select
a.column_name as column_name, a.type_code
from
lnr_fields a inner join
lnr_sub_pub_field_rel b on a.field_id = b.field_id
where
b.sub_publication_id = @I_SubPubID
order by
b.field_order

Open target_cursor
Set @varColumns = ''
Set @sql = ''
Fetch Next From target_cursor Into @cName, @typeCode
While @@FETCH_STATUS = 0
Begin
If len(@varColumns) > 0
Begin
Set @varColumns = @varColumns + ' + ''|'' + '
End
Set @varColumns = @varColumns + '''' + cast(@typeCode as varchar(15)) + '|'' + ' + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'
Fetch Next From target_cursor Into @cName, @typeCode
End
Close target_cursor
Deallocate target_cursor

if len(@varColumns) = 0
begin
Set @O_ErrStatus = 'C'
Set @O_ErrMessage = 'There are no results, likely due to a corrupt publication profile.
Please call support and request that the profile number: [' + cast(@I_SubPubID as varchar(20)) + '] be reviewed.'
GOTO FINISHED
end

Set @sql = N'
select
top 200
b.vehicle_id,
(select count(*) from lnr_ad_detail tor inner join lnr_ad_header bor on tor.ad_header_id = bor.ad_header_id
where bor.created_on between ''' + @I_BeginDate + ''' and ''' + @I_EndDate + ''' and tor.vehicle_id = b.vehicle_id and bor.company_id = ' + cast(@I_CompanyID as varchar(20)) + '
and tor.status_code = 1) as car_exists,
b.vin,
b.stock_number,
vm.year,
vm.make,
vm.model,
vm.series,
b.mileage,
dd.creation_date as creation_date,
d.sent_date as liner_ad_sent_date,
isnull(b.in_value, 0) as in_value,
b.recon,
b.condition_id,
b.acv,
isnull(b.retail_value, 0) as retail_value,
days_inv = case when b.status_id = 13 then dateDiff(dd,in_date, getdate())end,
cast(b.vehicle_id as varchar(20)) + '','' + cast(isnull((select top 1 vehicle_photo_id from aax_vehicle_photos where vehicle_id = b.vehicle_id and prefered = 1),-1) as varchar(20)) as liner_ad_vpic_id,
cast((select count(*) from aax_vehicle_photos where vehicle_id = b.vehicle_id) as varchar(20)) + '' - '' + case cast((select count(*) from aax_vehicle_photos where vehicle_id = b.vehicle_id and prefered = 1) as varchar(15)) when ''0'' then ''N'' else ''Y'' end as pinfo,
' + @varColumns + ' as linerText,
(select count(0) from lnr_ad_detail h where h.vehicle_id = b.vehicle_id and h.status_code = 1) as pend,
(select top 0 ud.liner_year
from lnr_ad_detail ud inner join lnr_ad_header uh on uh.ad_header_id = ud.ad_header_id
where ud.vehicle_id = b.vehicle_id and uh.created_on = dd.creation_date and uh.company_id = ' + cast(@I_CompanyID as varchar(15)) + ') as liner_year
from
StickEmUp.dbo.vehicle a with (nolock) right outer join
aax_vehicle b with (nolock) on a.vin = b.vin and a.company_id = b.company_id inner join
aax_vin_master vm with (nolock) on b.vin = vm.vin
left outer join
aax_condition_ctl acc on b.condition_id = acc.condition_id
left outer join
(select
max(f.date_sent) as sent_date,
k.company_id,
g.vehicle_id
from
lnr_ad_header f inner join
lnr_ad_detail g on f.ad_header_id = g.ad_header_id inner join
aax_vehicle k on g.vehicle_id = k.vehicle_id
group by
k.company_id,
g.vehicle_id) d on b.vehicle_id=d.vehicle_id left join
(select
max(ff.created_on) as creation_date,
kk.company_id,
gg.vehicle_id
from
lnr_ad_header ff inner join
lnr_ad_detail gg on ff.ad_header_id = gg.ad_header_id inner join
aax_vehicle kk on gg.vehicle_id = kk.vehicle_id
where ff.created_on between ''' + @I_BeginDate + ''' and ''' + @I_EndDate + '''
group by
kk.company_id,
gg.vehicle_id) dd on b.vehicle_id=dd.vehicle_id left join
lnr_ad_detail e on b.vehicle_id=e.vehicle_id left join
lnr_ad_header f on e.ad_header_id = f.ad_header_id

where
f.created_on between ''' + @I_BeginDate + ''' and ''' + @I_EndDate + ''' and b.company_id = ' + cast(@I_CompanyID as varchar(20))

if @I_StockNum <> ''
set @sql = @sql + N' and b.stock_number = ''' + @I_StockNum + ''''
else
begin
if @I_AppFlag = 'Y'
begin
if @I_InvFlag = 'Y'
begin
set @sql = @sql + N' and ((b.status_id = 13'
if @I_AgeFrom is not null and @I_AgeTo is not null
set @sql = @sql + N' and b.in_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()-' + cast(@I_AgeFrom as varchar(20))
else if @I_AgeFrom is null and @I_AgeTo is not null
set @sql = @sql + N' and b.in_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()'
else if @I_AgeTo is null and @I_AgeFrom is not null
set @sql = @sql + N' and b.in_date <= getdate()-' + cast(@I_AgeFrom as varchar(20))
set @sql = @sql + N') or (b.status_id in (21,22)'
if @I_AgeFrom is not null and @I_AgeTo is not null
set @sql = @sql + N' and b.creation_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()-' + cast(@I_AgeFrom as varchar(20))
else if @I_AgeFrom is null and @I_AgeTo is not null
set @sql = @sql + N' and b.creation_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()'
else if @I_AgeTo is null and @I_AgeFrom is not null
set @sql = @sql + N' and b.creation_date <= getdate()-' + cast(@I_AgeFrom as varchar(20))
set @sql = @sql + N'))'
end
else
begin
set @sql = @sql + N' and b.status_id in (21,22)'
if @I_AgeFrom is not null and @I_AgeTo is not null
set @sql = @sql + N' and b.creation_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()-' + cast(@I_AgeFrom as varchar(20))
else if @I_AgeFrom is null and @I_AgeTo is not null
set @sql = @sql + N' and b.creation_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()'
else if @I_AgeTo is null and @I_AgeFrom is not null
set @sql = @sql + N' and b.creation_date <= getdate()-' + cast(@I_AgeFrom as varchar(20))
end
end
else
begin
set @sql = @sql + N' and b.status_id = 13'
if @I_AgeFrom is not null and @I_AgeTo is not null
set @sql = @sql + N' and b.in_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()-' + cast(@I_AgeFrom as varchar(20))
else if @I_AgeFrom is null and @I_AgeTo is not null
set @sql = @sql + N' and b.in_date between getdate()-' + cast(@I_AgeTo as varchar(20)) + ' and getdate()'
else if @I_AgeTo is null and @I_AgeFrom is not null
set @sql = @sql + N' and b.in_date <= getdate()-' + cast(@I_AgeFrom as varchar(20))
end

if @I_RetailFrom is not null
set @sql = @sql + N' and b.retail_value >= ' + cast(@I_RetailFrom as varchar(20))
if @I_RetailTo is not null
set @sql = @sql + N' and b.retail_value <= ' + cast(@I_RetailTo as varchar(20))
if @I_SentFlag = 'N' and @I_NotSentFlag = 'Y'
set @sql = @sql + N' and d.sent_date is null'
else if @I_SentFlag = 'Y' and @I_NotSentFlag = 'N'
set @sql = @sql + N' and d.sent_date is not null'
end
set @sql = @sql + N' group by
b.vehicle_id,
b.vin,
b.stock_number,
vm.year,
vm.make,
vm.model,
vm.series,
vm.bodyStyle,
vm.make,
vm.model,
b.mileage,
dd.creation_date,
d.sent_date,
b.in_value,
b.recon,
b.condition_id,
b.acv,
case when b.status_id = 13 then dateDiff(dd,in_date, getdate())end,
b.retail_value,
b.internet_value '
if @I_SortColumn = 1
set @sql = @sql + N' order by
b.stock_number ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 2
set @sql = @sql + N' order by
case when b.status_id = 13 then dateDiff(dd,in_date, getdate())end ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 3
set @sql = @sql + N' order by
vm.year + '' '' + vm.make + '' '' + vm.series + '' '' + vm.bodyStyle ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 4
set @sql = @sql + N' order by
vm.make + '' '' + vm.make + '' '' + vm.series + '' '' + vm.bodyStyle ' + @sortDirection + ', vm.make'
else if @I_SortColumn = 5
set @sql = @sql + N' order by
vm.model + '' '' + vm.make + '' '' + vm.series + '' '' + vm.bodyStyle ' + @sortDirection + ', vm.make'
else if @I_SortColumn = 6
set @sql = @sql + N' order by
vm.series + '' '' + vm.make + '' '' + vm.series + '' '' + vm.bodyStyle ' + @sortDirection + ', vm.make'
else if @I_SortColumn = 7
set @sql = @sql + N' order by
b.mileage ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 8
set @sql = @sql + N' order by
isnull(b.in_value, 0) ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 9
set @sql = @sql + N' order by
b.acv ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 10
set @sql = @sql + N' order by
isnull(b.retail_value, 0) ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 11
set @sql = @sql + N' order by
dd.creation_date ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 12
set @sql = @sql + N' order by
d.sent_date ' + @sortDirection + ', vm.make, vm.model'
else if @I_SortColumn = 13
set @sql = @sql + N' order by
b.condition_id ' + @sortDirection + ', vm.make, vm.model'
else
set @sql = @sql + N' order by
case when b.status_id = 13 then dateDiff(dd,in_date, getdate())end ' + @sortDirection + ', vm.make, vm.model'
execute sp_executesql @sql

FINISHED:
RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 16:46:35

Wow, a cursor and dynamic SQL! I guess you aren't concerned about performance.

Take a look at these articles about ways to do dynamic ORDER BYs and WHERE clauses without the use of dynamic SQL:
http://www.sqlteam.com/item.asp?ItemID=2209
http://www.sqlteam.com/item.asp?ItemID=2077

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 01:40:51

quote:
Originally posted by eyechart

what exactly will a TOP 0 do?

I'm too lazy to even test that right now..

-ec

I think it will only retreive table structure

To copy only structure, we may use this (although the target table wont have indices,constraints,etc)

Select * into newtable from Oldtable where 1=0

The same can be done with top 0 as well

Select * into newtable from (Select top 0 * from Oldtable) T

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 03:01:37

quote:
Originally posted by madhivanan

quote:
Originally posted by eyechart

what exactly will a TOP 0 do?

I'm too lazy to even test that right now..

-ec

I think it will only retreive table structure

To copy only structure, we may use this (although the target table wont have indices,constraints,etc)

Select * into newtable from Oldtable where 1=0

The same can be done with top 0 as well

Select * into newtable from (Select top 0 * from Oldtable) T

Madhivanan

yeah, that makes sense.

-ec

iloveuzak
Starting Member

1 Post

Posted - 2005-08-23 : 05:54:37

It's so simple dear

Instead of "declare @sql nvarchar(4000)"
You use "declare @sql ntext"

ZAK IS A ZAK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources