problem with a stored procedure

Hello guys,
I have again problem with a stored procedure in sql server.
I would like to execute this query
declare @.sSQL varchar(255)
SET @.sSQL =
'SELECT TOP 1 [value], date, name, [times]
FROM (SELECT value, date, times, name
FROM view
WHERE ValueDate =
(SELECT MAX(date)
AS maxdate
FROM view
v
WHERE (date
BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
WHERE (name = ''TEST'')
GROUP BY [value], date, [times], name
ORDER BY [times] DESC'
EXEC (@.sSQL)
When I execute the query I have no problem but ... when i tried to
stored it I have this several problem I do not know why ... something
like
Line 4: Incorrect syntax near '='
I would like be able to pass variable on it and create function to
store the results in a table.
Please someone could help me on that.
Inaina
Replace EXEC(@.sql) with PRINT @.sql in order to debug and try run it in the
QA.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> When I execute the query I have no problem but ... when i tried to
> stored it
What does "tried to stored it" mean?

> I would like be able to pass variable on it and create function to
> store the results in a table.
Well, you can't execute dynamic SQL inside a function. If you provide
better specifications on exactly what you are trying to accomplish (instead
of describing how you are already trying to accomplish it), we may be able
to provide better assistance.|||Is it actually spanning multiple lines in your stored procedure? I don't
think this is allowed. Strings have to be contained on a single line.
Either put everything on one line, or concatenate each line to the end of
@.sSQL
Also, 255 is probably not long enough to contain your SQL. Bump this up to
2000 and it should handle most reasonable sql statements.
Lastly, don't ever use dynamic SQL if you have a choice. There are times
when it is needed, but you should simply be able to pass the parameters
inside the stored procedure without dynamic SQL.
Look up "Parameters" in books on line for an explanation of how to use them.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> Is it actually spanning multiple lines in your stored procedure? I don't
> think this is allowed. Strings have to be contained on a single line.
Strings can span lines. The only (obvious) limitation is the fact that they
need to be enclosed in single quotes.
ML
http://milambda.blogspot.com/|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||This sounds better and fairly simple to acomplish. All we need now is DDL an
d
sample data.
This article will help you help us:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||Thank you I will go through that and try to find the solution of my
problem. Thank you :D|||Hello,
I tried to see that but what I need it is to have the max of each month
since the startdate until endate. I already have the max of the period
a set. how to have the list of all period.
Can I use the function month() to accomplish it?
ina