Stored Procedure behavior [newbie] HELP!!!

Stored Procedure behavior [newbie] HELP!!!

Post by Chad Mille » Tue, 17 Jun 2003 23:43:41



The following SP below works OK, but it returns too many
rows. Im just trying to get a count from 2 joined tables.
If I add a GROUP BY (tbl_MainNotesArchive.Notes) to group
the fields (which works as a normal SQL statement), I get
an error: "][SQL Server]Subquery returned more than 1
value. This is not permitted when the subquery follows
=, !=, <, <= , >, >= or when the subquery is used as an
expression." on the ASP page. I am kinda new to complex
SPs and an under a deadline!!!

Works, but too many counts...

AS

tbl_MainNotesArchive JOIN tbl_MainArchive ON
tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
WHERE
(                                          


OR

OR

)
Group By tbl_MainArchive.ID
)
GO

This version fails...

AS

tbl_MainNotesArchive JOIN tbl_MainArchive ON
tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
WHERE
(                                          


OR

OR

)
Group By tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
)
GO

 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Edgardo Valde » Wed, 18 Jun 2003 00:07:50


What column you want to get a count of?
>-----Original Message-----
>The following SP below works OK, but it returns too many
>rows. Im just trying to get a count from 2 joined tables.
>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to group
>the fields (which works as a normal SQL statement), I get
>an error: "][SQL Server]Subquery returned more than 1
>value. This is not permitted when the subquery follows
>=, !=, <, <= , >, >= or when the subquery is used as an
>expression." on the ASP page. I am kinda new to complex
>SPs and an under a deadline!!!

>Works, but too many counts...
>CREATE PROCEDURE sp_GetArchiveSearchCount


>AS

>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>WHERE
>(                                      


>OR

>OR

>)
>Group By tbl_MainArchive.ID
>)
>GO

>This version fails...
>CREATE PROCEDURE sp_GetArchiveSearchCount


>AS

>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>WHERE
>(                                      


>OR

>OR

>)
>Group By tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
>)
>GO

>.


 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Chad Mille » Wed, 18 Jun 2003 00:36:09


I want ONLY a count of tbl_MainArchive.ID Where the
search criteria may be found in either tbl_MainArchive,
or in tbl_MainNotesArchive.Notes. All went hywire when I
linked the Notes field in the Join.
I tried Count(*), Count(tbl_MainArchive.ID), Count
(tbl_MainNotesArchive.ID) AS colCount.

This last one seems to work, but I can seem to get the
OUTPUT var to = colCount

SP are the bane of my existance. I think I am in over my
head! But they are so freakin' fast! The dB I have built
will grow to 100,000s of records in a very short while,
so this search feature must use SPs.

I know there is a problem with the logic. tbl_MainArchive
has unique IDs that are tied to "caseID" in
tbl_MainNotesArchive. ie: There can be multiple notes
tied to each unique record in tbl_MainArchive. Simple for
a standard SQL statement, but if there are, say, 3 notes
linked to tbl_MainArchive.ID, the SP returns 3, instead
of 1, even if the criteria is not found in
tbl_MainNotesArchive.Notes.
If I group by tbl_MainNotesArchive.Notes, I get an error
on the ASP page as stated below.([SQL Server]Subquery
returned more than 1 value, etc...)

This SQL statement retrives the records properly and is
essentially the same thing I want from the SP, but the SP
acts different:
SELECT tbl_MainArchive.ID,
tbl_MainArchive.BornOnDateTime,
tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
tbl_MainArchive.SerialNumber,
tbl_MainArchive.IssueContext,
tbl_MainArchive.IssueDetails,
tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
tbl_MainArchive.CurrOwnerName,
tbl_MainArchive.LastAccessed,
tbl_MainArchive.LastAccessedBy,
tbl_MainArchive.LastUpdated,
tbl_MainArchive.LastUpdatedBy " & _
"FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive ON
tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID " & _
"WHERE (((tbl_MainArchive.ID) Like '%" & fn_search
& "%')) OR (((tbl_MainArchive.ModelNumber) Like '%" &
fn_search & "%')) OR (((tbl_MainArchive.SerialNumber)
Like '%" & fn_search & "%')) OR (((tbl_MainArchive.CSO)
Like '%" & fn_search & "%')) OR
(((tbl_MainArchive.CreatedBY) Like '%" & fn_search
& "%')) OR (((tbl_MainArchive.CurrOwnerName) Like '%" &
fn_search & "%')) OR (((tbl_MainNotesArchive.Notes)
Like '%" & fn_search & "%')) " & _
"Group By tbl_MainArchive.ID,
tbl_MainArchive.BornOnDateTime,
tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
tbl_MainArchive.SerialNumber,
tbl_MainArchive.IssueContext,
tbl_MainArchive.IssueDetails,
tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
tbl_MainArchive.CurrOwnerName,
tbl_MainArchive.LastAccessed,
tbl_MainArchive.LastAccessedBy,
tbl_MainArchive.LastUpdated,
tbl_MainArchive.LastUpdatedBy " & _
"ORDER By tbl_MainArchive.BornOnDateTime"

>-----Original Message-----
>What column you want to get a count of?

>>-----Original Message-----
>>The following SP below works OK, but it returns too
many
>>rows. Im just trying to get a count from 2 joined
tables.
>>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to
group
>>the fields (which works as a normal SQL statement), I
get
>>an error: "][SQL Server]Subquery returned more than 1
>>value. This is not permitted when the subquery follows
>>=, !=, <, <= , >, >= or when the subquery is used as an
>>expression." on the ASP page. I am kinda new to complex
>>SPs and an under a deadline!!!

>>Works, but too many counts...
>>CREATE PROCEDURE sp_GetArchiveSearchCount


>>AS

>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>WHERE
>>(                                          


+'%')
>>OR

+'%')
>>OR

>>)
>>Group By tbl_MainArchive.ID
>>)
>>GO

>>This version fails...
>>CREATE PROCEDURE sp_GetArchiveSearchCount


>>AS

>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>WHERE
>>(                                          


+'%')
>>OR

+'%')
>>OR

>>)
>>Group By

tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes

- Show quoted text -

Quote:>>)
>>GO

>>.

>.

 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Edgardo Valde » Wed, 18 Jun 2003 00:59:37


This may not be very elegant, however, if you just want
the count of rows of the query, you can execute the exact

>-----Original Message-----
>I want ONLY a count of tbl_MainArchive.ID Where the
>search criteria may be found in either tbl_MainArchive,
>or in tbl_MainNotesArchive.Notes. All went hywire when I
>linked the Notes field in the Join.
>I tried Count(*), Count(tbl_MainArchive.ID), Count
>(tbl_MainNotesArchive.ID) AS colCount.

>This last one seems to work, but I can seem to get the
>OUTPUT var to = colCount

>SP are the bane of my existance. I think I am in over my
>head! But they are so freakin' fast! The dB I have built
>will grow to 100,000s of records in a very short while,
>so this search feature must use SPs.

>I know there is a problem with the logic. tbl_MainArchive
>has unique IDs that are tied to "caseID" in
>tbl_MainNotesArchive. ie: There can be multiple notes
>tied to each unique record in tbl_MainArchive. Simple for
>a standard SQL statement, but if there are, say, 3 notes
>linked to tbl_MainArchive.ID, the SP returns 3, instead
>of 1, even if the criteria is not found in
>tbl_MainNotesArchive.Notes.
>If I group by tbl_MainNotesArchive.Notes, I get an error
>on the ASP page as stated below.([SQL Server]Subquery
>returned more than 1 value, etc...)

>This SQL statement retrives the records properly and is
>essentially the same thing I want from the SP, but the SP
>acts different:
>SELECT tbl_MainArchive.ID,
>tbl_MainArchive.BornOnDateTime,
>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>tbl_MainArchive.SerialNumber,
>tbl_MainArchive.IssueContext,
>tbl_MainArchive.IssueDetails,
>tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>tbl_MainArchive.CurrOwnerName,
>tbl_MainArchive.LastAccessed,
>tbl_MainArchive.LastAccessedBy,
>tbl_MainArchive.LastUpdated,
>tbl_MainArchive.LastUpdatedBy " & _
>"FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive ON
>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID " & _
>"WHERE (((tbl_MainArchive.ID) Like '%" & fn_search
>& "%')) OR (((tbl_MainArchive.ModelNumber) Like '%" &
>fn_search & "%')) OR (((tbl_MainArchive.SerialNumber)
>Like '%" & fn_search & "%')) OR (((tbl_MainArchive.CSO)
>Like '%" & fn_search & "%')) OR
>(((tbl_MainArchive.CreatedBY) Like '%" & fn_search
>& "%')) OR (((tbl_MainArchive.CurrOwnerName) Like '%" &
>fn_search & "%')) OR (((tbl_MainNotesArchive.Notes)
>Like '%" & fn_search & "%')) " & _
>"Group By tbl_MainArchive.ID,
>tbl_MainArchive.BornOnDateTime,
>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>tbl_MainArchive.SerialNumber,
>tbl_MainArchive.IssueContext,
>tbl_MainArchive.IssueDetails,
>tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>tbl_MainArchive.CurrOwnerName,
>tbl_MainArchive.LastAccessed,
>tbl_MainArchive.LastAccessedBy,
>tbl_MainArchive.LastUpdated,
>tbl_MainArchive.LastUpdatedBy " & _
>"ORDER By tbl_MainArchive.BornOnDateTime"

>>-----Original Message-----
>>What column you want to get a count of?

>>>-----Original Message-----
>>>The following SP below works OK, but it returns too
>many
>>>rows. Im just trying to get a count from 2 joined
>tables.
>>>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to
>group
>>>the fields (which works as a normal SQL statement), I
>get
>>>an error: "][SQL Server]Subquery returned more than 1
>>>value. This is not permitted when the subquery follows
>>>=, !=, <, <= , >, >= or when the subquery is used as an
>>>expression." on the ASP page. I am kinda new to complex
>>>SPs and an under a deadline!!!

>>>Works, but too many counts...
>>>CREATE PROCEDURE sp_GetArchiveSearchCount


>>>AS

>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>WHERE
>>>(                                      


>+'%')
>>>OR

>+'%')
>>>OR

>>>)
>>>Group By tbl_MainArchive.ID
>>>)
>>>GO

>>>This version fails...
>>>CREATE PROCEDURE sp_GetArchiveSearchCount


>>>AS

>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>WHERE
>>>(                                      


>+'%')
>>>OR

>+'%')
>>>OR

>>>)
>>>Group By
>tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
>>>)
>>>GO

>>>.

>>.

>.

 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Chad Mille » Wed, 18 Jun 2003 01:24:22


Thanks for your help. I've got a lot to learn.  Still
pulling my hair out.  I think I need to go home. Can you
help me with 1 more thing?

I wrote this:

CREATE PROCEDURE sp_GetArchiveSearchCount
@fn_Search      nVarChar(50)

AS

Declare @intArchiveCount int
SET @intArchiveCount =0

SELECT tbl_MainArchive.ID,
tbl_MainArchive.BornOnDateTime,
tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
tbl_MainArchive.SerialNumber,
tbl_MainArchive.IssueContext,
tbl_MainArchive.IssueDetails,
tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
tbl_MainArchive.CurrOwnerName,
tbl_MainArchive.LastAccessed,
tbl_MainArchive.LastAccessedBy,
tbl_MainArchive.LastUpdated, tbl_MainArchive.LastUpdatedBy
FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive ON
tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
WHERE
(((tbl_MainArchive.ID) Like '%'+ @fn_Search + '%')) OR
(((tbl_MainArchive.ModelNumber) Like '%' + @fn_search
+ '%')) OR
(((tbl_MainArchive.SerialNumber) Like '%' + @fn_search
+ '%')) OR
(((tbl_MainArchive.CSO) Like '%' + @fn_search + '%')) OR
(((tbl_MainArchive.CreatedBY) Like '%' + @fn_search
+ '%')) OR
(((tbl_MainArchive.CurrOwnerName) Like '%' + @fn_search
+ '%')) OR
(((tbl_MainNotesArchive.Notes) Like '%' + @fn_search
+ '%'))
GROUP By tbl_MainArchive.ID,
tbl_MainArchive.BornOnDateTime,
tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
tbl_MainArchive.SerialNumber,
tbl_MainArchive.IssueContext,
tbl_MainArchive.IssueDetails,
tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
tbl_MainArchive.CurrOwnerName,
tbl_MainArchive.LastAccessed,
tbl_MainArchive.LastAccessedBy,
tbl_MainArchive.LastUpdated, tbl_MainArchive.LastUpdatedBy

SET @intArchiveCount = @@ROWCOUNT
Return @intArchiveCount
GO

Now I get, "[SQL Server]Procedure or function
sp_GetArchiveSearchCount has too many arguments
specified."

I'm scouring the Help file but I think my worries of
looking like a bafoon are futile...

Thanks again!

>-----Original Message-----
>This may not be very elegant, however, if you just want
>the count of rows of the query, you can execute the
exact
>same query inside the sp, and immediately after store
the
>@@ROWCOUNT on @ArchiveCount and be done with it.

>>-----Original Message-----
>>I want ONLY a count of tbl_MainArchive.ID Where the
>>search criteria may be found in either tbl_MainArchive,
>>or in tbl_MainNotesArchive.Notes. All went hywire when
I
>>linked the Notes field in the Join.
>>I tried Count(*), Count(tbl_MainArchive.ID), Count
>>(tbl_MainNotesArchive.ID) AS colCount.

>>This last one seems to work, but I can seem to get the
>>OUTPUT var to = colCount

>>SP are the bane of my existance. I think I am in over
my
>>head! But they are so freakin' fast! The dB I have
built
>>will grow to 100,000s of records in a very short while,
>>so this search feature must use SPs.

>>I know there is a problem with the logic.
tbl_MainArchive
>>has unique IDs that are tied to "caseID" in
>>tbl_MainNotesArchive. ie: There can be multiple notes
>>tied to each unique record in tbl_MainArchive. Simple
for
>>a standard SQL statement, but if there are, say, 3
notes
>>linked to tbl_MainArchive.ID, the SP returns 3, instead
>>of 1, even if the criteria is not found in
>>tbl_MainNotesArchive.Notes.
>>If I group by tbl_MainNotesArchive.Notes, I get an
error
>>on the ASP page as stated below.([SQL Server]Subquery
>>returned more than 1 value, etc...)

>>This SQL statement retrives the records properly and is
>>essentially the same thing I want from the SP, but the
SP
>>acts different:
>>SELECT tbl_MainArchive.ID,
>>tbl_MainArchive.BornOnDateTime,
>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>tbl_MainArchive.SerialNumber,
>>tbl_MainArchive.IssueContext,
>>tbl_MainArchive.IssueDetails,
>>tbl_MainArchive.ProductType,

tbl_MainArchive.CaseStatus,

- Show quoted text -

>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>tbl_MainArchive.CurrOwnerName,
>>tbl_MainArchive.LastAccessed,
>>tbl_MainArchive.LastAccessedBy,
>>tbl_MainArchive.LastUpdated,
>>tbl_MainArchive.LastUpdatedBy " & _
>>"FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive
ON
>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID " & _
>>"WHERE (((tbl_MainArchive.ID) Like '%" & fn_search
>>& "%')) OR (((tbl_MainArchive.ModelNumber) Like '%" &
>>fn_search & "%')) OR (((tbl_MainArchive.SerialNumber)
>>Like '%" & fn_search & "%')) OR (((tbl_MainArchive.CSO)
>>Like '%" & fn_search & "%')) OR
>>(((tbl_MainArchive.CreatedBY) Like '%" & fn_search
>>& "%')) OR (((tbl_MainArchive.CurrOwnerName) Like '%" &
>>fn_search & "%')) OR (((tbl_MainNotesArchive.Notes)
>>Like '%" & fn_search & "%')) " & _
>>"Group By tbl_MainArchive.ID,
>>tbl_MainArchive.BornOnDateTime,
>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>tbl_MainArchive.SerialNumber,
>>tbl_MainArchive.IssueContext,
>>tbl_MainArchive.IssueDetails,
>>tbl_MainArchive.ProductType,

tbl_MainArchive.CaseStatus,

- Show quoted text -

>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>tbl_MainArchive.CurrOwnerName,
>>tbl_MainArchive.LastAccessed,
>>tbl_MainArchive.LastAccessedBy,
>>tbl_MainArchive.LastUpdated,
>>tbl_MainArchive.LastUpdatedBy " & _
>>"ORDER By tbl_MainArchive.BornOnDateTime"

>>>-----Original Message-----
>>>What column you want to get a count of?

>>>>-----Original Message-----
>>>>The following SP below works OK, but it returns too
>>many
>>>>rows. Im just trying to get a count from 2 joined
>>tables.
>>>>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to
>>group
>>>>the fields (which works as a normal SQL statement), I
>>get
>>>>an error: "][SQL Server]Subquery returned more than 1
>>>>value. This is not permitted when the subquery
follows
>>>>=, !=, <, <= , >, >= or when the subquery is used as
an
>>>>expression." on the ASP page. I am kinda new to
complex
>>>>SPs and an under a deadline!!!

>>>>Works, but too many counts...
>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>@fn_Search      nVarChar(50),
>>>>@ArchiveCount int       OUTPUT
>>>>AS
>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>WHERE
>>>>(                                          
>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>+'%')
>>>>OR
>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>+'%')
>>>>OR
>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
+'%')
>>>>)
>>>>Group By tbl_MainArchive.ID
>>>>)
>>>>GO

>>>>This version fails...
>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>@fn_Search      nVarChar(50),
>>>>@ArchiveCount int       OUTPUT
>>>>AS
>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>WHERE
>>>>(                                          
>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>+'%')
>>>>OR
>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>+'%')
>>>>OR
>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
+'%')
>>>>)
>>>>Group By
>>tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
>>>>)
>>>>GO

>>>>.

>>>.

>>.

>.

 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Edgardo Valde » Wed, 18 Jun 2003 01:35:27


You are welcome. I am glad of being of any help.

Try using this:

CREATE PROCEDURE sp_GetArchiveSearchCount
(@fn_Search     nVarChar(50))
as
......(the rest..)

>-----Original Message-----
>Thanks for your help. I've got a lot to learn.  Still
>pulling my hair out.  I think I need to go home. Can you
>help me with 1 more thing?

>I wrote this:

>CREATE PROCEDURE sp_GetArchiveSearchCount
>@fn_Search  nVarChar(50)

>AS

>Declare @intArchiveCount int
>SET @intArchiveCount =0

>SELECT tbl_MainArchive.ID,
>tbl_MainArchive.BornOnDateTime,
>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>tbl_MainArchive.SerialNumber,
>tbl_MainArchive.IssueContext,
>tbl_MainArchive.IssueDetails,
>tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>tbl_MainArchive.CurrOwnerName,
>tbl_MainArchive.LastAccessed,
>tbl_MainArchive.LastAccessedBy,
>tbl_MainArchive.LastUpdated, tbl_MainArchive.LastUpdatedBy
>FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive ON
>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>WHERE
>(((tbl_MainArchive.ID) Like '%'+ @fn_Search + '%')) OR
>(((tbl_MainArchive.ModelNumber) Like '%' + @fn_search
>+ '%')) OR
>(((tbl_MainArchive.SerialNumber) Like '%' + @fn_search
>+ '%')) OR
>(((tbl_MainArchive.CSO) Like '%' + @fn_search + '%')) OR
>(((tbl_MainArchive.CreatedBY) Like '%' + @fn_search
>+ '%')) OR
>(((tbl_MainArchive.CurrOwnerName) Like '%' + @fn_search
>+ '%')) OR
>(((tbl_MainNotesArchive.Notes) Like '%' + @fn_search
>+ '%'))
>GROUP By tbl_MainArchive.ID,
>tbl_MainArchive.BornOnDateTime,
>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>tbl_MainArchive.SerialNumber,
>tbl_MainArchive.IssueContext,
>tbl_MainArchive.IssueDetails,
>tbl_MainArchive.ProductType, tbl_MainArchive.CaseStatus,
>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>tbl_MainArchive.CurrOwnerName,
>tbl_MainArchive.LastAccessed,
>tbl_MainArchive.LastAccessedBy,
>tbl_MainArchive.LastUpdated, tbl_MainArchive.LastUpdatedBy

>SET @intArchiveCount = @@ROWCOUNT
>Return @intArchiveCount
>GO

>Now I get, "[SQL Server]Procedure or function
>sp_GetArchiveSearchCount has too many arguments
>specified."

>I'm scouring the Help file but I think my worries of
>looking like a bafoon are futile...

>Thanks again!

>>-----Original Message-----
>>This may not be very elegant, however, if you just want
>>the count of rows of the query, you can execute the
>exact
>>same query inside the sp, and immediately after store
>the
>>@@ROWCOUNT on @ArchiveCount and be done with it.

>>>-----Original Message-----
>>>I want ONLY a count of tbl_MainArchive.ID Where the
>>>search criteria may be found in either tbl_MainArchive,
>>>or in tbl_MainNotesArchive.Notes. All went hywire when
>I
>>>linked the Notes field in the Join.
>>>I tried Count(*), Count(tbl_MainArchive.ID), Count
>>>(tbl_MainNotesArchive.ID) AS colCount.

>>>This last one seems to work, but I can seem to get the
>>>OUTPUT var to = colCount

>>>SP are the bane of my existance. I think I am in over
>my
>>>head! But they are so freakin' fast! The dB I have
>built
>>>will grow to 100,000s of records in a very short while,
>>>so this search feature must use SPs.

>>>I know there is a problem with the logic.
>tbl_MainArchive
>>>has unique IDs that are tied to "caseID" in
>>>tbl_MainNotesArchive. ie: There can be multiple notes
>>>tied to each unique record in tbl_MainArchive. Simple
>for
>>>a standard SQL statement, but if there are, say, 3
>notes
>>>linked to tbl_MainArchive.ID, the SP returns 3, instead
>>>of 1, even if the criteria is not found in
>>>tbl_MainNotesArchive.Notes.
>>>If I group by tbl_MainNotesArchive.Notes, I get an
>error
>>>on the ASP page as stated below.([SQL Server]Subquery
>>>returned more than 1 value, etc...)

>>>This SQL statement retrives the records properly and is
>>>essentially the same thing I want from the SP, but the
>SP
>>>acts different:
>>>SELECT tbl_MainArchive.ID,
>>>tbl_MainArchive.BornOnDateTime,
>>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>>tbl_MainArchive.SerialNumber,
>>>tbl_MainArchive.IssueContext,
>>>tbl_MainArchive.IssueDetails,
>>>tbl_MainArchive.ProductType,
>tbl_MainArchive.CaseStatus,
>>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>>tbl_MainArchive.CurrOwnerName,
>>>tbl_MainArchive.LastAccessed,
>>>tbl_MainArchive.LastAccessedBy,
>>>tbl_MainArchive.LastUpdated,
>>>tbl_MainArchive.LastUpdatedBy " & _
>>>"FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive
>ON
>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID " & _
>>>"WHERE (((tbl_MainArchive.ID) Like '%" & fn_search
>>>& "%')) OR (((tbl_MainArchive.ModelNumber) Like '%" &
>>>fn_search & "%')) OR (((tbl_MainArchive.SerialNumber)
>>>Like '%" & fn_search & "%')) OR (((tbl_MainArchive.CSO)
>>>Like '%" & fn_search & "%')) OR
>>>(((tbl_MainArchive.CreatedBY) Like '%" & fn_search
>>>& "%')) OR (((tbl_MainArchive.CurrOwnerName) Like '%" &
>>>fn_search & "%')) OR (((tbl_MainNotesArchive.Notes)
>>>Like '%" & fn_search & "%')) " & _
>>>"Group By tbl_MainArchive.ID,
>>>tbl_MainArchive.BornOnDateTime,
>>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>>tbl_MainArchive.SerialNumber,
>>>tbl_MainArchive.IssueContext,
>>>tbl_MainArchive.IssueDetails,
>>>tbl_MainArchive.ProductType,
>tbl_MainArchive.CaseStatus,
>>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>>tbl_MainArchive.CurrOwnerName,
>>>tbl_MainArchive.LastAccessed,
>>>tbl_MainArchive.LastAccessedBy,
>>>tbl_MainArchive.LastUpdated,
>>>tbl_MainArchive.LastUpdatedBy " & _
>>>"ORDER By tbl_MainArchive.BornOnDateTime"

>>>>-----Original Message-----
>>>>What column you want to get a count of?

>>>>>-----Original Message-----
>>>>>The following SP below works OK, but it returns too
>>>many
>>>>>rows. Im just trying to get a count from 2 joined
>>>tables.
>>>>>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to
>>>group
>>>>>the fields (which works as a normal SQL statement), I
>>>get
>>>>>an error: "][SQL Server]Subquery returned more than 1
>>>>>value. This is not permitted when the subquery
>follows
>>>>>=, !=, <, <= , >, >= or when the subquery is used as
>an
>>>>>expression." on the ASP page. I am kinda new to
>complex
>>>>>SPs and an under a deadline!!!

>>>>>Works, but too many counts...
>>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>>@fn_Search  nVarChar(50),
>>>>>@ArchiveCount int   OUTPUT
>>>>>AS
>>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>>WHERE
>>>>>(                                      
>>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>>+'%')
>>>>>OR
>>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>>+'%')
>>>>>OR
>>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
>+'%')
>>>>>)
>>>>>Group By tbl_MainArchive.ID
>>>>>)
>>>>>GO

>>>>>This version fails...
>>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>>@fn_Search  nVarChar(50),
>>>>>@ArchiveCount int   OUTPUT
>>>>>AS
>>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>>WHERE
>>>>>(                                      
>>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>>+'%')
>>>>>OR
>>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>>+'%')
>>>>>OR
>>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
>+'%')
>>>>>)
>>>>>Group By
>>>tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
>>>>>)
>>>>>GO

>>>>>.

>>>>.

>>>.

>>.

>.

 
 
 

Stored Procedure behavior [newbie] HELP!!!

Post by Chad Mille » Wed, 18 Jun 2003 01:44:13


BINGO!

I forgot I was passing the Output parameter from the ASP
page when I cleared it from the SP. The SP was looking
for this parameter.

It is ugly, but it seems to work properly now. Thanks a
ton!

Chad

>-----Original Message-----
>You are welcome. I am glad of being of any help.

>Try using this:

>CREATE PROCEDURE sp_GetArchiveSearchCount
>(@fn_Search nVarChar(50))
>as
>.......(the rest..)

>>-----Original Message-----
>>Thanks for your help. I've got a lot to learn.  Still
>>pulling my hair out.  I think I need to go home. Can
you
>>help me with 1 more thing?

>>I wrote this:

>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>@fn_Search      nVarChar(50)

>>AS

>>Declare @intArchiveCount int
>>SET @intArchiveCount =0

>>SELECT tbl_MainArchive.ID,
>>tbl_MainArchive.BornOnDateTime,
>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>tbl_MainArchive.SerialNumber,
>>tbl_MainArchive.IssueContext,
>>tbl_MainArchive.IssueDetails,
>>tbl_MainArchive.ProductType,

tbl_MainArchive.CaseStatus,
>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>tbl_MainArchive.CurrOwnerName,
>>tbl_MainArchive.LastAccessed,
>>tbl_MainArchive.LastAccessedBy,
>>tbl_MainArchive.LastUpdated,

tbl_MainArchive.LastUpdatedBy

- Show quoted text -

>>FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive ON
>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>WHERE
>>(((tbl_MainArchive.ID) Like '%'+ @fn_Search + '%')) OR
>>(((tbl_MainArchive.ModelNumber) Like '%' + @fn_search
>>+ '%')) OR
>>(((tbl_MainArchive.SerialNumber) Like '%' + @fn_search
>>+ '%')) OR
>>(((tbl_MainArchive.CSO) Like '%' + @fn_search + '%'))
OR
>>(((tbl_MainArchive.CreatedBY) Like '%' + @fn_search
>>+ '%')) OR
>>(((tbl_MainArchive.CurrOwnerName) Like '%' + @fn_search
>>+ '%')) OR
>>(((tbl_MainNotesArchive.Notes) Like '%' + @fn_search
>>+ '%'))
>>GROUP By tbl_MainArchive.ID,
>>tbl_MainArchive.BornOnDateTime,
>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>tbl_MainArchive.SerialNumber,
>>tbl_MainArchive.IssueContext,
>>tbl_MainArchive.IssueDetails,
>>tbl_MainArchive.ProductType,

tbl_MainArchive.CaseStatus,
>>tbl_MainArchive.Disposition, tbl_MainArchive.CurrOwner,
>>tbl_MainArchive.CurrOwnerName,
>>tbl_MainArchive.LastAccessed,
>>tbl_MainArchive.LastAccessedBy,
>>tbl_MainArchive.LastUpdated,

tbl_MainArchive.LastUpdatedBy

- Show quoted text -

>>SET @intArchiveCount = @@ROWCOUNT
>>Return @intArchiveCount
>>GO

>>Now I get, "[SQL Server]Procedure or function
>>sp_GetArchiveSearchCount has too many arguments
>>specified."

>>I'm scouring the Help file but I think my worries of
>>looking like a bafoon are futile...

>>Thanks again!

>>>-----Original Message-----
>>>This may not be very elegant, however, if you just
want
>>>the count of rows of the query, you can execute the
>>exact
>>>same query inside the sp, and immediately after store
>>the
>>>@@ROWCOUNT on @ArchiveCount and be done with it.

>>>>-----Original Message-----
>>>>I want ONLY a count of tbl_MainArchive.ID Where the
>>>>search criteria may be found in either
tbl_MainArchive,
>>>>or in tbl_MainNotesArchive.Notes. All went hywire
when
>>I
>>>>linked the Notes field in the Join.
>>>>I tried Count(*), Count(tbl_MainArchive.ID), Count
>>>>(tbl_MainNotesArchive.ID) AS colCount.

>>>>This last one seems to work, but I can seem to get
the
>>>>OUTPUT var to = colCount

>>>>SP are the bane of my existance. I think I am in over
>>my
>>>>head! But they are so freakin' fast! The dB I have
>>built
>>>>will grow to 100,000s of records in a very short
while,
>>>>so this search feature must use SPs.

>>>>I know there is a problem with the logic.
>>tbl_MainArchive
>>>>has unique IDs that are tied to "caseID" in
>>>>tbl_MainNotesArchive. ie: There can be multiple notes
>>>>tied to each unique record in tbl_MainArchive. Simple
>>for
>>>>a standard SQL statement, but if there are, say, 3
>>notes
>>>>linked to tbl_MainArchive.ID, the SP returns 3,
instead
>>>>of 1, even if the criteria is not found in
>>>>tbl_MainNotesArchive.Notes.
>>>>If I group by tbl_MainNotesArchive.Notes, I get an
>>error
>>>>on the ASP page as stated below.([SQL Server]Subquery
>>>>returned more than 1 value, etc...)

>>>>This SQL statement retrives the records properly and
is
>>>>essentially the same thing I want from the SP, but
the
>>SP
>>>>acts different:
>>>>SELECT tbl_MainArchive.ID,
>>>>tbl_MainArchive.BornOnDateTime,
>>>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>>>tbl_MainArchive.SerialNumber,
>>>>tbl_MainArchive.IssueContext,
>>>>tbl_MainArchive.IssueDetails,
>>>>tbl_MainArchive.ProductType,
>>tbl_MainArchive.CaseStatus,
>>>>tbl_MainArchive.Disposition,

tbl_MainArchive.CurrOwner,
>>>>tbl_MainArchive.CurrOwnerName,
>>>>tbl_MainArchive.LastAccessed,
>>>>tbl_MainArchive.LastAccessedBy,
>>>>tbl_MainArchive.LastUpdated,
>>>>tbl_MainArchive.LastUpdatedBy " & _
>>>>"FROM tbl_MainNotesArchive INNER JOIN tbl_MainArchive
>>ON
>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID " & _
>>>>"WHERE (((tbl_MainArchive.ID) Like '%" & fn_search
>>>>& "%')) OR (((tbl_MainArchive.ModelNumber) Like '%" &
>>>>fn_search & "%')) OR (((tbl_MainArchive.SerialNumber)
>>>>Like '%" & fn_search & "%')) OR

(((tbl_MainArchive.CSO)

- Show quoted text -

>>>>Like '%" & fn_search & "%')) OR
>>>>(((tbl_MainArchive.CreatedBY) Like '%" & fn_search
>>>>& "%')) OR (((tbl_MainArchive.CurrOwnerName) Like '%"
&
>>>>fn_search & "%')) OR (((tbl_MainNotesArchive.Notes)
>>>>Like '%" & fn_search & "%')) " & _
>>>>"Group By tbl_MainArchive.ID,
>>>>tbl_MainArchive.BornOnDateTime,
>>>>tbl_MainArchive.CreatedBY, tbl_MainArchive.Entity,
>>>>tbl_MainArchive.CSO, tbl_MainArchive.ModelNumber,
>>>>tbl_MainArchive.SerialNumber,
>>>>tbl_MainArchive.IssueContext,
>>>>tbl_MainArchive.IssueDetails,
>>>>tbl_MainArchive.ProductType,
>>tbl_MainArchive.CaseStatus,
>>>>tbl_MainArchive.Disposition,

tbl_MainArchive.CurrOwner,

- Show quoted text -

>>>>tbl_MainArchive.CurrOwnerName,
>>>>tbl_MainArchive.LastAccessed,
>>>>tbl_MainArchive.LastAccessedBy,
>>>>tbl_MainArchive.LastUpdated,
>>>>tbl_MainArchive.LastUpdatedBy " & _
>>>>"ORDER By tbl_MainArchive.BornOnDateTime"

>>>>>-----Original Message-----
>>>>>What column you want to get a count of?

>>>>>>-----Original Message-----
>>>>>>The following SP below works OK, but it returns too
>>>>many
>>>>>>rows. Im just trying to get a count from 2 joined
>>>>tables.
>>>>>>If I add a GROUP BY (tbl_MainNotesArchive.Notes) to
>>>>group
>>>>>>the fields (which works as a normal SQL statement),
I
>>>>get
>>>>>>an error: "][SQL Server]Subquery returned more than
1
>>>>>>value. This is not permitted when the subquery
>>follows
>>>>>>=, !=, <, <= , >, >= or when the subquery is used
as
>>an
>>>>>>expression." on the ASP page. I am kinda new to
>>complex
>>>>>>SPs and an under a deadline!!!

>>>>>>Works, but too many counts...
>>>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>>>@fn_Search      nVarChar(50),
>>>>>>@ArchiveCount int       OUTPUT
>>>>>>AS
>>>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>>>WHERE
>>>>>>(                                          
>>>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>>>+'%')
>>>>>>OR
>>>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>>>+'%')
>>>>>>OR
>>>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
>>+'%')
>>>>>>)
>>>>>>Group By tbl_MainArchive.ID
>>>>>>)
>>>>>>GO

>>>>>>This version fails...
>>>>>>CREATE PROCEDURE sp_GetArchiveSearchCount
>>>>>>@fn_Search      nVarChar(50),
>>>>>>@ArchiveCount int       OUTPUT
>>>>>>AS
>>>>>>SELECT @ArchiveCount = (Select Count(*) From
>>>>>>tbl_MainNotesArchive JOIN tbl_MainArchive ON
>>>>>>tbl_MainNotesArchive.CaseID = tbl_MainArchive.ID
>>>>>>WHERE
>>>>>>(                                          
>>>>>>(tbl_MainArchive.ID LIKE '%'+ @fn_Search +'%') OR
>>>>>>(tbl_MainArchive.ModelNumber Like  '%'+ @fn_Search
>>>>+'%')
>>>>>>OR
>>>>>>(tbl_MainArchive.SerialNumber Like  '%'+ @fn_Search
>>>>+'%')
>>>>>>OR
>>>>>>(tbl_MainNotesArchive.Notes Like  '%'+ @fn_Search
>>+'%')
>>>>>>)
>>>>>>Group By
>>>>tbl_MainArchive.ID, ...tbl_MainNotesArchive.Notes
>>>>>>)
>>>>>>GO

>>>>>>.

>>>>>.

>>>>.

>>>.

>>.

>.

 
 
 

1. newbie help with stored procedure

I have written a small stored procedure as coded below:

SELECT InquiryID, LastName, FirstName, City, StateCountry, PostalCode,

I' m running vb and here is the vb code below. I get an error "Invalid
character value for cast specification) .Can anyone suggest what the
problem is?
 LastName is a value in the table defined as VarChar (20)   *** don't
mind the syntax for loading the list box, I'm using ListPro and that's
what works.

Dim rs As New ADODB.Recordset

On Error GoTo ErrorCheck

rs.CursorLocation = adUseClient
rs.Open "SelectInquiryByName(" & txtSearchName & ")", oConn, _
adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

lbNames.Clear    'clear list box and load from recordset
While Not rs.EOF
   lbNames.AddItem rs("LastName") & Chr$(9) & rs("firstName") &
Chr$(9) & rs("City") & Chr$(9) & rs("State/Country") & Chr$(9) &
rs("PostalCode") & Chr$(9) & rs("phone") & Chr$(9) & rs("inquiryID")
   rs.MoveNext
Wend

rs.Close
Set rs = Nothing

lblCurSearchName = "Current Search Name is " & Me.txtSearchName
txtSearchName = ""

Exit Sub
ErrorCheck:
   MsgBox "Error in frmGetInquiry, cmdSearch Click " &
Err.Description, vbCritical, "Error"

2. Support from a vendor

3. Newbie Q: Need help creating a stored procedure

4. DATETIME bug

5. Text field in stored procedure - help needed (Newbie)

6. ODBC and excel, Help

7. Newbie needs Stored Procedure help!

8. Part-time extra $$$ for RDBMS experts - work from home!

9. Newbie-Stored Procedure Help

10. Newbie needs help with Stored Procedure

11. Stored procedure help - newbie

12. Newbie Help on Stored Procedures

13. Newbie Needs help with Stored Procedures???