problem with store - 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)

problem with store

Author

Topic

andryi
Starting Member

17 Posts

Posted - 2009-10-02 : 13:32:03

Hi all, I have this problem

ALTER PROCEDURE [dbo].[GridRowCount]
@TotalRegistries INT OUTPUT,
@Table VARCHAR(10)
as
DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT ' + @TotalRegistries + ' = COUNT(*) FROM ' + @Table
EXEC @SQL

in my store I want the number of the rows depending on table as parameter

when I put
EXEC GridRowCount 0,'City'

The sql server says:

Conversion failed when converting the varchar value 'SELECT' to data type int.

Can anybody hel me please??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:34:10

you cant use EXEC here you need to use sp_executesql if you want to return variable values like this

http://msdn.microsoft.com/en-us/library/ms175170.aspx

andryi
Starting Member

17 Posts

Posted - 2009-10-02 : 13:40:40

thank's for the answer, well I'm trying like this

--------------------------------------------------------
ALTER PROCEDURE [dbo].[GridRowCount]
@TotalRegistries INT OUTPUT,
@Table VARCHAR(10)
as
DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT ' + @TotalRegistries + ' = COUNT(*) FROM ' + @Table
EXEC sp_executesql @SQL

---------------------------------------------------------

EXEC [GridRowCount] 0,'mt_Sex'

---------------------------------------------------------

but the error continue, sorry if I not understand you, can you explain me please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 13:51:45

[code]ALTER PROCEDURE [dbo].[GridRowCount]
@TotalRegistries INT OUTPUT,
@Table VARCHAR(10)
as
DECLARE @SQL VARCHAR(100),@Param varchar(100)
SET @Param='@Table varchar(10),@TotalRegistries int OUTPUT'
SET @SQL = 'SELECT @TotalRegistries = COUNT(*) FROM ' + @Table
EXEC sp_executesql @SQL,@Param,@Table=@Table,@TotalRegistries=@TotalRegistries OUTPUT
GO

then execute it like

DECLARE @Ret int
EXEC [GridRowCount] @Ret OUT,'mt_Sex'
SELECT @Ret
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources