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


EndelmAsked:
Who is Participating?
 
twoboatsConnect With a Mentor Commented:
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:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
EndelmAuthor Commented:
Hi,

Im still not sure what you mean?

What does the sp_ExecuteSQL do?
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.