Select Stored Procedure w/ Return Value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Stored Procedure w/ Return Value

Author

Topic

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:11:17

I Have a stored procedure w/ 9 Parameters (that I use w/ Vb/Asp.net) that come from 4 different tables.. For a search page. Well I want to be able to take any of the Parameters and select based on it w/ return records and then als return a count of how many records where found by the Select statement.. and then have a return value sent to the program.
Ie.

Create Stp_Search
(
@var 1
.
.
.
@var9
)

as
Select field1 ..... field9
FROM table1 (inner join)...etc.. table 4
WHERE field1= @var1 .... or....Field9 = @var9

Then run the count...

How can I do this??? I am a little confused ...

THANKS ahead of time

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:15:27

@@ROWCOUNT is a system variable that tells you how many rows were affected by the previous statement.

I don't udnerstand what you are confused about the query as what you have so far looks good.

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:22:14

I guess maybe I thought it was harder than it is...

so how do I implement the RowCount? I think I remember, but as a refresher... THANKS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:23:23

DECLARE @rc int

SELECT statement goes here...

SET @rc = @@ROWCOUNT

Then have an output parameter in your stored procedure that outputs @rc. To find out how to do this, please look up CREATE PROC in SQL Server Books Online.

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:23:48

So I would do something like:

IF @@ROWCOUNT = 0
return 0
if @@RowCount = 1
return 1
else
return 2.....

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:24:20

GREAT THANKS' seems we were replying at the same time

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:40:28

Ok Another Issue... w/ my select statement I would think if I put "or" instead of "and" that it would take any items in the variables and find any account w/ that variable in the field? but it's finding more than it should...
Could I be doing it wrong...? Should I not use "or"...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:42:08

Well it just depends on what you want to happen. I can't recommend AND or OR right now as I can't see your system. You'll need to show us examples of what can happen and what you want it to output. Perhaps this article will help you out with constructing a dynamic WHERE clause:

http://www.sqlteam.com/item.asp?ItemID=2077

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:47:08

Ok I will read... Thanks, what can happens is an end user can field in 9 fields, as little as 1, at most nine. which will be sent to the Stored Procedure... and the select statement will take what they put in and 'select' records matching any/all of the variables. and return a record count of what they selected. so the output is just the recordcount (for this page).. the input would be at least 1 field, but can be up to 9... it's a search page...

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 14:49:28

Ok from this article I think I can use this COALESCE function...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 14:51:02

Here is an example of what you want. The article will provide the details about it.

CREATE PROC SomeProc (@var1 int, @var2 int, @var3 int, @rc int OUTPUT)
AS

SET NOCOUNT ON

SELECT *
FROM SomeTable
WHERE
Column1 = COALESCE(@var1, Column1) AND
Column2 = COALESCE(@var2, Column2) AND
Column3 = COALESCE(@var3, Column3)

SET @rc = @@ROWCOUNT

RETURN

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 15:02:37

Ok so the premise w/ the above which is what I have ... that if they don't put anything in (null)then the Stored procedure will ignore it(persey) and go onto the next??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 15:04:20

Yes. So let's say in my example the user has values for @var2 and @var3 but not for @var1. It would then be like this:

SELECT * FROM SomeTable
WHERE Column1 = Column1 AND Column2 = @var2 AND Column3 = @var3

It's kinda like doing WHERE 1 = 1.

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 15:07:02

Gotcha, so it's still not working right; here's my SP..

CREATE PROCEDURE Sp_Search
(
@AcctNum varchar (100),
@CliId varchar (10),
@CLName varchar (25),
@CFName varchar (25),
@Foundation varchar (100),
@Custodian varchar (255),
@MMgr varchar (255),
@Advisor varchar (50),
@PA varchar (3),
@Status varchar (3)
)

AS

SET NOCOUNT ON

DECLARE @rc int

SELECT tblAccounts.ActAccountNum, tblAccounts.ActClientID, tblMasterList.MstLName, tblMasterList.MstFName, tblMasterList.MstFoundationName, tblManagerList.Manager, tblMasterList.MstDTIARep, tblMasterList.MstReconAssignID, tblAccounts.ActAcctStatus
FROM ((tblAccounts INNER JOIN tblManagerList ON tblAccounts.ActMgrID = tblManagerList.MgrID) INNER JOIN tblCustodian ON tblAccounts.ActCustID = tblCustodian.CustIDRSN) INNER JOIN tblMasterList ON tblAccounts.ActClientID = tblMasterList.MstClientID
WHERE tblAccounts.ActAccountNum = COALESCE(@AcctNum, tblAccounts.ActAccountNum) AND tblAccounts.ActClientID = COALESCE(@CliID, tblAccounts.ActClientId) AND tblMasterList.MstLname = COALESCE( @CLName, tblMasterList.MstLName) AND tblMasterList.MstFName = COALESCE(@CFName, tblMasterList.MstFName) AND tblMasterList.MstFoundationName = COALESCE(@Foundation, tblMasterList.MstFoundationName) AND tblCustodian.Custodian = COALESCE(@Custodian, tblCustodian.Custodian) AND
tblManagerList.Manager = COALESCE(@MMgr, tblManagerList.Manager) AND tblMasterlist.MstDTIARep = COALESCE(@Advisor, tblMasterList.MstDTIARep) AND tblMasterlist.MstReconAssignID =COALESCE( @PA, tblMasterlist.MstReconAssignID) AND tblAccounts.ActAcctStatus =COALESCE( @Status, tblAccounts.ActAcctStatus)

SET @rc = @@ROWCOUNT

If (@rc = 0)
Return 0
IF (@rc = 1)
Return 1
If (@rc > 1)
Return 2
GO

Tara, I appreciate your help... I'm new to Sps. I catch on quick though.. and they seem pretty workable...

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 15:08:07

I guess I could have finished... When I run thru Vs.net I get no records.. if I put them as null; then I get an error saying that they are required..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 15:17:46

First off, never prefix a stored procedure with sp_. You receive a performance hit by using this prefix as SQL Server checks the master database first for any stored procedures prefixed with this and then it'll check the user's current database.

Second, I can't read your code very well as it isn't formatted for easy reading.

Third, did you try running the stored procedure inside Query Analyzer to see if it works as intended before running it through your application? That is always best to do so that you know if the problem is with the stored procedure or with the application.

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-17 : 15:31:45

Sorry
I did run the Query Analyzer.. when I ran w/ nulls except 1 field, came back w/ 0 records and no errors, but the exec line said
"EXEC @RC = [TrackingGenX2K].[dbo].[Sp_Search] @AcctNum, @CliId, @CLName, @CFName, @Foundation, @Custodian, @MMgr, @Advisor, @PA, @Status
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TrackingGenX2K.dbo.Sp_Search'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine"

And I'm sorry about the format...
This might be better?
CREATE PROCEDURE Sp_Search
(
@AcctNum varchar (100),
@CliId varchar (10),
@CLName varchar (25),
@CFName varchar (25),
@Foundation varchar (100),
@Custodian varchar (255),
@MMgr varchar (255),
@Advisor varchar (50),
@PA varchar (3),
@Status varchar (3)
)

AS

SET NOCOUNT ON

DECLARE @rc int

SELECT
tblAccounts.ActAccountNum,
tblAccounts.ActClientID,
tblMasterList.MstLName,
tblMasterList.MstFName,
tblMasterList.MstFoundationName,
tblManagerList.Manager,
tblMasterList.MstDTIARep,
tblMasterList.MstReconAssignID,
tblAccounts.ActAcctStatus

FROM ((tblAccounts INNER JOIN tblManagerList ON tblAccounts.ActMgrID = tblManagerList.MgrID)
INNER JOIN tblCustodian ON tblAccounts.ActCustID = tblCustodian.CustIDRSN)
INNER JOIN tblMasterList ON tblAccounts.ActClientID = tblMasterList.MstClientID

WHERE
tblAccounts.ActAccountNum = COALESCE(@AcctNum, tblAccounts.ActAccountNum) AND
tblAccounts.ActClientID = COALESCE(@CliID, tblAccounts.ActClientId) AND
tblMasterList.MstLname = COALESCE( @CLName, tblMasterList.MstLName) AND
tblMasterList.MstFName = COALESCE(@CFName, tblMasterList.MstFName) AND
tblMasterList.MstFoundationName = COALESCE(@Foundation, tblMasterList.MstFoundationName) AND tblCustodian.Custodian = COALESCE(@Custodian, tblCustodian.Custodian) AND
tblManagerList.Manager = COALESCE(@MMgr, tblManagerList.Manager) AND
tblMasterlist.MstDTIARep = COALESCE(@Advisor, tblMasterList.MstDTIARep) AND
tblMasterlist.MstReconAssignID =COALESCE( @PA, tblMasterlist.MstReconAssignID) AND
tblAccounts.ActAcctStatus =COALESCE( @Status, tblAccounts.ActAcctStatus)

SET @rc = @@ROWCOUNT

If (@rc = 0)
Return 0
IF (@rc = 1)
Return 1
If (@rc > 1)
Return 2
GO

I hope this helps... My thought on it; is because there are nulls in the tables w/ a lot of these fields. Could it still be looking at the nulls?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-17 : 15:36:25

You didn't pass any non-null values into the stored procedure using this:

"EXEC @RC = [TrackingGenX2K].[dbo].[Sp_Search] @AcctNum, @CliId, @CLName, @CFName, @Foundation, @Custodian, @MMgr, @Advisor, @PA, @Status
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TrackingGenX2K.dbo.Sp_Search'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine"

And you don't want this:

If (@rc = 0)
Return 0
IF (@rc = 1)
Return 1
If (@rc > 1)
Return 2

What happens if @rc equals 1000? You'd have a lot of lines of code. You just want:

SET @rc = @@ROWCOUNT

RETURN @rc

But I would use an output parameter rather than returning the rowcount as a return code.

Tara

Compkitty
Starting Member

24 Posts

Posted - 2005-02-18 : 08:56:45

Ok, so I haven't changed the name, but there is what I have now...

CREATE PROCEDURE Sp_Search
(
@AcctNum varchar (100),
@CliId varchar (10),
@CLName varchar (25),
@CFName varchar (25),
@Foundation varchar (100),
@Custodian varchar (255),
@MMgr varchar (255),
@Advisor varchar (50),
@PA varchar (3),
@Status varchar (3),
@rc int output
)

AS

SELECT
tblAccounts.ActAccountNum,
tblAccounts.ActClientID,
tblMasterList.MstLName,
tblMasterList.MstFName,
tblMasterList.MstFoundationName,
tblManagerList.Manager,
tblMasterList.MstDTIARep,
tblMasterList.MstReconAssignID,
tblAccounts.ActAcctStatus

FROM ((tblAccounts
INNER JOIN tblManagerList ON tblAccounts.ActMgrID = tblManagerList.MgrID) INNER JOIN tblCustodian ON tblAccounts.ActCustID = tblCustodian.CustIDRSN) INNER JOIN tblMasterList ON tblAccounts.ActClientID = tblMasterList.MstClientID

WHERE
tblAccounts.ActAccountNum = COALESCE(@AcctNum, tblAccounts.ActAccountNum) AND
tblAccounts.ActClientID = COALESCE(@CliID, tblAccounts.ActClientId) AND
tblMasterList.MstLname = COALESCE( @CLName, tblMasterList.MstLName) AND
tblMasterList.MstFName = COALESCE(@CFName, tblMasterList.MstFName) AND
tblMasterList.MstFoundationName = COALESCE(@Foundation, tblMasterList.MstFoundationName) AND
tblCustodian.Custodian = COALESCE(@Custodian, tblCustodian.Custodian) AND
tblManagerList.Manager = COALESCE(@MMgr, tblManagerList.Manager) AND
tblMasterlist.MstDTIARep = COALESCE(@Advisor, tblMasterList.MstDTIARep) AND
tblMasterlist.MstReconAssignID =COALESCE( @PA, tblMasterlist.MstReconAssignID) AND
tblAccounts.ActAcctStatus =COALESCE( @Status, tblAccounts.ActAcctStatus)

SET @rc = @@ROWCOUNT

Return
GO

AND here is my output from Analyzer...

EXEC @RC = [TrackingGenX2K].[dbo].[Sp_Search] @AcctNum, @CliId, @CLName, @CFName, @Foundation, @Custodian, @MMgr, @Advisor, @PA, @Status, @rc OUTPUT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TrackingGenX2K.dbo.Sp_Search'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
PRINT ' Output Parameter(s): '
SELECT @PrnLine = ' @rc = ' + isnull( CONVERT(nvarchar, @rc), '<NULL>' )
PRINT @PrnLine

Also, I have an error:
Server: Msg 134, Level 15, State 1, Line 13
The variable name '@rc' has already been declared. Variable names must be unique within a query batch or stored procedure.

Can you do me a favor and explain the (EXEC Line to me?)
THANKS, I appreciate all ur help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-19 : 02:25:06

That just executes the stored procedure. I don't execute stored procedures the same way as you. I assume you are using the open option in Query Analyzer. I just type my code out, like this:

DECLARE @var1 int
EXEC dbo.StoredProcName @SomeInputVar = 1, @var1 OUTPUT
PRINT @var1

You are getting that @rc error due to the the open option using that same name.

Tara

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2005-02-19 : 04:48:30

Why dont you get it from vb.net? If you use dataset to hold returned data, just count howmany rows it has.

ds.tables(0).rows.count

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources