Line 1: Incorrect syntax near getlistofspclass.

what am i doing wrong.. :(

CREATE PROCEDURE getlistofspclass
(
@.date datetime,
@.s varchar(8000) output
)

AS

select @.s = coalesce(@.s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @.date
exec( @.s)
GO

The procedure's parameters should not be enclosed in parentheses. Remove the parentheses that surround the parameters and you should have better luck.|||

my god!! i spent 4 hours on that and $60 on a Que sql server programming book. :(

but now it works. i can hang myself now

|||

hello can you help me again. ...my asp.net code is not working.. it will not send the date to the pramater.

can you make a sameple code in asp.net vb of how you will send a date to that stored procuder and how you would get the returned value

please. thank you verymuch

|||

All I have is a C# version I made the other day, sorry. But it's close enough to VB you should be able to figure out how to port it.

1public int InsertNewThread(Thread ThreadToInsert,out int InternalID)2{3using(SqlConnection sqlConnection =new SqlConnection(GetConnectionString()))4{5SqlCommand sqlCommand =new SqlCommand("Thread_Insert", sqlConnection);6sqlCommand.CommandType = CommandType.StoredProcedure;7sqlCommand.Parameters.Add("@.threadSummary", SqlDbType.NVarChar, 75).Value = ThreadToInsert.ThreadSummary;8sqlCommand.Parameters.Add("@.boardID", SqlDbType.Int).Value = ThreadToInsert.BoardID;9sqlCommand.Parameters.Add("@.internalID", SqlDbType.Int).Direction = ParameterDirection.Output;10sqlConnection.Open();11int RowsEffected = sqlCommand.ExecuteNonQuery();12sqlConnection.Close();13InternalID = (int) sqlCommand.Parameters["@.internalID"].Value;14return RowsEffected;15}16}

12/****** Object: Stored Procedure dbo.Thread_Insert Script Date: 7/28/2006 7:24:19 PM ******/3CREATE PROCEDURE Thread_Insert4@.threadSummaryNVARCHAR( 75 ),5@.internalIDINT OUTPUT6AS7INSERT INTO8 threads( threadSummary )910VALUES11 ( @.threadSummary )1213SELECT14@.internalID = SCOPE_IDENTITY ( )1516GO

|||

thanks again. you know the speed of getting the return value was just the same as putting the sql statement on the webpage itself...people say storedprocedures are faster..oh well

thanks everything worked

|||

Stored procedures are faster. If you coded one page without stored procedures, and another with stored procedures, and timed them with your watch, then you won't notice much.

Take a web server (or multiple web servers) using complex queries and time the difference when they are getting hit a few hundred requests per second, and you will notice a difference, especially in older versions of SQL Server. The newer ones have added a few little things that reduce the difference dramatically in most cases (Cached query plans, even for dynamic queries). They basically turn even dynamic queries into a kind of "temporary" stored procedure, getting most of the sp's benefits, a very nice addition to SQL Server.

Limiting the amount of data used between two tables

Two tables: People and TestValues
People
--
Name varchar(50)
TestValues
--
TestString varchar(10)
Insert into People (Name) values ('William')
Insert into People (Name) values ('Blake')
Insert into People (Name) values ('Kevin')
Insert into People (Name) values ('Merry')
Insert into People (Name) values ('Gary')
Insert into People (Name) values ('Tamara')
Insert into People (Name) values ('Rich')
insert into TestValues (testString) values ('A')
insert into TestValues (testString) values ('M')
insert into TestValues (testString) values ('K')
insert into TestValues (testString) values ('X')
insert into TestValues (testString) values ('Z')
The goal is to have each table filter the other, so that we only return
those items that they have in common. So:
'William' has an "A", so WIlliam and A both are included (there is also
an M, but A is a match so M is not necessary)
"Blake" has an "A", so both are included
"Kevin" has a "K"
"Merry" matches "M"
"Gary" matches "A"
"Rich" doesn't match anything, and isn't included
"X" and "Z" don't match, and aren't included
I would rather not create two cursors and two temp tables if there is a
set based way to do this. Thanks!
- Wm in Kansas Cityselect p.name, min(t.teststring) as teststring
from people p
join testvalues t on p.name like '%'+t.teststring+'%'
group by p.name
this probably won't perform very well, especially if the amount of data
gets large.
seamlyne@.hotmail.com wrote:
> Two tables: People and TestValues
> People
> --
> Name varchar(50)
>
> TestValues
> --
> TestString varchar(10)
>
> Insert into People (Name) values ('William')
> Insert into People (Name) values ('Blake')
> Insert into People (Name) values ('Kevin')
> Insert into People (Name) values ('Merry')
> Insert into People (Name) values ('Gary')
> Insert into People (Name) values ('Tamara')
> Insert into People (Name) values ('Rich')
>
> insert into TestValues (testString) values ('A')
> insert into TestValues (testString) values ('M')
> insert into TestValues (testString) values ('K')
> insert into TestValues (testString) values ('X')
> insert into TestValues (testString) values ('Z')
> The goal is to have each table filter the other, so that we only return
> those items that they have in common. So:
> 'William' has an "A", so WIlliam and A both are included (there is also
> an M, but A is a match so M is not necessary)
> "Blake" has an "A", so both are included
> "Kevin" has a "K"
> "Merry" matches "M"
> "Gary" matches "A"
> "Rich" doesn't match anything, and isn't included
> "X" and "Z" don't match, and aren't included
> I would rather not create two cursors and two temp tables if there is a
> set based way to do this. Thanks!
> - Wm in Kansas City
>|||select * from #people join #TestValues on charindex(testString, Name)>0

Limitation of 1023 Characters

I am using ASP to query a SQL Server 2000 database.
Whenever I try to insert large pieces of data (column is
declared as varchar), the data allowed is limited to 1023
characters. I have installed SP3, and still the same
error. It is not down to any syntax in the cell as trying
to insert 1024 instances of "b" (bbbbb etc. etc.) will
error on the insertion of the 1024th. I am sure this must
be a configuration conflict somewhere.
Anybody out there able to help me rectify this?
Many thanks
Martin Burford> Whenever I try to insert large pieces of data (column is
> declared as varchar),
VARCHAR(what?)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Sorry. Varchar(4000) in this case.
Martin
>--Original Message--
>> Whenever I try to insert large pieces of data (column
is
>> declared as varchar),
>VARCHAR(what?)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||All right, so can you show the statement that ASP is trying to run (e.g. if
you are using conn.execute sqlStatement, use response.write sqlStatement
instead, and show us the result).
Also, the exact error message might be helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> Sorry. Varchar(4000) in this case.
> Martin
> >--Original Message--
> >> Whenever I try to insert large pieces of data (column
> is
> >> declared as varchar),
> >
> >VARCHAR(what?)
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >.
> >|||I think I have actually determined the error. I was
entering my data through Enterprise Manager, which I'm
guessing can't accept more than a byte of info (1,024
chars a bit of a coincedence). Entering the data through
INSERT statements in Query Analyzer works upto 4000 chars
which is fine. This however causes a slight problem...is
there an easier way rather than creating custom insert
statements for each record? Otherwise, I'll just create a
basic admin area interface, and insert the code through
submitted form values (textareas).
Many thanks
Martin Burford
>--Original Message--
>All right, so can you show the statement that ASP is
trying to run (e.g. if
>you are using conn.execute sqlStatement, use
response.write sqlStatement
>instead, and show us the result).
>Also, the exact error message might be helpful.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
>> Sorry. Varchar(4000) in this case.
>> Martin
>> >--Original Message--
>> >> Whenever I try to insert large pieces of data
(column
>> is
>> >> declared as varchar),
>> >
>> >VARCHAR(what?)
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >http://www.aspfaq.com/
>> >
>> >
>> >.
>> >
>
>.
>|||> I think I have actually determined the error. I was
> entering my data through Enterprise Manager,
Don't do that! Enterprise Manager should be used for management, not data
entry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||http://www.aspfaq.com/search.asp?q=enterprise+manager+1023+characters
:-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Martin Burford" <martin_burford@.hotmail.com> wrote in message
news:065101c3c314$97ee3980$a401280a@.phx.gbl...
> I think I have actually determined the error. I was
> entering my data through Enterprise Manager, which I'm
> guessing can't accept more than a byte of info (1,024
> chars a bit of a coincedence). Entering the data through
> INSERT statements in Query Analyzer works upto 4000 chars
> which is fine. This however causes a slight problem...is
> there an easier way rather than creating custom insert
> statements for each record? Otherwise, I'll just create a
> basic admin area interface, and insert the code through
> submitted form values (textareas).
> Many thanks
> Martin Burford
> >--Original Message--
> >All right, so can you show the statement that ASP is
> trying to run (e.g. if
> >you are using conn.execute sqlStatement, use
> response.write sqlStatement
> >instead, and show us the result).
> >
> >Also, the exact error message might be helpful.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:1401c01c3c306$dce2d860$a601280a@.phx.gbl...
> >> Sorry. Varchar(4000) in this case.
> >>
> >> Martin
> >>
> >> >--Original Message--
> >> >> Whenever I try to insert large pieces of data
> (column
> >> is
> >> >> declared as varchar),
> >> >
> >> >VARCHAR(what?)
> >> >
> >> >--
> >> >Aaron Bertrand
> >> >SQL Server MVP
> >> >http://www.aspfaq.com/
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >