Help! How to pass string value into store procedure

[wsun]

Hi,

I'm facing a problem, does anyone know how to pass a string value into store procedure? I want to pass the filtering query into store procedure like " where StaffCode between '001' and '005' " in vb6.

For example,
CREATE proc SP_Staff
as
select * into #TmpTable from Staff (I want pass the string here)
select * from #TmpTable

I hope that can get your advise as soon as possible. Thank you.

[Hack]

Are you using ADO to connect to your database?

[wsun]

Hi,

Yes,I'm using ADO to connect to my database.

Besides, I have edit my code show as below.

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

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)  
DECLARE @SQLQuery2 AS VARCHAR(1000) 

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

IF Len(@Filter) > 0  
Begin  
SET @SQLQuery2 = @SQLQuery + ' Where ' + @Filter + @SQLQuery1
End
EXECUTE(@SQLQuery2)
select * from #TmpTable

execute SP_Staff 'StaffCode between 001 and 005'

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

[Hack]

It looks like you are running a SELECT query against a table you DROPPED