Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-26 : 12:58:10
|
Boby writes "Guru,
I have a situation where my SQL Statement is dynamic (ie my Where Condition changing time to time) and I want to get the records which satisfy the where condition using EXEC method(or any method).
I use this in a store procedure to return records. My intension is to write one function for all querries.
Here is an example.
select @NoOfEmp =count(1) from employee where employee_ID = @EID
I can get the value from @@RowCount or @NoOfEmp
Now my client application send me the where clause
EXEC ("select 1 from employee" + @WhereCondition)
@@RowCount always return 1 (for an invalid @EID also)
If I try this.
set @SQL = "declare @NoOfEmp int select @NoOfEmp =count(1) from employee" + @WhereCondition + " select @NoOfEmp "
exec (@SQL)
or
set @SQL = "declare @NoOfEmp int select @NoOfEmp =count(1) from employee" + @WhereCondition + " return(@NoOfEmp)"
exec (@SQL)
I can't get the value of @NoOfEmp nor the @@Rowcount
I tried the sp_executeSQL also but failed.
exec @a=(@SQL) WRONG doesn't work either.
or
declare @Ret int
set @SQL = "declare @NoOfEmp int select @NoOfEmp =count(1) from employee" + @WhereCondition + " select @Ret =@NoOfEmp"
exec (@SQL)
WRONG doesn't work either.
is there is any way to do this.
Temp solution I did through a #temp Table.
EXEC ("declare @NoOfEmp int select 1 from employee" + @WhereCondition)" |
|
|
|
|
|
EXEC Method limitation