Solved

MSSQL---Using Execute & Dynamic SQL Statements to return a COUNT to a variable

Posted on 2003-11-21
4
2,016 Views
Last Modified: 2011-04-14
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
0
Comment
Question by:calpha
  • 2
4 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
Comment Utility
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
Comment Utility
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
 

Author Comment

by:calpha
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
That is very fair.  I appreciate the explanation all the same, not many do that here.

Thanks,
Anthony
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now