Dynamic stored proc problem - 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 2005 Forums

Transact-SQL (2005)

Dynamic stored proc problem

Author

Topic

ramsesiitr
Starting Member

22 Posts

Posted - 2008-09-01 : 06:33:15

Hi all,

I have a problem with dynamic stored procedure. The code I have been trying is like that:


declare @records int  
declare @query nvarchar(100)
set @query='SELECT '+@records+' =COUNT(*) FROM dbo.person WHERE  id=''066738'''

The error says "Conversion failed when converting the varchar value 'SELECT ' to data type int."

I dont know what I missed. Is that possible to assign the value returned from Count method?

I ll be glad if you help me.
Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-01 : 06:37:02

why are you using Dynamic SQL here ?

You don't need Dynamic SQL here


select @records = count(*) from dbo.person
where id = '066738'

KH
[spoiler]Time is always against us[/spoiler]

ramsesiitr
Starting Member

22 Posts

Posted - 2008-09-01 : 06:41:44

I know, I dont need dynamic sql here. But imagine that you have lots of conditions ( like, user may want to search by an id, a name or surname). According to the parameters passed I perform a search,therefore I need a dynamic sql and I need the total records of the search result.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-01 : 07:14:58

http://www.sqlservercentral.com/links/279595/61172

ramsesiitr
Starting Member

22 Posts

Posted - 2008-09-01 : 07:24:50

Thanks sunil.
This is a good explanation of dynamic sql query. I can do everything written in the article. But Count() function seems to be an exception. Still cant make it work. I need to count the records returned from the dynamic query.

Thanks again...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 07:33:52

Read about sp_executesql in sql server help file

Madhivanan

Failing to plan is Planning to fail

ramsesiitr
Starting Member

22 Posts

Posted - 2008-09-01 : 08:51:40

Yep, found the answer.

The correct code is:


declare @records int  
declare @query nvarchar(100)
set @query='SELECT @records =COUNT(*) FROM dbo.person WHERE  id=''066738'''

execute sp_executesql @query,N'@records int OUT',@records OUT
Select @records

Thanks everybody who guided and [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45193[/url]

and thanks madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources