calpha
asked on
MSSQL---Using Execute & Dynamic SQL Statements to return a COUNT to a variable
My SQL Statement below is dynamic based on server name and db name and the servers i'm using are linked----however, I can't figure out how to get the Count from my select statement. My Rowcount is always coming back as 1, when it should be zero in some cases.
I've tried:
Code:---------------------
if ((@SERV_NAME != '') and (@DB_NAME != ''))
begin
set @strSQL = 'select count(*)from ' + ltrim(rtrim(@SERV_NAME)) + '.' + ltrim(rtrim(@DB_NAME)) + '.dbo.sysobjects where name = ''images'''
execute(@strSQL)
set @tmp_rc = @@ROWCOUNT
--------------------------
and
Code:---------------------
declare @myCount int
set @strSQL = 'select @myCount = count.....
execute (@strsql)
--------------------------
Obviously, select @myCount = count..... works fine if it's not dynamic (ie, in a varchar variable), but I can't figure out how
I've tried:
Code:---------------------
if ((@SERV_NAME != '') and (@DB_NAME != ''))
begin
set @strSQL = 'select count(*)from ' + ltrim(rtrim(@SERV_NAME)) + '.' + ltrim(rtrim(@DB_NAME)) + '.dbo.sysobjects where name = ''images'''
execute(@strSQL)
set @tmp_rc = @@ROWCOUNT
--------------------------
and
Code:---------------------
declare @myCount int
set @strSQL = 'select @myCount = count.....
execute (@strsql)
--------------------------
Obviously, select @myCount = count..... works fine if it's not dynamic (ie, in a varchar variable), but I can't figure out how
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is very fair. I appreciate the explanation all the same, not many do that here.
Thanks,
Anthony
Thanks,
Anthony
ASKER
Even though you were dead on acperkins, I felt i had to split points because bhess1 gave me the exact code I needed to use.