-
I'm trying to write the following stored procedure where the @strStock variable represents a table in the SQL DB:
CREATE PROCEDURE sp_KairosQueryMINMAXstock
@strStock char(4),
@strDate char(10),
@strStartTime char(8),
@strEndTime char(8)
AS
Select MIN(trade), MAX(trade), Count(*) from strStock
where Date = @strDate
and Time > @strStartTime
and Time < @strEndTime
If I run it as a straight query from VB, I have no trouble. But how do I write the query so that the table name variable @strStock is properly inserted into the stored proc.
Thanks
-
if i remember correctly all you need to do is
CREATE PROCEDURE sp_KairosQueryMINMAXstock
@strStock char(4),
@strDate char(10),
@strStartTime char(8),
@strEndTime char(8)
AS
Select MIN(trade), MAX(trade), Count(*) from @strStock
where Date = @strDate
and Time > @strStartTime
and Time < @strEndTime
could be wrong though
-
...
build your sql string inside the stored proc and
exec it...
so
CREATE PROCEDURE sp_KairosQueryMINMAXstock
@strStock char(4),
@strDate char(10),
@strStartTime char(8),
@strEndTime char(8)
as
begin
declare @strline varchar(255)
declare @strline1 varchar(255)
select @strline = "select min(....... from " + @strStock
select @strline = " where ....."
-- then here
exec (strline + strline2)
-
Thanks for your help, expecially the tip about concatenating everything in the EXEC statement. After several hours running the sp through T-SQL Debugger (a pain to install, but worth it), I could see that any @Str with the @strStock in it failed to work. So I figured I had to concatenate it somehow. Then I checked back here and saw your reply. I've simplified everything, but this is finally what works. Don't forget to put double-quotes around the single quote.
CREATE PROCEDURE sp_Kairos6
@strStock char(4),
@strDate char(10)
AS
begin
declare @Str1 varchar(255)
declare @Str2 varchar(255)
select @Str1 = "select Count(*) from "
select @Str2 = " where Date = " + "'" + @strDate +"'"
exec (@Str1 + @strStock + @Str2)
Thanks again
end