Table name as Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table name as Parameter

Author

Topic

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 16:12:08

Hi there... i need to pass a table name in as a parameter. However, I'm having trouble getting the count out of it, as below - I can do it hard-coded no problem:

SELECT @RowCount=COUNT(*) FROM Parameterized_Table_Name

But can't seem to make it work with what I've got going below. Any help much appreciated!

DECLARE @RowCount int, @DataCheckView nvarchar(255), @cmd_Count nvarchar(255), @cmd_All nvarchar(255);

-- Parameterized_Table_Name being set here...
SET @DataCheckView = 'Parameterized_Table_Name';

-- This doesn't work, complaining about the need to Declare @RowCount, even though it is above!
--SET @cmd_Count = 'SELECT @RowCount=COUNT(*) FROM ' + @DataCheckView;

-- This works, except I don't know how to get @RowCount out of it!
SET @cmd_Count = 'SELECT COUNT(*) FROM ' + @DataCheckView;
EXEC (@cmd_Count);

IF @RowCount > 0
BEGIN
-- Do some stuff with @cmd_All
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 16:14:06

This is a very bad idea. Why do you need to do this dynamically?

Tara

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 16:20:44

They will actually be views -

I'm working on some data integrity checks (dealing with un-normalizeable data - legacy system extracts).

So, I want to create a stored procedure that essentially executes a given view (the parameterized table name), and if it returns any records, e-mail the appropriate people advising them of the issue.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 16:24:02

Here you go:

DECLARE @SQL nvarchar(4000), @objName sysname, @Count int

SET @objName = 'SomeViewName'

SET @SQL = 'SELECT COUNT(*) FROM ' + @objName
EXEC @Count = sp_executesql @SQL

PRINT @Count

Tara

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 16:25:15

quote:
Originally posted by tduggan

Here you go:

DECLARE @SQL nvarchar(4000), @objName sysname, @Count int

SET @objName = 'SomeViewName'

SET @SQL = 'SELECT COUNT(*) FROM ' + @objName
EXEC @Count = sp_executesql @SQL

PRINT @Count

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 16:29:48

Did you have a question?

Tara

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 16:32:51

Well, I was intending to say, "Thanks - knew it had to be simple!", but forgot my uname/password, and it re-set to the quote. javascript:insertsmilie('

')
Blush

Anyhow - I tried it, but it seems to halt execution and not continue to the IF statement - I tried a "GO", but then it griped again about @Count not being declared. Any ideas?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 16:37:03

You can't use GOs as that terminates the batch so you've started a new session where those variables don't exist as they are now out of scope.

Could you post the code that you are using so that I can try it on my machine?

Tara

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 16:39:50

Still running this in the query analyzer, if that makes a difference...(?)

DECLARE @SQL nvarchar(4000), @objName sysname, @Count int

SET @objName = 'the_name_of_my_view'

SET @SQL = 'SELECT COUNT(*) FROM ' + @objName
EXEC @Count = sp_executesql @SQL

IF @Count > 0
BEGIN
EXEC Master.dbo.xp_sendmail
@recipients = 'me@myaddress.com',
@subject = 'SQL Server Report',
@message = 'Your data is whack'
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 16:50:08

I had a bug. Here you go:


DECLARE @SQL nvarchar(4000), @objName sysname, @Count int

SET @objName = 'sysobjects'

SET @SQL = 'SELECT @Count = COUNT(*) FROM ' + @objName
EXEC sp_executesql @SQL, N'@Count int OUTPUT', @Count OUTPUT

IF @Count > 0
BEGIN
	EXEC Master.dbo.xp_sendmail 
		@recipients = 'me@myaddress.com', 
		@subject = 'SQL Server Report',
		@message = 'Your data is whack'
END

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q262499

Tara

dforester
Starting Member

6 Posts

Posted - 2005-08-03 : 17:02:14

Works like a charm - Thanks, Tara!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-04 : 08:55:46

You didn't test it first?

d'oh

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-04 : 09:28:29

Tara had a bug?

EVERYTHING I KNOW IS A LIE!!!!!!

Change your code to email me if any horsemen are detected riding across the sky, as the Apocalypse is surely upon us!

Aj

--What happens when social workers and programmers collide:
Social Worker: "They aren't an Identified Resource Family, they are an IR Family."
Me: "What does IR stand for?"
Social Worker: "Identified Resource."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources