Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing DateTime and sql strings to Store Procedure

Posted on 2000-05-08
10
Medium Priority
?
452 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:
Brendt Hess earned 600 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:Brendt Hess
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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:Brendt Hess
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:Brendt Hess
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

972 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