Stored Procedure and Dynamic SQL

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)

Stored Procedure and Dynamic SQL

Author

Topic

aax
Starting Member

11 Posts

Posted - 2005-08-29 : 09:22:28

I need to figure out how to make my stored procedure and @sql still work if len(@varModel) = 0

It is currently giving me errors in the @sql if len(@varModel) = 0
' + @varModel + ' as linerModel,

-- Model
declare target_cursor CURSOR FOR
select
a.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
a.column_name = 'vm.model' and b.sub_publication_id = @I_SubPubID
order by
b.field_order

Open target_cursor

Set @varModel = ''

Fetch Next From target_cursor Into @cName, @typeCode
While @@FETCH_STATUS = 0
Begin
If len(@varModel) > 0
Begin
Set @varModel = @varModel + ' + '''' + '
End
Set @varModel = @varModel + 'isnull(cast(' + @cName + ' as varchar(25)),'''')'
Fetch Next From target_cursor Into @cName, @typeCode
End
Close target_cursor
Deallocate target_cursor
--

Set @sql = '
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,
isnull(b.internet_value, 0) as internet_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,
' + @varMake + ' as linerMake,
' + @varModel + ' as linerModel,
...
...
...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 09:27:05

Post the table structure, sample data and the result you want
Why do you use cursor?

Madhivanan

Failing to plan is Planning to fail

aax
Starting Member

11 Posts

Posted - 2005-08-29 : 10:18:27

I am using cursor to retrieve the column_name and typecode

Fetch Next From target_cursor Into @cName, @typeCode
While @@FETCH_STATUS = 0
Begin
If len(@varModel) > 0
Begin
Set @varModel = @varModel + ' + '''' + '
End
Set @varModel = @varModel + 'isnull(cast(' + @cName + ' as varchar(25)),'''')'
Fetch Next From target_cursor Into @cName, @typeCode
End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 10:23:24

Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

aax
Starting Member

11 Posts

Posted - 2005-08-29 : 11:56:55

I got it to work using this:
' + case when @varModel = '' then '''''' else @varModel end + ' as linerModel,

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-30 : 05:36:08

The oft-posted advice around here is 'to avoid cursors' IF at all possible. IF you can follow the advice "Post the table structure, sample data and the result you want",, you may be pleased to receive a solution that would a) perform far better and b) educate you to the benefits of using SET-BASED processing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources