Creating a Default Input Parameter

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

SQL Server Development (2000)

Creating a Default Input Parameter

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-05-06 : 13:24:39

I'm using an input parameter in a stored procedure to obtain the corresponding information.

CREATE spExample
@ExParameter VarChar(25)

SELECT Name, Address, Birthday FROM tblExample

WHERE Criteria = @ExParameter

In this example, if there are no "Criteria" fields with the @ExParamter value then no records are returned. In these cases, I'd like to replace @ExParameter with a default parameter value that would assure that a record is returned. The logic of what I'm trying to do is as follows:

1) Run above query
2) If no records are found, then rerun above query WHERE Criteria = 'Default'

I'm sure that this is possible in a stored procedure, but haven't been able to find the correct syntax. I'd appreciate any guidance that you can provide. Thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-06 : 13:34:40

declare @count int

SELECT Name, Address, Birthday FROM tblExample

WHERE Criteria = @ExParameter

select @count = @@rowcount

if (@count = 0)
BEGIN
SELECT Name, Address, Birthday FROM tblExample
WHERE Criteria = 'Default'
END

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-05-06 : 16:55:19

Chad-
Your suggestion definately got me on the right track. Interestingly enough, when there wasn't a @ExParameter match your code returned both a blank and the 'Default' record in SQL Query Analyzer and my ASP.NET code "choked" on it. Therefore, I had to modify the code as follows to get it to work:

CREATE PROCEDURE spExample
@ExParameter varchar(25)

AS

declare @count int

select @count = @@rowcount FROM tblExample
WHERE Criteria = @ExParameter

select @count = @@rowcount

if (@count = 0)
SELECT Name, Address, Birthday FROM tblExample
WHERE Criteria = 'Default'

else
SELECT Name, Address, Birthday FROM tblExample
WHERE Criteria = @ExParameter

GO

I'm not exactly sure why this works and your code didn't. It was also interesting to discover that it stopped working when I removed the second "select @count = @@rowcount" statement. If you have any insights on this, I'd appreciate them. Thanks again for your assistance.

Dave

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-06 : 19:24:14

Is your ASP.NET expecting output parameters?
What error do you get?

Edited by - ValterBorges on 05/06/2003 19:27:28

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-05-07 : 00:02:58

I'm using a DataReader in ASP.NET. I don't get an error in this situation, but the DataReader remains empty. When I tested Chad's code in SQL Query Analyzer with a nonmatching @ExParameter, it listed all the field headings with no data and then appended the same column headings in the result display window with data corresponding with the 'Default' parameter value.

My modified code yielded just one list of field headings and the appropriate values in the SQL Query Analyzer result set for either matching or nonmatching @ExParameters. In addition, the appropriate values were successfully obtained by the DataReader object. I'd appreciate any suggestions. Thanks!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-07 : 03:37:54

Try this:


CREATE PROCEDURE spExample 
@ExParameter varchar(25) 

AS 

DECLARE @count int 

SELECT @count = COUNT(*) FROM tblExample 
WHERE Criteria = @ExParameter 

IF (@count = 0) 
BEGIN
  SELECT Name, Address, Birthday FROM tblExample 
  WHERE Criteria = 'Default' 
END

ELSE
BEGIN 
  SELECT Name, Address, Birthday FROM tblExample 
  WHERE Criteria = @ExParameter 
END

GO

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-07 : 11:41:11

Sorry,

I thought that is what you wanted based on

quote:

1) Run above query
2) If no records are found, then rerun above query WHERE Criteria = 'Default'

You could still get to the second result set through a data reader, you would need to call NextResult on the datareader, then you should get your second result set.

The reason you have to run the second @@rowcount query is because @@rowcount tells you the rows affected by the previous query.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources