July 9, 2005 at 9:16 pm
Hello,
I have created a stored procedure to create a table.
CREATE PROCEDURE sp_forecast
@addfilter as char(500)
as
declare @itemid as char(16)
if object_id('fquery') is not null
drop table fquery
create table fquery (itemcode char(16),prodcode char(16))
declare prdhist_cursor cursor for select prodid from prdhist where 1=1 + @addfilter
open prdhist_cursor
fetch next from prdhist_cursor into @itemid
while (@@fetch_status=0) begin
fetch next from prdhist_cursor into @itemid
end
close prdhist_cursor
deallocate prdhist_cursor
GO
The @addfilter is the continuation of the query i generated from VB.
When I execute it = exec sp_forecast 'and ph_date between ''1/1/2005'' and ''1/5/2005'''
And error occurs saying = 'Syntax error converting the varchar value ' and ph_date between '1/1/2005' and '1/5/2005''
What shoud I do? Please help....
July 9, 2005 at 10:55 pm
You can't do that in sql server. You have to use dynamic sql for such a task (and build the whole cursor part in the dynamic sql).
Why is the goal of this sp?
July 10, 2005 at 5:43 am
My goal is to run cetain User Defined Function i needed to generate a record for my new table fquery which I will be needed for my program.
BTW, what do you mean by dynamic sql?
July 10, 2005 at 8:36 am
1) Why are you creating your table 'fquery' within the procedure - do you actually want to use a temporary table ?!
2) What data type is your ph_date ?
3) Have you posted the complete procedure here ?! What are you doing with the value in your @itemid variable ?
4) if your select statement is "select prodid from prdhist where 1=1 + @addfilter" & you pass something like "and ph_date between ''1/1/2005'' and ''1/5/2005''" to the input parameter, it is not correct t-sql syntax.
5) You should post the table definition of your 'prdhist' table so someone can come up with a suitable solution.
6) Give us some sample data of what you have in your prdhist table and what you expect to see when you run your stored procedure!
7) Lastly here's an explanation of dynamic sql from Books Online which is simple and straightforward:
"A static SQL statement is a complete Transact-SQL statement that is embedded in the program source code. Static SQL statements can be placed into stored procedures and can contain host variables.
With dynamic SQL statements, knowing the complete structure of an SQL statement before building the application is not necessary. Dynamic SQL statements allow run-time input to provide information about the database objects to query."
However, in all likelihood you should be able to do this without using dynamic sql - once you post the sample data, table definition and result expected, there should be a simple solution....
**ASCII stupid question, get a stupid ANSI !!!**
July 10, 2005 at 5:58 pm
where 1=1 + @addfilter
2 problems in this clause:
1. it will not return any results as long as there is a value in @addfilter. It will always return FALSE condition.
2. @addfilter is a char type, if your @addfilter = '200500711' then you will no error. If @addfilter = '1/5/2005' then you will have char conversion (to int) error.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply