Variable in Dynamic SQL

Question

Post reply

Variable in Dynamic SQL

Jagan Kumar

SSC Eights!

Points: 805
More actions
August 19, 2004 at 2:15 am

#102164

Hi All,

I have stored procedure.

Create Procedure (@Table as Varchar(20)) As
Begin
       Declare @strSQL as varchar(100)
       Declare @Out as Integer
       Set @strSQL='Select @Out = Count(*) from ' + @Table
      Exec(@strSQL)
End
Here I am passing Table Name as arguement for the SP. I should get the count(*) in the Table.
But I am getting the Error
"Must declare the variable '@Out'."
How can I solve this problem?
How can I get the variable value @Out?
Is it possible to assign a variable like this in Dynamic SQL?
Please HELP
Jag

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

i'd use

set nocount on

Declare @strSQL as varchar(100)

Declare @Out as Integer

set @strSQL='Set @out=(select Count(*) from ' + @Table+')'

exec sp_executesql @strsql

select @out

MVDBA

Jagan Kumar SSC Eights! Points: 805 More actions · Answer 2

Michael,

But I am getting the same problem

Server: Msg 137, Level 15, State 1, Line 1

Must declare the variable '@out'.

-----------

NULL

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

oh yes, i just realised - @out is out of scope in the new sql execution -

i've just toyed with

set @out=(exec sp_executesql @strsql) but i'm struggling here

i think yo may have to dump to a temp table and then read

MVDBA

Cristian Lupascu SSC Rookie Points: 49 More actions · Answer 4

--I think this might solve your problem (you no longer need the @Out variable):

Create Procedure RecCount (@Table as Varchar(20)) As

Begin

Declare @strSQL as varchar(100)

Declare @Out as int

Set @strSQL='Select Count(*) from ' + @Table

Exec(@strSQL)

End

------Hope this helps

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 5

If you require the number of rows at the output from the proc then try this:

create Procedure dbo.RecCount (@Table as nvarchar(100)) As

Begin DECLARE @cntTotal varchar(10) DECLARE @sql nvarchar(400)
set @sql = N'select @cnt=count(*) from ' + @Table exec sp_executesql @sql, N' @cnt varchar(10) output', @cnt=@cntTotal output select @cntTotal End

u2kota SSChasing Mays Points: 601 More actions · Answer 6

Hi Jagan, you can either take Grasshopper's route or implement the following code to resolve the issue....

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

Declare @strSQL as varchar(100)

Set @strSQL='Declare @Out as Integer Select @Out = Count(*) from ' + @Table + ' Print @out'

Exec(@strSQL)

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

Cheers,

Phani

Igor Raytsin Ten Centuries Points: 1341 More actions · Answer 7

Or you could try this:

Create Procedure RecCount (@Table sysname ) As

SELECT si.rowcnt

FROM sysindexes si

INNER JOIN sysobjects so on si.id=so.id AND si.indid IN (1,0)

WHERE so.name = @Table

Igor