fangtan
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I believe that it should - at least, according to the documentation I read, this should be perfectly fine with a Sybase date
ASKER
Why does it only allow varchar to be passed when you do exec? I tried pass smallint, and get error, too.
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?
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(@vsFilte
declare @vsStatement varchar(255)
select @vsStatement='select * from TestTable T where T.Tradedate='
+char(34)+convert(varchar,
+@vsFilters
exec (@vsStatement)
end
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(@vsFilte
declare @vsStatement varchar(255)
select @vsStatement='select * from TestTable T where T.Tradedate='
+char(34)+convert(varchar,
+@vsFilters
exec (@vsStatement)
end
ASKER
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.
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.
ASKER
bhess1, how do I pass the points to you?
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 :)
(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 :)
ASKER
Thanks again, bhess1
ASKER
But does this code work on sybase too?