1: Ordinary SQL statements can be executed with exec
Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- Note that you must add N before the string
2: When using field names, table names, database names, etc. as variables, dynamic SQL must be used
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- Error, no error is prompted, but the result is a fixed value FiledName, which is not what you want.
exec('select ' + @fname + ' from tableName') -- note that a space is added to the side of the single quotation mark before and after the plus sign
Of course, you can also change the string to the form of a variable
declare @fname varchar(20)
set @fname = 'FiledName' --Set the field name
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
exec(@s) -- success
exec sp_executesql @s -- This sentence will report an error
declare @s Nvarchar(1000) -- note that it is changed to nvarchar(1000) here.
set @s = 'select ' + @fname + ' from tableName'
exec(@s) -- success
exec sp_executesql @s -- this sentence is correct
3. Output parameters
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--how do I put the result of exec execution into a variable?
declare @num int, @sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
1: Ordinary SQL statements can be executed with Exec Example: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- Note that you must add N before the string
2: When using field names, table names, database names, etc. as variables, dynamic SQL must be used
Error: declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- Error, no error is prompted, but the result is a fixed value FiledName, which is not what you want.
Correct: Exec('select ' + @fname + ' from tableName') -- notice the addition of a space to the side of the single quote before and after the plus sign
Of course, you can also change the string to the form of a variable
declare @fname varchar(20)
set @fname = 'FiledName' --Set the field name
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- Success
exec sp_executesql @s -- This sentence will report an error
--Note: @s parameter must be of ntext or nchar or nvarchar type, declare @s varchar(1000) must be changed to declare @s nvarchar(1000)
As follows:
declare @s Nvarchar(1000) -- note that it is changed to nvarchar(1000) here.
set @fname = 'FiledName' --Set the field name
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- Success
exec sp_executesql @s -- this sentence is correct
3. Input or output parameters
(1) Input parameters:
declare @QueryString nvarchar(1000) -- dynamic query statement variable (note: must be ntext or nchar nvarchar type, not varchar type)
declare @paramstring nvarchar(200) - set the string of parameters in the dynamic statement (note: must be ntext or nchar nvarchar type, not varchar type)
declare @input_id int - defines the value of the parameters to be passed into the dynamic statement
set @QueryString='select * from tablename where id=@id' --id is the field name, @id is the parameter to be passed
set @paramstring='@id int' -- Sets the string that defines the parameters in the dynamic statement
set @input_id =1 -- Set the value of the parameter to be passed in the dynamic statement to 1
exec sp_executesql @querystring,@paramstring,@id=@input_id
If there are multiple parameters:
declare @QueryString nvarchar(1000) -- dynamic query statement variable (note: must be ntext or nchar nvarchar type, not varchar type)
declare @paramstring nvarchar(200) - set the string of parameters in the dynamic statement (note: must be ntext or nchar nvarchar type, not varchar type)
declare @input_id int - defines the value of the parameter to be passed into the dynamic statement, parameter 1
declare @input_name varchar(20) - defines the value of the argument to be passed into the dynamic statement, parameter 2
set @QueryString='select * from tablename where id=@id and name=@name' --id and name are field names, and @id and @name are the parameters to be passed
set @paramstring='@id int,@name varchar(20)' -- Set the string of the definition of the parameters in the dynamic statement, separated by ","
set @input_id =1 -- Set the value of the parameter to be passed in the dynamic statement to 1
set @input_name='Zhang San' --Set the value of the parameter to be passed in the dynamic statement to 'Zhang San'
exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name -- Note the order of the arguments
(2) Output parameters
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--how do I put the result of exec execution into a variable?
declare @QueryString nvarchar(1000) -- dynamic query name variable (Note: must be ntext or nchar nvarchar type, not varchar type)
declare @paramstring nvarchar(200) - set the string of parameters in the dynamic statement (note: must be ntext or nchar nvarchar type, not varchar type)
declare @output_result int - the query result is assigned to the @output_result
set @QueryString='select @totalcount=count(*) from tablename' --@totalcount is the output parameter
set @paramstring='@totalcount int output' --Set the string defined by the parameters in the dynamic statement, separated by ","
exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output
select @output_result
Of course, the input and output parameters can be used together, so you can try it yourself.
In addition, if you want to output the result set of dynamic statement query, I only think of the following method of using temporary tables, I don't know if you have a better way.
IF object_id('[tempdb].[ dbo].#tmp') IS NOT NULL -- Determine if the temporary table #tmp exists, and if it does, delete it
drop table #tmp
select * into #tmp from tablename where 1=2 -- Create a temporary table #tmp with the same structure as tablename
declare @QueryString nvarchar(1000) -- dynamic query name variable (Note: must be ntext or nchar nvarchar type, not varchar type)
set @QueryString='select * from tablename '
insert into #tmp(field1,field2,...) exec(@querystirng) |
Posted on 2/9/2015 2:44:30 PM
|
|
|
