Stored Procedure - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-11 : 14:48:45

I am new with SQL Server and have never tried to create/run a stored procedure. What I want to do is to have a stored procedure that counts the records in any table, with any field = any value. I believe that one SP would have to be created for each field data type but.....

Anyway:


Create procedure getCount 
       @Table varchar (255)
       @Field varchar (255)
       @FieldValue varchar(255)

AS 
SELECT COUNT(*) AS Count FROM Table WHERE Field = FieldValue
return Count

This I know doesn't work but I was hoping someone would help me out with it?

Mike B

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-11 : 14:52:57

Mike, do a search for "Dynamic SQL" on the main site or the forums. That is the solution that you need. You need to build up the SQL string that needs to be executed, and then execute it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-11 : 15:28:18

quote:
Originally posted by MichaelP

Mike, do a search for "Dynamic SQL" on the main site or the forums. That is the solution that you need. You need to build up the SQL string that needs to be executed, and then execute it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Everything I read about Dynamic SQL has had both "Performance problems" and "Security Breach" in it :).

Maybe I will just stick to opeining a recordset the normal way using ADO and get either a record count or simply passing the SELECT COUNT(*) in the recordset. Thanks for your reply!

Mike B

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-11 : 20:36:16

You could get Recordset.Recordcount once you get the recordset, or you can make stored procs that return you just the count.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-11 : 21:07:34

Mike,

You can get a quick and dirty rowcount using the system tables

This gives a result for all tables but you can change it to accept a table name....


SELECT SO.Name as Tables, SI.rows as Rows
FROM sysindexes as SI
INNER  JOIN sysobjects SO on SO.id = SI.id AND SO.type = 'U'
WHERE (SI.indid = 0 OR SI.indid = 1) and SO.Name != 'dtproperties'

DavidM

"SQL-3 is an abomination.."

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-11 : 21:15:32

Now there's a good solution!
Can that row count be inaccurate if statistics are not on or something? I forget.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-11 : 22:45:27

Yeah, it can get out of whack, but if you run DBCC UPDATEUSAGE on the table beforehand then it will be accurate.

Be advised that sysindexes shows all rows in the table, without condition.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-12 : 06:55:25

If I'm not mistaken you should be somehat safe security-wise if you remove/replace all single-quotes that any user might enter as any of the fields. And even though I belive a dynamic procedure like this will force a recompile every time it runs, you're not in for a serious load here. Offcourse things should be done in a proper manner, but why the he** go through all that hassle just for a tiny insignificant improvement? But if it were a large(er) procedure with alot of stuff we are in a completely different ballpark. If you decide to go with the dynamic sql it can be done like this:

Create procedure dbo.getCount 
  @Table nvarchar (255)
  @Field nvarchar (255)
  @FieldValue nvarchar(255)
AS 
SET NOCOUNT ON
BEGIN
  DECLARE @SQL nvarchar(300)

  SET @SQL = 'SELECT COUNT(*) FROM ' + @Table + ' WHERE ' + @Field + ' = ''' + @FieldValue + ''''
  EXECUTE sp_executesql @SQL

END

Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-12 : 09:05:04

quote:
Originally posted by Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

That I like a lot, I suck at documenting UGHH!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources