Assigning result if Exec (query) to a Variable

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)

Assigning result if Exec (query) to a Variable

Author

Topic

namrnaahmad
Starting Member

4 Posts

Posted - 2009-10-16 : 03:12:01

Dearest All,
I am writing an query in TSQL and finding the following problem
1. First I tried this method :
DECLARE @SQLStatement varchar(500)
DECLARE @uName varchar(25)
DECLARE @TblName varchar(25)
Declare IdNumber int

Set @TblName=’SP001001’
Set @uName=’Namran Ahmed’
Set @SQLStatement='Select Top(1) idNo from ' + @TblName + ' where Name=' + @uName +'

? Set IdNumber =Exec(@SQLStatement)

( ? I am getting Error
[Msg 156, Level 15, State 1, Procedure EmpReport, Line 91 Incorrect syntax near the keyword 'Exec'.]
)

Q. How can I assign the result of my query in a variable?

2. Then I tried This method

DECLARE @SQLStatement varchar(500)
DECLARE @uName varchar(25)
DECLARE @TblName varchar(25)
Declare IdNumber int

Set @TblName=’SP001001’
Set @uName=’Namran Ahmed’
SELECT IdNumber =(Select Top(1) idNo from + @TblName + where Name= + @uName )

( ? I am getting this Error if I assing database table name in Variable.
[Msg 1087, Level 15, State 2, Procedure EmpReport, Line 80
Must declare the table variable "@TblName".]

It works in this case if I hard code the database Table name but I need to change table name according to my need e.g.:
SELECT IdNumber =(Select Top(1) idNo from SP001001 where Name= + @uName )

I need that After making store porcedure I can change any table I want.by passing table name to the porcedure to change table name for getting required result of that table .

Q. How can I change the Database table name in a query through characher variable so I can change table name I want by passing Variable value as name to a store procedure?

Kindly guide me.
Regards
Namran Ahmed

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-16 : 06:13:58

DECLARE @SQLStatement nvarchar(500)
DECLARE @uName varchar(25)
DECLARE @TblName varchar(25)
Declare @IdNumber int
Declare @VarDefinition nvarchar(200)

Set @TblName='SP001001'
Set @uName='Namran Ahmed'

Set @SQLStatement='Select Top(1) @IdNumber= idNo from '+@TblName+' where Name= '''+ @uName + ''''
Set @VarDefinition=N'@TblName varchar(25),@uName varchar(25),@IdNumber int Output'

EXEC sp_executeSql @SQLStatement,@VarDefinition,@TblName=@TblName,@uName=@uName,@IdNumber=@IdNumber OUTPUT
Select @IdNumber

mkasa
Starting Member

2 Posts

Posted - 2009-10-16 : 08:56:16

One of the method is to store the result of dynamic sql into a temporary table and then generate the result. Comments are in /* */

I am giving you an example stored procedure for that.

CREATE procedure [dbo].[DynamicSP](@TableName varchar(1000))
as
begin
declare @TmpTable table ( mRowId int identity(1,1),mIdProduct bigint)
delcare @query varchar(max)
declare @ids bigint
/* tmpTable is a temporary table here where midProduct is the bigint type column */
select @Query = 'insert into @TmpTable Select Top(1) idNo from ' + @TblName + ' where Name=' + @uName
Exec @Query

/* Your desired result should be in Temporary table */
/* Now query the values */

select @ids= top(1) mIdProduct from @TmpTable

/* ids variable contain your desire value */

End

A.K.A Pakistani Brain

imughal
Posting Yak Master

192 Posts

Posted - 2009-10-16 : 23:42:40

Hi,

getting following error when run/create above procedure. I am using SQL 2005.

Msg 102, Level 15, State 1, Procedure DynamicSP, Line 5
Incorrect syntax near 'delcare'.
Msg 137, Level 15, State 2, Procedure DynamicSP, Line 8
Must declare the scalar variable "@TblName".
Msg 137, Level 15, State 2, Procedure DynamicSP, Line 9
Must declare the scalar variable "@Query".
Msg 156, Level 15, State 1, Procedure DynamicSP, Line 14
Incorrect syntax near the keyword 'top'.

namrnaahmad
Starting Member

4 Posts

Posted - 2009-10-17 : 03:38:24

create Procedure EnergyReport(
-------------------- INPUT Variables ----------------------------------------------------------
@GetFDate varchar(25),
@GetTDate varchar(25),
@TblName VARCHAR(255),
@sConnect varchar(25),
@GetsDevice nvarchar(25),
@GetsID varchar(25)
---------------------------------------------------------------------------------------------------
)
AS
BEGIN
---- Create a variable @SQLStatement-----------------------------------------------------------------------

DECLARE @@FromDate datetime
DECLARE @@ToDate datetime
DECLARE @@TotalDays int
DECLARE @@DaysCount int
DECLARE @@RDate datetime
DECLARE @@R1date varchar(10)
DECLARE @@R2DATE DATETIME
DECLARE @@R3DATE varchar(10)
DECLARE @@addsub int
declare @@iEnergyConsumption bigint
declare @@FinalEnergy bigint
DECLARE @@sSite varchar(8)
DECLARE @SQLStatement1 varchar(max)

DECLARE @SQLStatement2 varchar(max)

Declare @@DisplayDate varchar(10)

DECLARE @TempTblPrev TABLE (PrevEnergyConsumption bigint)

DECLARE @TempTblCurr TABLE (CurrEnergyConsumption bigint)

DECLARE @@Result TABLE
(
sDate Varchar(10),
EnergyConsumption bigint,
Energy int
)

-------------------------ASSUMING -------------------------------------------------------------------------
----
----SET @GetFDate='01/10/2009'
----SET @GetTDate='15/10/2009'
----set @TblName='SP001001'
----SET @sConnect='SP001001'
----
----SET @GetsDevice ='SPMM-X'
----SET @GetsID ='00'

-----------------------------------------------------------------------------------------------------------
SET @@FromDate=(SELECT CONVERT(datetime, @GetFDate, 103) AS [DD/MM/YYYY])
SET @@ToDate=(SELECT CONVERT(datetime, @GetTDate, 103) AS [DD/MM/YYYY])
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
---- SELECT @SQLStatement = "SELECT * FROM " +
---- @TableName + "WHERE FirstName = '"
---- + @FirstName + "' AND LastName = '"
---- + @LastName + "'"

SET @@addsub = -1
SET @@TotalDays = DATEDIFF(dd,@@FromDate,@@ToDate)
SET @@DaysCount = 0
-- truncate table @TblName
WHILE @@TotalDays >= @@DaysCount
BEGIN
SET @@RDate = (SELECT(@@FromDate + @@DaysCount) )
SET @@R1date= (SELECT CONVERT(VARCHAR(10), @@RDate, 112) AS [DD/MM/YYYY])
SET @@DisplayDate =(SELECT CONVERT(VARCHAR(10), @@RDate, 103) AS [DD/MM/YYYY])
set @@R2DATE = dateadd(day,1*@@addsub,@@Rdate)
SET @@R3DATE = (SELECT CONVERT(VARCHAR(10), @@R2date , 112) AS [DD/MM/YYYY])

Declare @MyEnergyI bigint
Declare @MyEnergyII bigint
SET @MyEnergyI = 0
SET @MyEnergyII = 0
----
SELECT @SQLStatement1 ='Insert into @TempTblPrev ([PrevEnergyConsumption]) VALUES (SELECT SUM(SUM1) as PrevEnergyConsumption FROM SELECT Top (3) cast(F7 as int) as SUM1 FROM '+ @TblName + ' where sConnect=' + @sConnect + ' and Sdevice= ' + @GetsDevice +' and sDeviceId= '+ @GetsID +' and ddate=' + @@R3date + ' order by ntime desc) as Ps)'

---- SELECT @SQLStatement1 ='SELECT SUM(sum1) as Energy FROM (SELECT Top (3) cast(F7 as int) as Sum1 FROM ' +
---- @TblName + ' where sConnect=' +
---- @sConnect + ' and Sdevice=' +
---- @GetsDevice +' and sDeviceId='+
---- @GetsID +' and ddate=' +
---- @@R3date + ' order by ntime desc) as Ps'

-- SELECT SUM(sum1) as Energy FROM (SELECT Top (3) cast(F7 as int) as Sum1 FROM SP001001 where sconnect='SP001001' and Sdevice='SPMM-X' and sDeviceId='00' and ddate=20091010 and F7<>'' order by ntime desc) as Ps
-- SET @MyEnergyI= Execute(@SQLStatement)
-- SELECT @MyEnergyI=(SELECT SUM(sum1) as Energy FROM (SELECT Top (3) cast(F7 as int) as Sum1 FROM @TblName where sconnect=@sConnect and Sdevice=@GetsDevice and sDeviceId=@GetsID and ddate=@@R3date and F7<>'' order by ntime desc) as Ps)

Exec (@SQLStatement1)
--
SELECT @SQLStatement2 ='Insert into @TempTblCurr ([CurrEnergyConsumption]) VALUES ( SELECT SUM(SUM1) as Energy FROM (SELECT Top (3) cast(F7 as int) as SUM1 FROM ' +
@TblName + ' where sConnect=' +
@sConnect + ' and Sdevice= ' +
@GetsDevice +' and sDeviceId= '+
@GetsID +' and ddate=' +
@@R1date + ' order by ntime desc) as Ps)'
-- -- SELECT SUM(sum1) as Energy FROM (SELECT Top (3) cast(F7 as int) as Sum1 FROM SP001001 where sconnect='SP001001' and Sdevice='SPMM-X' and sDeviceId='00' and ddate=20091010 and F7<>'' order by ntime desc) as Ps
-- ---- SELECT @MyEnergyII=(SELECT SUM(sum1) as Energy FROM (SELECT Top (3) cast(F7 as int) as Sum1 FROM @TblName where sconnect=@sConnect and Sdevice=@GetsDevice and sDeviceId=@GetsID and ddate=@@R1date and F7<>'' order by ntime desc) as Ps)
--
--
Exec(@SQLStatement2)

select @MyEnergyI = PrevEnergyConsumption from @TempTblPrev
select @MyEnergyII = CurrEnergyConsumption from @TempTblCurr
SET @@iEnergyConsumption=@MyEnergyII - @MyEnergyI
IF @@iEnergyConsumption >0
BEGIN

-- PRINT 'ELECTRICITY CONSUMPTION / DAY :' + cast(@iEnergyConsumption as nvarchar(15))
-- PRINT 'YOUR ENERGY = ' + cast(@MyEnergyII as nvarchar(15))
SET @@FinalEnergy=@MyEnergyII
END
ELSE
BEGIN
declare @@iEnergy int

set @@iEnergy= @MyEnergyI-@@iEnergyConsumption

-- PRINT 'ELECTRICITY CONSUMPTION / DAY :'+ cast(@iEnergyConsumption as nvarchar(15))
-- PRINT 'YOUR ENERGY = ' + cast(@iEnergy as nvarchar(15))
SET @@FinalEnergy=@@iEnergy
END

INSERT INTO @@Result VALUES ( @@DisplayDate,@@iEnergyConsumption, @@FinalEnergy)
SET @@DaysCount = @@DaysCount + 1
Delete from @TempTblPrev
Delete from @TempTblCurr
--Select @R1date as sDate,@iEnergyConsumption as EnergyConsumption,@FinalEnergy as Energy
END
select * from @@Result

END

mkasa
Starting Member

2 Posts

Posted - 2009-10-17 : 03:49:12

Dear NamranAhmed ,
i am sending you the following article from sqlteam regarding your problem. Hope so it will helps you to achieve your target.
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2
see the last example specially.

A.K.A Pakistani Brain

namrnaahmad
Starting Member

4 Posts

Posted - 2009-10-17 : 07:03:15

Thank You Kashif Bhi I got It
It Works ..

Namran Ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources