如何SQL动态添加查询字段

如student表中有如下内容：

现在查询条件不定，但只能是一个字段，如：可能会查sex为男的人，可能会查name为张三的人。可以这样写一个简单的存储过程。

1

2

3

4

5

6

7

declare @<a href="https://www.baidu.com/s?wd=col&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YzrHmdn1DkrHwbPHDzujD40ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPHm1njcknj6s" target="_blank" class="baidu-highlight">col</a>varchar(20)

declare @sql varchar(2000)

declare @str varchar(20)

set @<a href="https://www.baidu.com/s?wd=col&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YzrHmdn1DkrHwbPHDzujD40ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPHm1njcknj6s" target="_blank" class="baidu-highlight">col</a>='sex'

set @str='男'

本文主要通过调用SQL Server 的系统存储过程和系统表 在程序中动态增加和删除用户 一 首先用系统管理员帐号登录系统 程序省略 请看源程序 　二 进入用户管理界面 读取数据库 从master sysdatabse中读取所有数据库 void CUser::AddDataBase(){_RecordsetPtr rs_bstr_t btHRESULT hrCString strbt=(_bstr_t) select * from master sysdatabases try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()while(!rs >EndOfFile){str=(char*)(_bstr_t)rs >Fields >GetItem( name ) >Valuem_database AddString(str)rs >MoveNext()}rs >Close()m_database SetCurSel( )}catch(__error){}} 读取用户 从master syslogins视图中读取所有非NT用户 void CUser::AddUser(){_RecordsetPtr rs_bstr_t btHRESULT hrCString strbt=(_bstr_t) select * from master syslogins where isntname= try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()while(!rs >EndOfFile){str=(char*)(_bstr_t)rs >Fields >GetItem( name ) >Valuem_user_list AddString(str)rs >MoveNext()}rs >Close()m_user_list SetCurSel ( )}catch(__error){}} 读取当前用户有权限的数据库 每个数据库有权限的用户在每个数据的sysusers表中 void CUser::OnSelchangeUserList(){CString strUser strDBm_db_list ResetContent ()m_user_list GetText (m_user_list GetCurSel () strUser)for(int i= i<m_database GetCount ()i++){m_database GetLBText (i strDB)if(HasRight(strUser strDB)){m_db_list AddString (strDB)}}if(m_db_list GetCount ()>){m_db_list SetCurSel ( )}}//查看用户user有没有数据库database的权限BOOL CUser::HasRight(CString user CString database){_RecordsetPtr rs_bstr_t btHRESULT hrBOOL bResult=FALSEbt=(_bstr_t) select * from +(_bstr_t)database+(_bstr_t) sysusers where name= +(_bstr_t)user+(_bstr_t) try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()rs >Close()bResult=TRUE}catch(__error){}return bResult} 增加用户及授予权限 增加用户调用存储过程sp_addlogin 授予权限调用存储过程sp_grantdbaccess 基本用法为:sp_addlogin username password sp_grantdbaccess usernamevoid CUser::OnOK(){// TODO: Add extra validation hereUpdateData()CString strDBm_database GetLBText (m_database GetCurSel() strDB)if(m_user IsEmpty () || m_user== sa )returntry{if(m_user_list FindString ( m_user)<){cn >Execute ( sp_addlogin +(_bstr_t)m_user+ +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_user_list AddString (m_user)}else{cn >PutDefaultDatabase ((_bstr_t)strDB)cn >Execute ( sp_grantdbaccess +(_bstr_t)m_user+(_bstr_t) NULL adExecuteNoRecords)m_db_list AddString (strDB)}}catch(__error){AfxMessageBox( 发生错误！ )}//CDialog::OnOK()} 删除用户及权限: 删除用户调用存储过程sp_droplogin 授予权限调用存储过程sp_revokedbaccess 基本用法为:sp_droplogin usernamesp_revokedbaccess usernamevoid CUser::OnBnClickedDel(){UpdateData()m_user TrimRight ()if(m_user IsEmpty () || m_user== sa || m_user_list FindString ( m_user)<){AfxMessageBox( 不能删除! )return}CString strDBm_database GetLBText (m_database GetCurSel () strDB)try{if(m_db_list GetCount ()<){//删除用户cn >Execute ( exec sp_droplogin +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_user_list DeleteString (m_user_list FindString ( m_user))}else{//删除权限if(m_db_list FindString ( strDB)>= ){cn >PutDefaultDatabase ((_bstr_t)strDB)cn >Execute ( exec sp_revokedbaccess +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_db_list DeleteString (m_db_list FindString ( strDB))}}}catch(__error){AfxMessageBox( 发生错误! )}} lishixinzhi/Article/program/SQLServer/201311/21990

---