Dynamic SQL statement

xiaoxiao2021-03-06  112

1: Ordinary SQL statement can be performed with EG: Select * from tablename EXEC ('select * from tablename ") sp_executesql n'select * from tablename' - Please pay attention to the string must add N 2: field name, table name When the database name is variable, it is necessary to use dynamic SQLEG: Declare @fname varchar (20) set @fname = '[name]' select @fname from sysobjects - Error EXEC ('SELECT' @fname 'from sysobjects ') - Please note that the edge of the single quotes before and after the addition of the sparks exec sp_executesql n' select ' @fname ' from sysobjects' Of course, the string is changed to variables. Declare @S varchar (1000) Set @S = 'SELECT' @fname 'from sysobjects' exec (@s) - successful exec sp_executesql @s - This sentence will report an error Declare @s nvarchar (1000) - Note to NVARCHAR (1000) Set @S = 'SELECT' @fname 'from sysobject' exec (@s) - successful exec sp_executesql @s - This sentence is correct, 3: output parameter EG: Declare @num, @sqlsset @ SQLS = 'SELECT Count (*) from ' @servername ' .a.dbo.b'Exec (@sqls) How can I put the result of Exec to save the result of the variable @num Declare @num int, @sqls nvarchar (4000) set @ SQLS = 'SELECT @ a = count (*) from' @servername .a.dbo .B'Exec sp_executesql @ SQLS, N '@ a int outprut', @num outputselect @num

转载请注明原文地址:https://www.9cbs.com/read-124593.html

New Post(0)