Solved

Passing DateTime and sql strings to Store Procedure

Posted on 2000-05-08
10
408 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:fangtan
  • 5
  • 4
10 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
Comment Utility
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
 
LVL 1

Author Comment

by:fangtan
Comment Utility
Thanks, bhess1.  
But does this code work on sybase too?
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
I believe that it should - at least, according to the documentation I read, this should be perfectly fine with a Sybase date
0
 
LVL 1

Author Comment

by:fangtan
Comment Utility
Why does it only allow varchar to be passed when you do exec?  I tried pass smallint, and get error, too.
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
Comment Utility
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
 
LVL 1

Author Comment

by:fangtan
Comment Utility
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
 
LVL 1

Author Comment

by:fangtan
Comment Utility
bhess1, how do I pass the points to you?
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
 
LVL 1

Author Comment

by:fangtan
Comment Utility
Thanks again, bhess1
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now