Solved

Passing DateTime and sql strings to Store Procedure

Posted on 2000-05-08
10
435 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

837 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