problem with variables - 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 2000 Forums

Transact-SQL (2000)

problem with variables

Author

Topic

nsrao_1975
Starting Member

13 Posts

Posted - 2004-02-26 : 01:59:55

hi,

can any guru help me out here..

set @var1='inst1' -- value i will be knowing only in runtime
set @var1='select '+@var1+' from table_1 where <cond>'
here i want to set the output value of the @var1 to @var2

i can't give with case condition becos inst[n] where n can be upto any value..

thanks inavance

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-26 : 02:22:40

so whats the problem?
may be i have not understood u completely can u elaborate further?

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

nsrao_1975
Starting Member

13 Posts

Posted - 2004-02-26 : 02:40:38

hi,

the problem is like this

@var1 contains a dynamic sql statement which will return an integer value,on execution

that returned value i want to assign to @var2 the second variable.

@var1= "select col_1 from table_1 where <cond>"

when i gave

set @var2 =execute(@var1) it is giving an error..

i hope u got it..

thanks

ns rao

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-26 : 03:31:10

quote:
Originally posted by nsrao_1975

hi,

the problem is like this

@var1 contains a dynamic sql statement which will return an integer value,on execution

that returned value i want to assign to @var2 the second variable.

@var1= "select col_1 from table_1 where <cond>"

when i gave

set @var2 =execute(@var1) it is giving an error..

i hope u got it..

thanks

ns rao

Hi,
R u trying to do something like this:

DECLARE @strSql nvarchar(2000)
DECLARE @total int
SET @strSql = 'select @total =count(*) from sometable'
exec sp_executesql @strSql, N'@total int OUTPUT', @total OUTPUT
select @total

regards,
harshal.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

nsrao_1975
Starting Member

13 Posts

Posted - 2004-02-26 : 03:49:06

hi,

thanks alot for ur reply.. i think i was not clear to u..

set quoted_identifier off
DECLARE @strSql nvarchar(2000)
DECLARE @total int
DECLARE @var1 nvarchar(10)
SET @var1='INST1' -- column name i will be getting dynamically
-- at run time
SET @strSql = "select @total =(select @var1) from tempag"
exec sp_executesql @strSql, N'@total int OUTPUT', @total OUTPUT
select @total

-- this is throwing an error @var1 not declared

pls give me a solution

thanking you

Ns Rao

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-02-26 : 03:53:09

quote:
Originally posted by nsrao_1975

hi,

thanks alot for ur reply.. i think i was not clear to u..

set quoted_identifier off
DECLARE @strSql nvarchar(2000)
DECLARE @total int
DECLARE @var1 nvarchar(10)
SET @var1='INST1' -- column name i will be getting dynamically
-- at run time
SET @strSql = "select @total =(select @var1) from tempag"
exec sp_executesql @strSql, N'@total int OUTPUT', @total OUTPUT
select @total

-- this is throwing an error @var1 not declared

pls give me a solution

thanking you

Ns Rao

set quoted_identifier off
create table #nam (names varchar(100))
insert into #nam values ('harshal')
DECLARE @strSql nvarchar(2000)
DECLARE @total varchar(100)
DECLARE @var1 nvarchar(10)
SET @var1='names' -- column name i will be getting dynamically
-- at run time
SET @strSql = 'select @total ='+@var1+' from #nam'
exec sp_executesql @strSql, N'@total varchar(100) OUTPUT', @total OUTPUT
select @total

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-26 : 03:56:46

quote:
Originally posted by harshal_in

quote:
Originally posted by nsrao_1975

hi,

thanks alot for ur reply.. i think i was not clear to u..

set quoted_identifier off
DECLARE @strSql nvarchar(2000)
DECLARE @total int
DECLARE @var1 nvarchar(10)
SET @var1='INST1' -- column name i will be getting dynamically
-- at run time
SET @strSql = "select @total =(select @var1) from tempag"
exec sp_executesql @strSql, N'@total int OUTPUT', @total OUTPUT
select @total

-- this is throwing an error @var1 not declared

pls give me a solution

thanking you

Ns Rao

set quoted_identifier off
create table #nam (names varchar(100))
insert into #nam values ('harshal')
DECLARE @strSql nvarchar(2000)
DECLARE @total varchar(100)
DECLARE @var1 nvarchar(10)
SET @var1='names' -- column name i will be getting dynamically
-- at run time
SET @strSql = 'select @total ='+@var1+' from #nam'
exec sp_executesql @strSql, N'@total varchar(100) OUTPUT', @total OUTPUT
select @total

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

Yeah , I said something like this on the other thread with the same name

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources