Dynamic SQL
-
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
-
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?
-
I want to know how can I execute this instruction on dynamic SQL
-
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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)
-
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')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply