Sql Server 2000
這樣寫是對的
declare @sql varchar(1000)
set @sql='Select the age='+cast(@theAge as varchar(10))
exec(@sql) 這樣寫是錯的,經查是因為exec中不能調用函數exec('Select the age='+cast(@theAge as varchar(10)))現遇到如下問題:
因為要做一個系統的查詢的功能(該系統用戶數有限制),不能在數據庫中使用存儲過程,而我的查詢條件不確定,所以需用到sql的exec()。public INVMBData GetDatasBy2(int pagesize,int pageindex,string keywords)
{
string str = @"(SELECT TOP '+ CAST(@PageSize AS VARCHAR(20)) +' * FROM INVMB as A WHERE '+ @Keywords+' AND MB001 NOT IN (SELECT TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' MB001 FROM INVMB WHERE '+ @Keywords+' ORDER BY MB001) ORDER BY MB001) ";
string sql = "Select A.* ,B.MA003 as MB0052 ,C.MA003 as MB0062 ,D.MA003 as MB0072 ,E.MA003 as MB0082,MB064065=(case when MB064=0 Then 0 Else MB065/MB064 END) From " + str + " as A LEFT JOIN INVMA as B ON A.MB005=B.MA002 AND B.MA001=''1'' LEFT JOIN INVMA as C ON A.MB006=C.MA002 AND C.MA001=''2'' LEFT JOIN INVMA as D ON A.MB007=D.MA002 AND D.MA001=''3'' LEFT JOIN INVMA as E ON A.MB008=E.MA002 AND E.MA001=''4''"; SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@Keywords",SqlDbType.NVarChar,200)};
parms[0].Value = pagesize;
parms[1].Value = pageindex;
parms[2].Value = keywords; return FillData("Exec(' " + sql + " ')", parms);
} private INVMBData FillData(string commandText, string paramName, string paramValue)
{
if (dsCommand == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
} INVMBData data = new INVMBData(); SqlCommand command = dsCommand.SelectCommand;
//command.CommandType = CommandType.StoredProcedure;
command.CommandType = CommandType.Text;
command.CommandText = commandText; SqlParameter parm = new SqlParameter(paramName, SqlDbType.NVarChar, 255);
parm.Value = paramValue; command.Parameters.Add(parm); dsCommand.Fill(data);
return data;
}
上面的代碼可以不看,其實主要是因為return FillData("Exec(' " + sql + " ')", parms);這句出錯,因為Exec()中不能調用Cast等函數,請教應該如何處理?
這樣寫是對的
declare @sql varchar(1000)
set @sql='Select the age='+cast(@theAge as varchar(10))
exec(@sql) 這樣寫是錯的,經查是因為exec中不能調用函數exec('Select the age='+cast(@theAge as varchar(10)))現遇到如下問題:
因為要做一個系統的查詢的功能(該系統用戶數有限制),不能在數據庫中使用存儲過程,而我的查詢條件不確定,所以需用到sql的exec()。public INVMBData GetDatasBy2(int pagesize,int pageindex,string keywords)
{
string str = @"(SELECT TOP '+ CAST(@PageSize AS VARCHAR(20)) +' * FROM INVMB as A WHERE '+ @Keywords+' AND MB001 NOT IN (SELECT TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' MB001 FROM INVMB WHERE '+ @Keywords+' ORDER BY MB001) ORDER BY MB001) ";
string sql = "Select A.* ,B.MA003 as MB0052 ,C.MA003 as MB0062 ,D.MA003 as MB0072 ,E.MA003 as MB0082,MB064065=(case when MB064=0 Then 0 Else MB065/MB064 END) From " + str + " as A LEFT JOIN INVMA as B ON A.MB005=B.MA002 AND B.MA001=''1'' LEFT JOIN INVMA as C ON A.MB006=C.MA002 AND C.MA001=''2'' LEFT JOIN INVMA as D ON A.MB007=D.MA002 AND D.MA001=''3'' LEFT JOIN INVMA as E ON A.MB008=E.MA002 AND E.MA001=''4''"; SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@Keywords",SqlDbType.NVarChar,200)};
parms[0].Value = pagesize;
parms[1].Value = pageindex;
parms[2].Value = keywords; return FillData("Exec(' " + sql + " ')", parms);
} private INVMBData FillData(string commandText, string paramName, string paramValue)
{
if (dsCommand == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
} INVMBData data = new INVMBData(); SqlCommand command = dsCommand.SelectCommand;
//command.CommandType = CommandType.StoredProcedure;
command.CommandType = CommandType.Text;
command.CommandText = commandText; SqlParameter parm = new SqlParameter(paramName, SqlDbType.NVarChar, 255);
parm.Value = paramValue; command.Parameters.Add(parm); dsCommand.Fill(data);
return data;
}
上面的代碼可以不看,其實主要是因為return FillData("Exec(' " + sql + " ')", parms);這句出錯,因為Exec()中不能調用Cast等函數,請教應該如何處理?
string str = @"(SELECT TOP '+ CAST(@PageSize AS VARCHAR(20)) +' * FROM INVMB as A WHERE '+ @Keywords+' AND MB001 NOT IN (SELECT TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' MB001 FROM INVMB WHERE '+ @Keywords+' ORDER BY MB001) ORDER BY MB001) ";
string sql = "Select A.* ,B.MA003 as MB0052 ,C.MA003 as MB0062 ,D.MA003 as MB0072 ,E.MA003 as MB0082,MB064065=(case when MB064=0 Then 0 Else MB065/MB064 END) From " + str + " as A LEFT JOIN INVMA as B ON A.MB005=B.MA002 AND B.MA001=''1'' LEFT JOIN INVMA as C ON A.MB006=C.MA002 AND C.MA001=''2'' LEFT JOIN INVMA as D ON A.MB007=D.MA002 AND D.MA001=''3'' LEFT JOIN INVMA as E ON A.MB008=E.MA002 AND E.MA001=''4''";