How to Set value from Execute Statement

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)

How to Set value from Execute Statement

Author

Topic

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2002-04-10 : 08:15:25

Hi,

What is wrong on the below statement. iam not able to store in result in variable @cnt

DECLARE @THH VARCHAR(50), @cnt varchar(1)
SET @THH = 'SEKAR'
exec ('DECLARE @CNT=COUNT(1) FROM ' + @THH + '')
PRINT @CNT

Thanks in advance

dsdeming

479 Posts

Posted - 2002-04-10 : 09:28:55

I don't remember where I found this, but it should solve your problem:

DECLARE @iCount AS int,
@nvcCommand As nvarchar( 500 ),
@vcTable as varchar( 30 )

SET @vcTable = 'sysobjects'
SET @nvcCommand = 'Select @iCount = Count(*) FROM ' + @vcTable
EXECUTE sp_ExecuteSql @nvcCommand, N'@iCount int OUT', @iCount OUT
SELECT @iCount AS OutVal

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-10 : 09:40:03

An exec(<string>) executes the <string> in a its own batch, therefor the @CNT variable is out of scope. There are two ways to do what you are trying to do.

First, use sp_executesql. Take a look at Books on Line for the specifics, but . . .


create table sekar ( col1 int )
insert sekar values (1)
insert sekar values (2)
insert sekar values (3)
go
declare
    @tth varchar(50),
    @sql nvarchar(1000),
    @theCount int
set @tth = '<databasename>.<owner>.sekar'
select
    @sql = N'select @cnt = count(47) from ' + @tth
exec master..sp_executesql @sql, N'@cnt INT OUT', @theCount OUT
select @theCount
go
drop table sekar
go

Second, you can run the execute into a temp table . . .


create table sekar ( col1 int )
insert sekar values (1)
insert sekar values (2)
insert sekar values (3)
go

create table #count (
    thecount int
)

declare
    @tth varchar(50),
    @sql varchar(1000)
set @tth = '<databasename>.<owner>.sekar'

select
    @sql = 'select count(47) from ' + @tth

insert #count
exec(@sql)

select thecount from #count
go

drop table sekar
drop table #count
go

Hope that helps.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources