June 11, 2008 at 2:56 am
Hi,
This is the procedure i have written
alter PROCEDURE [dbo].[getVehicleSummary_check]
@userid varchar(50)
as
--exec [getVehicleSummary_check] 'admin'
declare @table_history nvarchar(50)
declare @str1 nvarchar(4000)
begin
if(@UserId='admin')
begin
--get from both tables
select @str1='select registrationno + '',''+tablename1+''next'' as RegTable from mapping.[dbo].virtualmapping_admin_web union select registrationno+'',''+tablename1 +''next'' as RegTable from mapping.[dbo].virtualmapping_web '
execute sp_executesql @str1
end
end
Now I get the result as
98B065372E,gpsdata1next
AL 001,gpsdata2next
AL 002,gpsdata2next
AP 22 H 1310,gpsdata28next
AP 02 U 3312,gpsdata28next
Now what i want is i want to select the otherfields present in the table for that particular registrationno
i.e. select speed,location,gps_datetime from gpsdata1 where registrationno='98B065372E'
select speed,location,gps_datetime from gpsdata2 where registrationno='AL 001'
select speed,location,gps_datetime from gpsdata28 where registrationno='AP 02 U 3312'
In this way there will be 578 rows, after i retrieve this I have to do a join
Kindly let me know how to split each and every values and concatenate as above
regards
Hema
June 12, 2008 at 8:43 am
I would eliminate the dynamic SQL for the primary query, and use a cursor to traverse the primary query, and for each resulting record, use dynamic SQL to query the necessary table. If you know how to traverse a recordset using a cursor, combining that with dynamic SQL for the subquery should be fairly easy. Just be careful with dynamic SQL with respect to the potential for SQL injection. Others with more experience at how to avoid that kind of problem should speak to that, as I don't have the necessary experience on that front.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 12, 2008 at 1:28 pm
May I ask why you are combining the data into a single string?
I would have thought it would be more beneficial to keep each data item separate to negate the need to split the string at a later stage. My approach would be to do your entire query processing within the stored procedure also.
First create you initial result set as a temporary table:
select registrationno,tablename
into #tmp_resultSet
from mapping.[dbo].virtualmapping_admin_web
union select registrationno,tablename1
from mapping.[dbo].virtualmapping_web
Then as steve suggests process each record as a dynamic SQL query. Also rather than using a cursor, you may prefer to create a table with an autoid and sequence through it using a typical loop strucutre.
Hope this helps.
Cheers,
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply