Sql query without cursor - 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

Old Forums

CLOSED - General SQL Server

Sql query without cursor

Author

Topic

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 01:42:13

I am creating a module in my application which will maintain the master tables of my application.
I have a table "tblMain" which has a column "cTName" which has names of master tables used in my

application.

I need to create a sql query which will give me the count of records each master tables (listed in the

cTName column) has. I don't want to use cursors or looping. I would prefer to have a single query which will

give me the output.

I will be doing some manipulation on the basis of record count of master tables obtained.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-08 : 02:06:57

Do you need all the rows returned in a single resultset? There are a couple of ways I can think of to do this, one is using dynamic sql, the other would probably be joining to the system tables. This one uses dynamic SQL:


USE pubs
GO

declare @tables table
(
 tableName varchar(50)
)

insert into @tables values('authors')
insert into @tables values('titles')
insert into @tables values('titleauthor')

declare @str varchar(1000)

set @str = ''

select @str = @str + 'SELECT ''' + tableName + ''' AS TableName, COUNT(*) AS Rows FROM ' + tableName + ' UNION ALL ' from @tables

set @str = left(@str, len(@str)-10)

exec (@str)

go

The table variable and the insert statements are there just to provide the query with sample data. If this works fine, you should put this code in a stored procedure.

OS

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 02:15:27

Thanks mohdowais for reply
But Iam not able to get the table name from the "tblmain" table
and use it dynamically in the sql query.

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 04:01:20

Thanks for reply mohdowais
Instead of writing insert queries, I need to write a query which will be created on the basis of arguments supplied to the procedure.
Like

SET @sqlstr = 'Select cTName from tblMain where ShowTable=' + param1 + ' and CheckSecurity>=' + param2

SET @sqlstr ='Insert into @tables ' + @sqlstr
EXEC (@sqlstr)

But this is not working.
Error is :-
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tables'.
Iam not able to handle this.
Can u help me out.
Thanks in advance.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-08 : 04:07:38

I did mention that the table variable and INSERT statements were there just to provide sample data for the query. Your version will look something like this:


declare @str varchar(1000)

set @str = ''

select @str = @str + 'SELECT ''' + cTName + ''' AS TableName, COUNT(*) AS Rows FROM ' + cTName + ''UNION ALL ' 
  from tblMain WHERE ShowTable = @param1 AND CheckSecurity >= @param2

set @str = left(@str, len(@str)-10)

exec (@str)

Of course if you are using parameters, this code needs to go into a stored procedure and you will need to call the procedure with the appropriate parameters.

OS

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 04:24:03

Thanks for ur reply
I was working with procedures only
This is the procedure I was using : -

CREATE PROCEDURE spMaintTable
@strUserKey integer,
@strFilter varchar(200) = null
AS
DECLARE @tables table
(
tableName varchar(50)
)

DECLARE @sqlstr varchar(1000)
DECLARE @str varchar(1000)
Set @sqlstr = ''
Set @str = ''

SET @sqlstr = 'Select cTName from tblMain

SET @sqlstr =' Insert into @tables ' + @sqlstr
EXEC (@sqlstr)

--Select @str = @str + 'SELECT ''' + tableName + ''' AS TableName, COUNT(*) AS Rows FROM ' + tableName + ' UNION ALL ' from @tables

--Set @str = left(@str, len(@str)-10)

--EXEC (@str)
GO

Error:
Must declare the variable '@tables'.

I have commented the execusion of the last query for now, since the first part itself is not working. Iam sure if the first query executes the second will execute. Let me know where iam wrong.

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 04:45:57

There was error in the previous procedure. This is the corrected one -
CREATE PROCEDURE spMaintTable
@strUserKey integer,
@strFilter varchar(200) = null
AS
DECLARE @tables table
(
tableName varchar(50)
)

DECLARE @sqlstr varchar(1000)
DECLARE @str varchar(1000)
Set @sqlstr = ''
Set @str = ''

SET @sqlstr = 'Select cTName from tblMain where iusercreated ='+ CAST(strUserKey as Varchar)

SET @sqlstr =' Insert into @tables ' + @sqlstr
EXEC (@sqlstr)

--Select @str = @str + 'SELECT ''' + tableName + ''' AS TableName, COUNT(*) AS Rows FROM ' + tableName + ' UNION ALL ' from @tables

--Set @str = left(@str, len(@str)-10)

--EXEC (@str)
GO

Error:
Must declare the variable '@tables'.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-08 : 07:05:30

You don't need to use the table variable at all. Here's what I think your proc should look like


CREATE PROCEDURE spMaintTable
@strUserKey integer,
@strFilter varchar(200) = null
AS
BEGIN

 DECLARE @str varchar(1000)
 SET @str = ''

 SELECT @str = @str + 'SELECT ''' + cTName + ''' AS TableName, COUNT(*) AS Rows FROM ' + cTName + ''UNION ALL ' 
  FROM tblMain WHERE iusercreated ='@strUserKey 

 SET @str = left(@str, len(@str)-10)

 EXEC (@str)

END

Please go through this code carefully and note the changes I have made. You don't need as much dynamic sql as you think.

OS

michael1
Starting Member

28 Posts

Posted - 2004-03-08 : 07:34:01

Thanks for the co-operation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources