如何使用存储过程过滤数据库表? [英] How to Filter a database table by using stored procedure?

问题描述

我面临一个问题，希望可以从您那里得到一些建议.我想将过滤器查询传递到存储过程.

例如，

Hi,

I''m facing a problem and hope that can get some advise from you. I want to pass a filter query into store procedure.

For example,

alter proc [dbo].[SP_Staff]
     @Filter varchar(1000)=''
as
--drop tempTable if exist
if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U')
    and o.id = object_id(N'tempdb..#TmpTable')) 
DROP TABLE #TmpTable;
DECLARE @SQLQuery AS VARCHAR(1000)
DECLARE @SQLQuery1 AS VARCHAR(1000)

SET @SQLQuery = 'select * into TmpTable From Staff'
SET @SQLQuery1 = 'order by StaffCode'
IF Len(@Filter) > 0
Begin
SET @SQLQuery = @SQLQuery + ' Where ' + @Filter + @SQLQuery1
End
EXECUTE(@SQLQuery)
select * from #TmpTable

execute SP_Staff 'StaffCode between 001 and 005'

执行存储过程时出现错误无效的对象名称"#TmpTable"".

It got error "Invalid object name ''#TmpTable''" when I execute the store procedure.
How to solve this error?

推荐答案

frnd:

基本上无法找到临时表#TmpTable
首先，我想知道您要在哪里创建 #TmpTable 表.

下一行缺少另一件事:

Hi frnd:

Basically it is unable to find temporary table #TmpTable
First of all i would like to know where you are creating #TmpTable table.

One another thing missing in the below line:

SET @SQLQuery = 'select * into TmpTable From Staff'

应该是这样的:

It should be like this :

SET @SQLQuery = 'select * into #TmpTable From Staff'

我的建议是不要使用此代码:

My suggestion is instead of using this code:

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U')
    and o.id = object_id(N'tempdb..#TmpTable'))
DROP TABLE #TmpTable;

像这样修改代码:

Modify the code like this :

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U')
    and o.id = object_id(N'tempdb..#TmpTable'))
Begin
  DROP TABLE #TmpTable;
  Create Table #TmpTable(
   Id int --  you may include more columns (for Example)
  )
End

您无需在开始时删除临时表. SP执行完后，仍将其删除.

看这个:

You don''t need to remove the temp table in begining. It would anyways be removed after the SP execution.

Look at this:

alter proc [dbo].[SP_Staff]
     @Filter varchar(1000)=''
as

DECLARE @SQLQuery AS VARCHAR(1000)
DECLARE @SQLQuery1 AS VARCHAR(1000)

SET @SQLQuery = 'select * into #TmpTable From Staff'
SET @SQLQuery1 = 'order by StaffCode'
IF Len(@Filter) > 0
Begin
SET @SQLQuery = @SQLQuery + ' Where ' + @Filter + @SQLQuery1
End
EXECUTE(@SQLQuery)
select * from #TmpTable

DROP TABLE #TmpTable;

应该像这样

It should be like this

SET @SQLQuery = 'select * into #TmpTable From Staff'
SET @SQLQuery1 = 'order by StaffCode'

这篇关于如何使用存储过程过滤数据库表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！