OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

osql return to check backup success/failure

I am running an osql using input script which runs a database backup. Is
there a way I could get a return of 1 or 0, 0 being success and 1 being
error. I tried using -b option but it returns the entire details of failure
followed by the return number and if i use -o paramater I dont see any
result at all. Can anyone please suggest some solution to this. any help
will be greatly appreciated. Thanks
Message posted via http://www.webservertalk.comTry catching the error in the batch being executed with OSQL and use
RAISERROR to set the DOS variable ERRORLEVEL.
Example:
Create a batch file with the following instructions:
osql -S my_server -E -d master -Q "backup database northwind to blahblah if
@.@.error != 0 raiserror ('Error during backup.', 16, 127)"
echo %ERRORLEVEL%
Execute the batch file and you will see 50000 as the echo of the variable
ERRORLEVEL, because I am specifying a msg_str in RAISERROR. You can create
your own error msg using sp_addmessage.
AMB
"ishaan99 via webservertalk.com" wrote:

> I am running an osql using input script which runs a database backup. Is
> there a way I could get a return of 1 or 0, 0 being success and 1 being
> error. I tried using -b option but it returns the entire details of failur
e
> followed by the return number and if i use -o paramater I dont see any
> result at all. Can anyone please suggest some solution to this. any help
> will be greatly appreciated. Thanks
> --
> Message posted via http://www.webservertalk.com
>|||I forgot to mention that in order to set DOS variable ERRORLEVEL, using
RAISERROR, you have to specify state 127.
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
AMB
"Alejandro Mesa" wrote:
> Try catching the error in the batch being executed with OSQL and use
> RAISERROR to set the DOS variable ERRORLEVEL.
> Example:
> Create a batch file with the following instructions:
> osql -S my_server -E -d master -Q "backup database northwind to blahblah i
f
> @.@.error != 0 raiserror ('Error during backup.', 16, 127)"
> echo %ERRORLEVEL%
> Execute the batch file and you will see 50000 as the echo of the variable
> ERRORLEVEL, because I am specifying a msg_str in RAISERROR. You can create
> your own error msg using sp_addmessage.
>
> AMB
>
> "ishaan99 via webservertalk.com" wrote:
>|||Thanks so much. That helped and worked fine.
Message posted via http://www.webservertalk.com

oSql Error Handling

Can anyone please tell me why my error handling does not work. I am using the following code in a script file which I pass as an input file to oSql:

IF (@.@.ERROR <> 0)
EXIT(1)
ELSE
EXIT(0)

I have also tried labels as written below to no avail:
IF (@.@.ERROR <> 0) GOTO error_control

success_control:
EXIT(0)

error_control:
EXIT(1)

If anyone knows the solution could they please let me know.I've just written a little script as well and it seems that 'EXIT' must be the last statement within the script.

Example:
SET NOCOUNT ON

DECLARE @.rc int
SET @.rc = 0

IF ((SELECT DB_NAME()) = 'pubs') BEGIN
SET @.rc = 99
GOTO COMMON_EXIT
END

SELECT DB_NAME()

COMMON_EXIT:
EXIT(SELECT @.rc)
GO

The EXIT that will set the MS-DOS ERRORLEVEL variable.

Example (1)
C:\Work>osql -E -Styche -isctest.sql -n -h-1
master

0

C:\Work>echo %errorlevel%
0

C:\Work>


Example (2) against Pubs
C:\Work>osql -E -Styche -isctest.sql -n -dpubs -h-1
99

C:\Work>echo %errorlevel%
99

C:\Work>

Also one thing about @.@.ERROR, it is affect by all T-SQL statements.

Example

UPDATE blah blah blah

IF (@.@.ROWCOUNT = 0) BEGIN
IF (@.@.ERROR = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END

@.@.ERROR will always be 0, since the IF statement is successful it will override the @.@.ERROR value set by the UPDATE statement.

UPDATE blah blah blah

SELECT @.Rows = @.@.ROWCOUNT,
@.Err = @.@.ERROR

IF (@.Rows = 0) BEGIN
IF (@.Err = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END