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)
--------------------------------------------------------------------------------Neither one works right. Help please---what am i missing

Obviously, select @myCount = count..... works fine if it's not dynamic (ie, in a varchar variable), but I can't figure out how
calphaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
You need to use the sp_executesql extended stored procedure to return the count.  Checkout this article from MSDN:
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/default.aspx?scid=kb;en-us;262499

Anthony
0
Brendt HessSenior DBACommented:
YOu can do this with the sp_executeSQL Stored Procedure, e.g.:

if ((@SERV_NAME != '') and (@DB_NAME != ''))
        begin
            set @strSQL = 'select @Ct=count(*)from ' + ltrim(rtrim(@SERV_NAME)) + '.' + ltrim(rtrim(@DB_NAME)) + '.dbo.sysobjects where name = ''images'''
            execute sp_executeSQL @strSQL, N'@Ct int output', @Ct = @tmp_rc OUTPUT


After this, your @tmp_rc variable should have the count.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
calphaAuthor Commented:
Thanks Guys.  I felt bad just giving it to one since both of you had the right answer.

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.
0
Anthony PerkinsCommented:
That is very fair.  I appreciate the explanation all the same, not many do that here.

Thanks,
Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.