Passing DateTime and sql strings to Store Procedure

I have a question about Store Procedure.

If I use the following type of create procedure:

create procedure test
@StartDate smalldatetime,
@Filters varchar(200)
AS
exec ("SELECT * FROM TestTable T Where T.Tradedate = " + @StartDate + " " +
@Filters)
GO

Then I do
execute test  '1/1/2000', " AND PTID > 0"
GO

I got error:
Server: Msg 207, Level 16, State 3,
Line 1
Invalid column name '¬Ž'.

If I do
create procedure test
@StartDate smalldatetime
AS
SELECT * FROM TestTable T Where T.Tradedate = @StartDate @Filters
GO

I got error:
Server: Msg 170, Level 15, State 1, Procedure fangtest, Line 7
Line 7: Incorrect syntax near '@Filters'.

I want to know if there is a way to pass SQL strings to Store Procedure and also works fine with DataTime.  

The first way doesn't work on SYBASE.  The solution should work on both MS-SQL and SYBASE.

I am using SQL 7.
LVL 1
fangtanAsked:
Who is Participating?
 
Brendt HessSenior DBACommented:
Try this procedure:

create procedure test
@StartDate varchar(10),
@Filters varchar(200)
AS
exec ("SELECT * FROM TestTable T Where T.Tradedate = '" + @StartDate + "' " +
@Filters)
GO


And do:

execute test  "1/1/2000", " AND PTID > 0"
GO
0
 
fangtanAuthor Commented:
Thanks, bhess1.  
But does this code work on sybase too?
0
 
Brendt HessSenior DBACommented:
I believe that it should - at least, according to the documentation I read, this should be perfectly fine with a Sybase date
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fangtanAuthor Commented:
Why does it only allow varchar to be passed when you do exec?  I tried pass smallint, and get error, too.
0
 
Brendt HessSenior DBACommented:
Actually, I don't know that it does.  However, the 'execute' that you are using does not appear to translate the data types correctly.  How are you using execute - from the query analyzer?
0
 
Gustavo Perez BuenrostroCommented:
fangtan,
Check next procedure and let me know your opinion:

create proc test
 @dtStartDate smalldatetime
,@vsFilters varchar(200)=null
as
begin
  set nocount on
  select @vsFilters=isnull(@vsFilters,space(1))
  declare @vsStatement varchar(255)
  select @vsStatement='select * from TestTable T where T.Tradedate='
                     +char(34)+convert(varchar,@dtStartDate,121)+char(34)+space(1)
                     +@vsFilters
  exec (@vsStatement)
end
0
 
fangtanAuthor Commented:
Thanks, bhess1.  You solution is good in MS-SQL.  I find out "execute" in MS-SQL can be used to execute string (than's the reason everything have to be passed down as char or convert to).
But in Sybase you can do sp_remotesql in some version.  But even use sp_remotesql, you can only use up to 255 chars.

Anyway, you solution is correct in MS-SQL.  You get the points.

0
 
fangtanAuthor Commented:
bhess1, how do I pass the points to you?
0
 
Brendt HessSenior DBACommented:
On the bar above my comment is a button - Accept Comment as Answer.  Click that, and it'll be answered.

(BTW - make sure you accept the one that is the answer - I've had several people accept comments like this one as an answer to a tech question :)
0
 
fangtanAuthor Commented:
Thanks again, bhess1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.