Execute SQL Task return 0 record
-
Hi,
I put a SELECT statement in execute sql task and it return no record. I want to handle this retireved value. i.e. if there are records retrieved, do a), if no, do b).
I know i can use the Count(*) and store the result to global variable. But i also need put the fields value to global variables too if there are records.
What's the best way to do that? or use other task?
thanks
-
This was removed by the editor as SPAM
-
You might try querying @@rowcount. It should contain the number of rows in your result set.
-
You could do
IF EXISTS (SELECT ...)
BEGIN ...
END
ELSE
-
Here's what I do. This stored procedure query looks for errors in a table after users at various sites have entered data. If a site has no errors, they get a report that gives blanks where they would normally have data, and a message telling them they have done a good job.
SELECT
InsCo,
Patient,
PtNum,
CertLen,
CertNo,
CertScreen,
InsCd,
InsName,
Site
FROM
VtblCertNoValidAllIns
WHERE
Site = @Site
UNION
--if there are no results for a site
--give a default message
SELECT
'' InsCo,
'' Patient,
'' PtNum,
'' CertLen,
'' CertNo,
'All OK, Good Job!' CertScreen,
'' InsCd,
'' InsName,
@Site Site
WHERE NOT EXISTS
(SELECT
Site
FROM
VtblCertNoValidAllIns
WHERE
Site = @Site
)
-
I use exists too.
see this part of a for next loop
if NOT exists (
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = object_id('TblRapporten')
and OBJECTPROPERTY(sysobjects.id, N'IsUserTable') = 1
and syscolumns.name= @KOLOMNAAM
and sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
 
BEGIN --if
/* DE COLOM BESTAAT NIET, DUS MAAK HEM AAN */
EXEC('ALTER TABLE TblRapporten ADD ['+ @KOLOMNAAM + '] BIT NULL ' )
END --if
-
Thanks you all for the reply.
But my question is to store the resaults (no record or has records with the fields values) to global varibles in Execute SQL task in DTS packages.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply