• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1309
  • Last Modified:

SQL Variable - SELECT COUNT(*)

Hi,

create table dbo.MyTable
(id int,
name varchar(20)
)

insert into dbo.MyTable(id,name)
values(0, 'John')

The following works (gives me 0):

declare @TableName varchar(20), @SomeInt as int
set @TableName = 'dbo.MyTable'

declare @SQL varchar(1000)
set @SQL = 'SELECT count(*) FROM ' + @TableName
 exec(@SQL)

But this doesn't work:

declare @TableName varchar(20), @SomeInt as int
set @TableName = 'dbo.GunniTemp'

declare @SQL varchar(1000)
set @SQL = 'SELECT count(*) FROM ' + @TableName
 exec(@SQL)
set @SomeInt = exec(@SQL)

Anybody know why?

Thank you


0
Endelm
Asked:
Endelm
  • 3
  • 3
1 Solution
 
EndelmAuthor Commented:
This line:
set @integer = exec(@SQL)

gives me:
"Incorrect syntax near the keyword 'exec'."
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @SQL nvarchar(1000)
set @SQL = 'SELECT @SomeInt = count(*) FROM ' + @TableName
 
EXEC sp_ExecuteSQL @Sql,N'@SomeInt int out' , @SomeInt output
SELECT @SomeInt
0
 
EndelmAuthor Commented:
Hi,

Im still not sure what you mean?

What does the sp_ExecuteSQL do?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
twoboatsCommented:
sp_execute executes a string containing a sql command

Is there a reason for not just doing

SELECT @SomeInt = count(*) FROM mytable

I'm guessing there is, but from a performance point of view, the more dynamic sql you have, the bigger the impact on performance
0
 
EndelmAuthor Commented:
Wow, this worked. Thank you :)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
If you checks books online, you can see the two methods of executing the Dynamic Queries
1. Using the Exec statement
2. Using sp_ExecuteSQL
and the second option is Recommended because
 1. It allows you to pass parameters
 2. will store the execution plan

Now if you need to continue to work with exec part , you can work like this


declare @TableName varchar(20), @SomeInt as int
set @TableName = 'dbo.GunniTemp'

declare @SQL nvarchar(1000)
set @SQL = 'SELECT count(*) FROM ' + @TableName

CREATE TABLE #temp(i INT)
INSERT INTO #temp
EXEC(@Sql)
SELECT * FROM #Temp


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Wow, this worked. Thank you :)

 Can i know , which one ?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now