February 3, 2004 at 5:51 am
Hi
I have created the following sp:
CREATE PROCEDURE test
@UnitID varchar (8000)
AS
declare @Cursor varchar (8000)
set @Cursor = 'declare dbcursor cursor for select distinct unitid from posdata where unitid in (' + @Unitid + ') '
exec (@Cursor)
Open Dbcursor
declare @FinalSQL varchar (8000)
set @FinalSQL = ' '
declare @CursorID varchar (8000)
fetch next from dbcursor into @CursorID
while @@fetch_status = 0
begin
set @FinalSQL = @FinalSQL + 'select top 9 * from (select top 9 * from posdata where unitid = ' + '''' + @CursorID + ''' order by datetime desc) dt '
fetch next from dbcursor into @CursorID
if (@@fetch_status = 0)
begin
set @FinalSQL = @FinalSQL + ' Union all '
end
end
close dbcursor
deallocate dbcursor
exec (@FinalSQL)
The string can easily go over 8000, is there any other way I could do this so the string could be say for example 100000 in length??????
Thanks in advance
February 3, 2004 at 6:21 am
A string of 100k length? What are you trying to do?
Maybe this will help you http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 3, 2004 at 7:35 am
Use a temp table and a loop
CREATE TABLE #temp(....)
DECLARE @startpos int,@endpos int
SET @startpos = 1
WHILE (@startpos < LEN(@UnitID))
BEGIN
SET @endpos = CHARINDEX(',',@UnitID+',',@startpos)
INSERT INTO #temp
SELECT top 9 * from posdata
where unitid = SUBSTRING(@UnitID,@startpos,@endpos - @startpos)
order by datetime desc
SET @startpos = @endpos + 1
END
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
February 3, 2004 at 8:04 am
I would put the UnitIDs in question into a temporary table rather than a concatenated string. No need for a cursor, loop, or dynamic SQL. Using PKId as the primary key here:
SELECT p.*
FROM PosData p JOIN #TempTable t ON t.UnitId = p.UnitId
WHERE PKId IN
(SELECT TOP 9 PKId
FROM PosData
WHERE UnitId = p.UnitId
ORDER BY [DateTime] DESC)
--Jonathan
February 3, 2004 at 8:31 am
Hi
Thanks for your replies it has been a great help and have got it working now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply