Solved

Passing DateTime and sql strings to Store Procedure

Posted on 2000-05-08
10
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
ID: 2789999
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
ID: 2790131
Thanks, bhess1.  
But does this code work on sybase too?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2790205
I believe that it should - at least, according to the documentation I read, this should be perfectly fine with a Sybase date
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 1

Author Comment

by:fangtan
ID: 2790244
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
ID: 2790324
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2790355
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
ID: 2794760
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
ID: 2828126
bhess1, how do I pass the points to you?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2834042
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
ID: 2835626
Thanks again, bhess1
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

739 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