selecting result of a dynamic sql query into a variable
-
im executing this as dynamic sql
Select
@SQLCommand ='Select count(*) from '+ ltrim(rtrim(@TableName))
i want to select this returned number into a variable so i tried this
Select
@SQLCommand ='select @NumRows = (Select count(*) from '+ ltrim(rtrim(@TableName)) +')'
it tells me i have to declare the variable @NumRows, even though i have declared it at the top of the procedure. i just get an error when i try this
select @NumRows = (exec @SQLCommand)
how do i do this??
-
Actually the EXEC command is executed in a diff address space of the current so... your var/s are not visible.
What you have to do is to declare the var inside the EXEC command:
declare @TableName varchar(256)
declare @SQLCommand varchar(256)
select @TableName='items '
select @SQLCommand = 'Declare @NumRows int
select @NumRows = count(*) from '+ ltrim(rtrim(@TableName))+
' select @NumRows'
exec(@SQLCommand)
------------
When you 've got a hammer, everything starts to look like a nail... -
ive tried that with this code
declare
@tableName varchar (50)
Declare
@command varchar(50)
select
@tableName = 'customer'
select
@command = 'Declare @NumRows int
select @numRows = (select count(*) from '
+ ltrim(rtrim(@tableName)) + ') select @NumRows'
exec
(@command)
but i keep getting an error saying incorrect syntax near select. i cannot see any incorrect syntax
-
The problem is not actually in your code but in the size of the @Command which is too short to keep the whole string.... try
Declare @command varchar(256)
...and everything will work...
------------
When you 've got a hammer, everything starts to look like a nail... -
one more thing ive just realised. ok, i can get the number of rows in each of those tables, but i when i try to use that number when inserting into a table, i simply get a null. the @numRows variable doesnt seem to have any value outside of the dynamic sql statement. i need to get the number of rows in a table into the variable, then further down , insert the variable into a table, like so
set
@SQLCommand ='Declare @NumRows int
select @NumRows = count(*) from '
+ ltrim(rtrim(@TableName))
--+ ' select @NumRows'
exec
( @SQLCommand)
insert
into temp
values
(@tableName,@numRows)
this is the result set i get
Table Name RowCount
tblCurrentQuarter NULL
BreakdownDeductionsOnceOFF NULL
tblCustBreakdown NULL
BreakdownDeductionsOnceOFFq2 NULL
-
Seems that you dont read my replies....
------------
When you 've got a hammer, everything starts to look like a nail... -
i did i did!! i am getting a result back for @numRows, but thats in the context of the dynamic sql, and i cant reference that outside of the dynamic sql, in my sproc.
-
Try sp_executesql
This question gets asked at least once a month usually more, I'm surprised you couldn't do a simple search to find the answer
Use Pubs
declare @SQLCommand nvarchar(4000)
declare @TableName varchar(50)
declare @myoutputRowcount int
set @TableName = 'Authors'
Select @SQLCommand ='Select @NumRows = count(*) from '+ ltrim(rtrim(@TableName))
exec sp_ExecuteSql @SQLCommand, N'@NumRows int OUTPUT', @NumRows = @myoutputRowcount OUTPUT
select @myoutputRowcount
-
thank you. that worked a treat. i would have searched, but i didnt know what to search for.
-
sp_executesql is what u r looking 4...
Actually (by reading the SBO) u will see the following:
"Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server."
------------
When you 've got a hammer, everything starts to look like a nail...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply