1. 查询
a) 相同表结构的两张表联合查询:
SELECT DISTINCT * FROM ( SELECT * FROM [SqlServerExpressTest1].[dbo].[Record1] WHERE [UpldStatus] LIKE 'YES' UNION ALL SELECT * FROM[SqlServerExpressTest1].[dbo].[Record2] )AS tmp WHERE [UpldStatus] LIKE 'YES' ORDER BY [BigPackage] ASC,[Sn] ASC
b) 动态拼接语句,以下是一个获取当前数据库下所有以'Bag'打头的表数据、并且筛选UpldStatus列数据为NOT的例子(以存储过程的方式):
--获取当前数据库下所有名称以'Bag'打头的表数据的联合 if OBJECT_ID('pro_test') is not null drop proc pro_test go create proc pro_test --创建存储过程 as declare @str varchar(max) set @str = '' select @str = @str + 'select *,''' + name + '''as t_name from ' + name + char(10) + ' union all ' + char(10) from sysobjects where xtype = 'U' and category = 0 and CHARINDEX('Bag', name) = 1 and CHARINDEX('20160409', name) = 0 --所有名称以'Bag'打头、且不以'20160409'结尾的用户表('U') select @str = LEFT(@str, LEN(@str)-12) --去除最后多余的' union all ' + char(10),一共12个字符 print @str exec('select * from (' + @str + ')AS tmpSelection' + ' where [UpldStatus] like ''NOT'' and [Box] is not null order by [Box] asc,[Sn] desc') --执行动态语句 go exec pro_test
或者直接:
--获取当前数据库下所有名称以'Bag'打头的表数据的联合 declare @str varchar(max) set @str = '' select @str = @str + 'select *,''' + name + '''as t_name from ' + name + char(10) + ' union all ' + char(10) from sysobjects where xtype = 'U' and category = 0 and CHARINDEX('Bag', name) = 1 and CHARINDEX('20160427', name) = 0 --所有名称以'Bag'打头、且不以'20160427'结尾的用户表('U') select @str = LEFT(@str, LEN(@str)-12) --去除最后多余的' union all ' + char(10),一共12个字符 print @str exec('select * from (' + @str + ')AS tmpSelection' + ' where [UpldStatus] like ''NOT'' and [Box] is not null order by [Box] asc,[Sn] desc') --执行动态语句
PS:这里在联合查询的时候形如select *,name as t_name是为了在查询结果中反映出表的来源,以便后续可能的update操作。
2. 存储过程
这一段是在Microsoft SQL Server Management Studio里执行的query语句(三个单引号表示一个单引号):
CREATE PROCEDURE sp_insert_record -- Add the parameters for the stored procedure here @TableName nvarchar(40), @Sn nvarchar(2), @YMD nvarchar(10), @HMS nvarchar(8) AS DECLARE @sql nvarchar(4000) BEGIN -- Insert statements for procedure here SET @sql = 'INSERT INTO ' + @TableName + '(Sn, YMD, HMS) VALUES (''' + @Sn + ''',''' + @YMD + ''',''' + @HMS''')' EXEC (@sql) END GO
执行完后,可以在Databases->定义的数据库名->Programmability->Stored Procedures下面能看到对应的存储过程。
然后再调用存储过程执行就可以了:
exec sp_insert_record TableName,01,2016-03-07,13:11:26
C#中的调用过程为:
/// <summary> /// 数据库执行存储过程 /// </summary> /// <param name="dbConn">数据库连接</param> /// <param name="spName">存储过程名称</param> /// <param name="parasName">存储过程参数名称数组</param> /// <param name="parasValue">存储过程参数值数组</param> /// <returns></returns> public bool StoreProcExec(string dbConn, string spName, string[] parasName, string[] parasValue) { try { SqlConnection conn = new SqlConnection(dbConn); SqlCommand comm = new SqlCommand(spName, conn); comm.CommandType = CommandType.StoredProcedure; // 参数 SqlParameter sqlParme; int i = 0; foreach (string para in parasName) { sqlParme = comm.Parameters.Add("@" + para, SqlDbType.NVarChar); sqlParme.Direction = ParameterDirection.Input; sqlParme.Value = parasValue[i]; i++; } // 执行存储过程 conn.Open(); comm.ExecuteNonQuery(); conn.Close(); return false; } catch (Exception ex) { Console.WriteLine("SQL Insert Exception:" + ex.Message); return false; } }