I have been searching but can't seem to locate an answer for this. I have a file I need to create that has a header row with the count of the number of rows that will be following for checking. When the initial test sp was built there were only the TOP 10 records selected and when the SQL was run as text it worked perfectly. Now when the 'SELECT TOP 10' is replaced with simply SELECT, it ceases to work and I simply get count of the rows affected and the word 'record' Here is the query if someone can tell what is wrong. I have been able to get it to run up to 10999. Anything higher and it breaks.
Code:
IF OBJECT_ID('tempdb..#temp1')IS NOT NULL BEGIN
drop table #temp1
END
IF OBJECT_ID('tempdb..#temp2')IS NOT NULL BEGIN
drop table #temp2
END
declare @xRowCount numeric
CREATE TABLE #temp1
(record nvarchar(255)
)
INSERT INTO #temp1
SELECT --TOP 19000
LEFT(CONVERT(VARCHAR,isnull(ie.ind_employee_id_ext, '')),10) +
Left(CONVERT(varchar,isnull(ie.ind_ssn_ext,''))+'000000000',9) +
substring(CAST (dbo.client_H2U_GetPaddedString(20,'R',' ',i.ind_first_name) as varchar(20)),1,20) +
substring(CAST (dbo.client_H2U_GetPaddedString(30,'R',' ',i.ind_last_name) as varchar(30)),1,30)
FROM
co_individual i
JOIN co_individual_ext ie (NOLOCK) ON i.ind_cst_key=ie.ind_cst_key_ext
JOIN co_customer c (NOLOCK) ON c.cst_key=i.ind_cst_key and i.ind_delete_flag=0
join lab_screening_result lr (nolock) on lr.h14_cst_key = c.cst_key
and lr.h14_delete_flag = 0
WHERE h14_h15_key ='6C7CFD35-E293-4983-8C7B-B4242C1435AB'
Set @xRowCount = @@ROWCOUNT
CREATE TABLE #temp2
(record nvarchar(255)
)
Insert into #temp2
SELECT dbo.client_GetPaddedString(9,'L','0',@xRowCount) + CONVERT(char(8), GETDATE(), 112) + dbo.client_GetPaddedString(51,'L',' ',' ')
SELECT record
FROM #temp2
UNION
SELECT record
from #temp1
DROP TABLE #temp1
DROP TABLE #temp2