SQL server builds a method to execute dynamic SQL statements, serversql

SQL server builds a method to execute dynamic SQL statements, serversql

1: Common SQL statements can be executed using exec

Select * from tableName exec ('select * from tablename') exec sp_executesql N 'select * from tablename' -- note that N must be added before the string

2: when the field name, table name, database name and so on are used 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 of FiledName, not required. 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 change the string to a variable in the form of declare @ fname varchar (20) set @ fname = 'filedname' -- set the field name declare @ s varchar (1000) set @ s = 'select' + @ fname + 'from tablename' exec (@ s) -- successful -- exec sp_executesql @ s -- the error declare @ s Nvarchar (1000) will be reported. -- note that this is changed to nvarchar (1000) (ntext or nchar must be nvarchar, cannot be of the varchar type) set @ s = 'select' + @ fname + 'from tableName' exec (@ s) -- exec sp_executesql @ s -- this sentence is correct

3. input or output parameters

-- (1) input parameter: declare @ QueryString nvarchar (1000) -- dynamic query statement variable (Note: ntext or nchar must be nvarchar type, not varchar type) declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (Note: It must be of the ntext or nchar nvarchar type and cannot be of the varchar type) declare @ input_id int -- defines the parameter value of the dynamic statement to be passed in. set @ QueryString = 'select * from tablename where id = @ id' -- id is the field name, @ id is the parameter set @ paramstring = '@ id int' to be passed in -- set the string set @ input_id = 1 defined by the parameter in the dynamic statement -- set the Parameter the value is 1 exec sp_exe. Cutesql @ querystring, @ paramstring, @ id = @ input_id -- if multiple parameters exist: declare @ QueryString nvarchar (1000) -- dynamic query statement variable (note: it must be ntext or nchar nvarchar type and cannot be varchar type.) declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (note: it must be ntext or nchar nvarchar type, and cannot be varchar type.) declare @ input_id int -- defines the value of the parameter to be passed in the dynamic statement. Parameter 1 declare @ input_name varchar (20) -- defines the value of the parameter to be passed in a 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 in. set @ paramstring = '@ id int, @ name varchar (20) '-- sets the string defined by parameters in a dynamic statement ", "separate set @ input_id = 1 -- set the parameter value of the dynamic statement to be passed in to 1 set @ input_name = 'zhang san' -- set the parameter value of the dynamic statement to be passed in to" Zhang San" exec sp_executesql @ querystring, @ paramstring, @ id = @ input_id, @ name = @ input_name -- pay attention to the parameter order -- (2) output parameter declare @ num int, @ sqls nvarchar (4000) set @ sqls = 'select count (*) from tableName 'exec (@ sqls) -- how to put exec execution results into Variables Medium? Declare @ QueryString nvarchar (1000) -- dynamically queries language name variables (Note: ntext or nchar must be nvarchar type, not varchar type) declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (Note: It must be of the ntext or nchar nvarchar type and cannot be of the varchar type) declare @ output_result int -- the query result is assigned to @ output_result set @ QueryString = 'select @ totalcount = count (*) from tablename '-- @ totalcount is the output result parameter set @ paramstring =' @ totalcount int output' -- sets the defined string of parameters in a dynamic statement, and uses multiple parameters ", "Separate exec sp_exec Utesql @ querystring, @ paramstring, @ totalcount = @ output_result output select @ output_result -- of course, the input and output parameters can be used together. You can try them yourself. -- In addition, if the result set of the dynamic statement query is to be output, I only want to use the temporary table method below. I wonder if you have any better method. IF object_id ('[tempdb]. [dbo]. # tmp ') is not null -- determines whether a temporary table # tmp exists, delete 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 of language name variables (Note: It must be ntext or nchar nvarchar type, not varchar type) set @ QueryString = 'select * from tablename' insert into # tmp (field1, field2,) exec (@ querystirng)

For some special reasons, we need to dynamically create SQL statements in SQL statements or stored procedures, and then dynamically execute them in SQL statements or stored procedures.

Here, Microsoft provides two methods:

Execute function

The execution method is

Execute (@ SQL) to dynamically Execute an SQL statement, but the SQL statement here cannot get the returned results. Next we will introduce another method.

Use the Stored Procedure sp_ExecuteSql

You can use this stored procedure to return parameters in dynamic statements.

For example

Declare @ SQL nvarchar (800), @ dd varchar (20) set @ SQL = 'set @ mm = ''test string ''' exec sp_executesql @ SQL, n' @ mm varchar (20) output', @ dd outputselect @ dd

Execute the SQL statement to return the value of a variable created internally to an external caller.

It mainly comes from an accidental need at work:

Create proc proc_InToServer @ toll site no. varchar (4), @ Lane NO. tinyint, @ entry time varchar (23), @ UID char (16), @ license plate varchar (12 ), @ model char (1), @ identification license plate number varchar (12), @ identification model char (1), @ paid amount money, @ Transaction Status char (1 ), @ image bit, @ departure time varchar (23), @ speed float, @ HasInsert int outputasbegin declare @ inTime datetime, @ TableName varchar (255), @ leaveTime datetime, @ HasTable bit, @ SQL nvarchar (4000) select @ intime = Convert (datetime, @ entry time), @ leaveTime = Convert (datetime, @ Exit Time) set @ TableName = 'etc03 _ 01_OBE Original vehicle transfer record table _ '+ dbo. formatDatetime (@ intime, 'yyyymmdd') select @ HasTable = (Case when Count (*)> 0 then 1 else 0 end) from sysobjects where id = Object_id (@ TableName) and ObjectProperty (id, 'isusertable') = 1 if @ HasTable = 0 begin set @ SQL = 'create TABLE [dbo]. ['+ @ TableName +'] ([toll site number] [char] (4) COLLATE Chinese_PRC_CI_AS not null, [lane number] [tinyint] not null, [entry time] [datetime] not null, [UID] [char] (16) COLLATE Chinese_PRC_CI_AS not null, [license plate] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL, [model] [char] (1) COLLATE Chinese_PRC_CI_AS NULL, [Identification license plate number] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL, [Identification Model] [char] (1) COLLATE Chinese_PRC_CI_AS NULL, [paid amount] [money] NULL, [Transaction Status] [char] (1) COLLATE Chinese_PRC_CI_AS NULL, [with image] [bit] not null, [departure time] [datetime] NULL, [speed] [float] NULL, Constraint '+ 'pk _' + @ TableName + 'Primary key (toll site number, lane number, entry Time, UID) ON [PRIMARY] 'execute (@ SQL) end set @ SQL = 'select @ Cnt = count (*) from '+ @ TableName + 'where toll site no. = ''' + @ toll site no. + ''' and Lane NO. =' + cast (@ Lane NO. as varchar (4)) + 'and entry time = ''' + @ entry time + ''' and UID = ''' + @ UID + ''' set @ SQL = @ SQL + 'if @ Cnt = 0 'set @ SQL = @ SQL + 'insert' + @ TableName + 'values (''' + @ toll site number + ''', '+ cast (@ lane number as varchar (4) +', ''' + @ entry time + ''', ''' + @ Uid + ''', ''' + @ license plate + ''', ''' + @ model + ''', ''' + @ recognize license plate number + ''', ''' + @ Recognition Model + ''', '+ Cast (@ billing amount as varchar (8) +', ''' + @ Transaction Status + ''', '+ cast (@ image as varchar (1) +', ''' + @ departure time + ''', '+ Cast (@ speed as varchar (8 )) + ')' -- Execute (@ SQL) exec sp_executesql @ SQL, n' @ Cnt int output', @ HasInsert outputend

In this way, you will have some knowledge.