Out put of Select Statement Stored in a variable to another Variab

Hi Guys,
I need a solution in SQL Server 2000. I wanted to store the output of a
select query into a variable. The Select Statement is stored in a variable
.
the sample is given below.
declare @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec(@.Tmp1)
print @.Tmp2
Do any one have the idea.
Thanks in advance
Regds
Kag"Kag" <Kag@.discussions.microsoft.com> wrote in message
news:304B2B5C-AE55-491A-9D26-88F205861757@.microsoft.com...
> Hi Guys,
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
>
You will need to use a Temp table. The variables are only valid within the
scope in which they are defined. Temp tables are part of your session.
DECLARE @.tmp varchar(100),
@.tmp1 varchar(100)
CREATE TABLE #ReturnValue (tmp2 varchar(15) NULL)
SET @.tmp = 'columntitle FROM bworksheetdtl WHERE Worksheetcode=1 AND
serialno = 5'
SET @.tmp1 = 'INSERT #ReturnValue (tmp2) SELECT ' + @.tmp
EXECUTE (@.tmp1)
SELECT tmp2 FROM #ReturnValue
DROP TABLE #ReturnValue
Rick Sawtell
MCT, MCSD, MCDBA|||The @.Temp2 variable does not exist (is not declared) inside the context of
your EXECUTE statement.
Take a look at sp_executesql in Books Online. It accepts parameters and
really simplifies execution of dynamic SQL.
On the other hand, if you post DDL and sample data and specify what you're
actually trying to achieve, we can help you find a more efficient solution.
ML|||Hi Kag,
Would you like to try this?
DECLARE @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
SELECT @.tmp2 = columntitle FROM bworksheetdtl WHERE Worksheetcode = 1 and
serialno = 5
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Hi Kag,
This may be what you want:
Declare @.tmp varchar(15)
SELECT @.temp = columntitle FROM bworksheetdtl WHERE Worksheetcode=1 and
serialno=5
PRINT @.tmp
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100), columntitle from bworksheetdtl where Worksheetcode=1
and
> serialno=5
> @.Tmp2 Varchar(15)
> set @.Tmp = ''
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Pl try code below
declare @.Tmp nVarchar(100), -- varchar changed to nvarchar
@.Tmp1 nVarchar(100), -- varchar changed to nvarchar
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec sp_executesql @.Tmp1, N'@.Tmp2 Varchar(15) output', @.Tmp2 output -- alter
ed
print @.Tmp2
Rakesh
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Thanks Rakesh and ML. I tried the solution you have given with
sp_executesql and it was the one i was searching for. Special thanks to
Rakesh for the exact example. Thanks again guys..
Kag
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kagsql