stored procedure using tablename as a variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure using tablename as a variable

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-25 : 14:24:44

hi there im trying to create a sproc with the code below. however i am having trouble using the table name as a variable. can anyone help me with this i do not know what im doing wrong.

create procedure usp_test
@tablename varchar (20)

AS
SELECT
so.name AS [TABLENAME],
MAX(si.rows) AS [ROWCOUNT]
FROM
sysobjects AS so INNER JOIN sysindexes AS si ON
(SO.ID=SI.id)
WHERE
so.xtype = 'U'
GROUP BY
so.name
ORDER BY
[ROWCOUNT]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 14:25:58

You have @tablename as an input parameter, but you aren't using it anywhere in your code. Could you show us the code that is failing?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-25 : 15:07:29

quote:
Originally posted by tkizer

You have @tablename as an input parameter, but you aren't using it anywhere in your code. Could you show us the code that is failing?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

that is the code the variable i first used was @name i then changed it. im rather confused

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-25 : 15:12:11

you ARE NOT using the variable @tablename anywhere in your query!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:14:33

There is nothing wrong with the stored procedure that you posted, so I don't see how we can help you fix something that isn't broken. Perhaps you haven't explained your problem clearly yet.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-25 : 15:21:20

hi there wat i want to do is use that sproc. so when i use
execute usp_test
@name = table a

i want to just display table a and a row count

however i cant seem to get that working. Because i keep getting all the table within that database.

Is that more clearer?? plz help me !!!!!!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:23:59

It still isn't clear to me. Please show us exactly what output the stored procedure should return.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:25:18

Or perhaps you just want:

WHERE so.xtype = 'U' AND so.name = @tablename

Be warned though that the row count is going to be inaccurate. The only way to get a true row count is to use count(*).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-25 : 15:32:12

so for example i use the sproc.

lets say i change the variable name to tablexy i should be able to view the table name and row count for that particular table. however when i execute the sproc i cnt. can you please clarify why and where im going wrong.

My variable needs to be a table name!!!

so when i use the sproc and enter any table name next to the variable it should work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:33:36

And did you read my last post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-25 : 15:41:56

thanks very much !!! i cnt believe i missed that out. im new to sql and i am currently training to be a sql developer believe it or not. thank you very much once again.

one last point would i input execute dbo.usp_test if i wanted to use the sproc in another database ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:44:20

To access an object not in the current database, you must use the three part naming convertion:
DatabaseName.ObjectOwner.ObjectName

You need to understand that your stored procedure is going to return incorrect information. Check out DBCC UPDATEUSAGE in BOL for more details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources