Dynamic SQL

Question

Post reply

Dynamic SQL

ofsouto

SSC Enthusiast

Points: 179
More actions
December 15, 2005 at 5:06 am

#88843

Hi, dear
How can I get a variable value using dynamic SQL?
How can I execute the instruction bellow using dynamic SQL?
declare @var
select @var = count(*) from table_A
Thank you very much
Obede

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

I'm not sure I understand your question.

Your statement above is valid except for a small syntax error. You need to define the parameter's datatype.

declare @var int

select @var = count(*) from serverlist

Is that what you're asking for?

Karl
source control for SQL Server

ofsouto SSC Enthusiast Points: 179 More actions · Answer 2

I want to know how can I execute this instruction on dynamic SQL

Phil Parkin SSC Guru Points: 247208 More actions · Answer 3

You will need to be a bit more specific before we can really help you.

Can you provide a bit more detail about why you need to do this? What are you going to do with the variable after the command has been executed?

Low Rider SSCommitted Points: 1502 More actions · Answer 4

do u mean this...........................?

declare @var as int

declare @sql as varchar(1000)

select @sql = 'declare @var as int select @var = count(*) from table_A select @var'

exec (@sql)

esmith21 SSC Journeyman Points: 87 More actions · Answer 5

I _think_ what you're trying to do execute the count function for a table dynamically....maybe so that you can do it for a list of tables with a cursor or something. If that's what you're trying to accomplish then I'd use sp_executesql with an output param. The following sql will return the number of rows in your table and return the count to the calling process.

DECLARE @sql NVARCHAR(4000), @count INT, @table_name SYSNAME

SELECT @table_name = 'sysobjects'

select @sql = 'select @count = count(*) from ' + @table_name

EXEC sp_executesql @sql,

N'@count INT OUTPUT',

@count OUTPUT

PRINT @count

If all you're trying to do is run the query dynamically then you can do the following, but I doubt this is what you're asking:

EXEC('DECLARE @count INT

select @count = count(*) from sysobjects

SELECT @count')