Problem with the Dynamic Query

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

Transact-SQL (2000)

Problem with the Dynamic Query

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-27 : 02:21:57

I want to have a sp which handles the dynamic query hoping that it won't affect any performance about what Iam doing.

Create Procedure Common_count_info_s
@emp_id INT
@TableName VarChar(100)
AS

Declare @SQL VarChar(7000)

SELECT @SQL = 'SELECT COUNT(*) FROM '
SELECT @SQL = @SQL + @TableName
Exec ( @SQL)

I want to process the below shown query...

SELECT COUNT(*) FROM @TableName
emp_id=@emp_id AND
process_flag='N' AND
indicator IN ('A','C')

There is some issue when I have ' with the IN function and all...Please help me create the Sp mentioned above

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-27 : 03:37:35

Why do you want such SP?
It's not a good design choice. How you can be sure that any table which you pass will have emp_id, process_flag & indicator columns?

Also with dynamic sql, you are inviting risk of SQL Injection (security threat). Performance won't be good either since the plan won't be reused. It is in a way similar to passing an ad-hoc in the front-end.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 09:01:25

Select @sql=
'SELECT COUNT(*) FROM '+@TableName+'
Where emp_id='+@emp_id+' AND
process_flag=''N'' AND
indicator IN (''A'',''C'')'

Also read http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-27 : 21:02:08

The Sp won't be used for application.This is a sp which gets the count from all the table and inserts into one tables with the table name and the record count of the corresonding.

If I don't do this way then I will have to write count(*) query for each table.

Here again How can I assign the
EXEC(@SQL)
DECLARE @record_count int
SET @record_count = EXEC(@SQL)

This give me error

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-27 : 21:56:40

You'll need to use sp_executeSql to do this:
DECLARE @count INT
SET @SQL = N'SELECT @c = COUNT(*) FROM ' + @yourTable + ' WHERE <your filters'
EXEC sp_ExecuteSQL @SQL, N'@c INT OUT', @count OUT

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-27 : 23:02:48

Thanks a lot..Will this have an effect on the perfomance...Otherwise I will have to write the query count for each table
The main thing is the where clause is same in all the cases.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-28 : 12:47:21

For now I don't see a performance in DEV enviornment.But in Prod I don't know...Please help

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-28 : 20:47:14

Will it cause any issue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources