Record Count using 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)

Record Count using Dynamic Query

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-25 : 17:48:01

CREATE PROCEDURE [dbo].[usp_record_counts_i]

@table_name VARCHAR(50),
@to_process INT OUPUT

AS

DECLARE @record_count VARCHAR(1000)

SET @record_count='SELECT COUNT(*) FROM '+' '+@table_name

CREATE TABLE #temp_count
(record_count INT)

INSERT INTO #temp_count
EXEC(@record_count)

SELECT
@to_process= record_count
FROM
#temp_count

DROP TABLE #temp_count

Is this the only way to get the record count if Iam using a dynamic query..Please help me.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-01-25 : 18:14:10

You can also do it this way:

CREATE PROCEDURE [dbo].[usp_record_counts_i]
@table_name VARCHAR(50),
@to_process INT OUTPUT
AS
DECLARE @record_count nvarchar(1000)

SET @record_count = 'SELECT @to_process = COUNT(*) FROM ' + @table_name

EXECUTE sp_executesql @record_count, N'@to_process int OUTPUT', @to_process OUTPUT

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-25 : 18:49:15

Iam getting an error as
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 14
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-25 : 19:01:00

quote:
Originally posted by sqllearner

Iam getting an error as
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 14
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

It would help us to answer you question if you posted the code that is giving you the error.

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-01-25 : 19:18:42

Did you declare @statement as nvarchar? You probably declared it as varchar.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-25 : 19:44:46

It was my mistake when I wrote it I declared it as DECLARE @record_count varchar(1000) instead of
DECLARE @record_count nvarchar(1000).Thanks a lot

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-25 : 19:47:42

One more thing...How does nvarchar gives more advantage in this case

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 19:54:02

sp_executesql expects nvarchar in the first 2 parameters
Note the nvarchar used in nosepicker's code. Highlighted in red

DECLARE @record_count nvarchar(1000)
EXECUTE sp_executesql @record_count, N'@to_process int OUTPUT', @to_process OUTPUT

----------------------------------
'KH'
Happy Chinese New Year

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-01-25 : 19:58:23

Thanks a Lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources