April 15, 2009 at 2:50 am
I have this stored proc and what i want to do is check is a record already exists and if it exist return an output parameter
and output it to an aspx page.
If i use the @status varchar(200) OUTPUT
and run the stored proc i get an error :expects parameter '@status', which was not supplied.
If i uncomment it out and just use print like so:
print 'already exists'
I do get a desired result.
Any ideas what i'm missing..
thanks a lot
-------------------------------------------
alter procedure [dbo].[checkComms]
@CommID int,
@username varchar(50),
@status varchar(200) OUTPUT
as
if exists
(SELECT 1
FROM [Northwind].[dbo].[Comms]
where storeCommID=@CommID and [UserName] = @username)
begin
set @status ='Already Exists'
print @status
--print 'already exists'
return
end
Insert into [storeComms] (CommID, UserName)
Values (@storeCommID,@UserName)
--print @status
go
April 15, 2009 at 3:13 am
There are a few things to check on the asp side as from what I can see you proc looks fine, other that if something doesn't exist then a null is returned.
So make sure you can accept nulls in the asp side.
Make sure you have declared @status as an Ouput Param on your asp side.
Actually could you show us the code for your proc call?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 3:21 am
Patrick Ige (4/15/2009)
I have this stored proc and what i want to do is check is a record already exists and if it exist return an output parameterand output it to an aspx page.
If i use the @status varchar(200) OUTPUT
and run the stored proc i get an error :expects parameter '@status', which was not supplied.
If i uncomment it out and just use print like so:
print 'already exists'
I do get a desired result.
Any ideas what i'm missing..
thanks a lot
-------------------------------------------
alter procedure [dbo].[checkComms]
@CommID int,
@username varchar(50),
@status varchar(200) OUTPUT
as
if exists
(SELECT 1
FROM [Northwind].[dbo].[Comms]
where storeCommID=@CommID and [UserName] = @username)
begin
set @status ='Already Exists'
print @status
--print 'already exists'
return
end
Insert into [storeComms] (CommID, UserName)
Values (@storeCommID,@UserName)
--print @status
go
alter procedure [dbo].[checkComms]
@CommID int,
@username varchar(50),
@status varchar(200) OUTPUT
as
if exists
(SELECT 1
FROM [Northwind].[dbo].[Comms]
where storeCommID=@CommID and [UserName] = @username)
begin
set @status ='Already Exists'
--print 'already exists'
end
Insert into [storeComms] (CommID, UserName)
Values (@storeCommID,@UserName)
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
April 15, 2009 at 3:25 am
I think the OP wanted the return there so that no records are inserted...
otherwise good move taking it out but that shouldn't change the nature of the output param should it?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 4:36 am
Thanks kshitij
But i can't see any difference in the code i posted with the one you posted?
Am i blind 🙂
April 15, 2009 at 4:42 am
the return was removed from within the if statement!
Did that fix your problem?
If it did I would be interested to know why?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 5:09 am
Patrick Ige (4/15/2009)
Thanks kshitijBut i can't see any difference in the code i posted with the one you posted?
Am i blind 🙂
I removed the return statement from the stored procedure. now the stored procedure is perfect for the OUTPUT variable. what you have to do is: on the Data Access layer fetch this output variable as:
command.parameter["OUTPUT variable"].value;
and you will get the expected result.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
April 15, 2009 at 5:49 am
why does .NET fall over when you have a return in your code?
Thanks for the solution, just hoping for an explanation as well
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 6:05 am
krayknot (4/15/2009)
I removed the return statement from the stored procedure.
Why? Now the stored procedure doesn't work - it will insert a row whether it's required or not.
krayknot (4/15/2009)
now the stored procedure is perfect for the OUTPUT variable.
What was wrong with it before?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2009 at 6:18 am
I'm with Chris...
Oh another thing is to maybe SET NOCOUNT ON at the beginning of the proc.
This sounds wierd but I have seen some ASP and PHP code fall over when it things it's getting multiple result sets.
Worth a try, but like I said please post the ASP code otherwise we can't tell why...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 7:02 am
Christopher Stobbs (4/15/2009)
why does .NET fall over when you have a return in your code?Thanks for the solution, just hoping for an explanation as well
If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure. The same thing you can also do with return but if you will use return then how you will get the result inside the .NET code as there is no OUTPUT varialbe in the stored procedure. Thus, here there is no requirement of the return command.
Then where to use return??
If you want to fetch a dataset from the stored procedure, use return as
Select from table
return
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
April 15, 2009 at 7:24 am
krayknot (4/15/2009)
If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure.
Are you saying that you can only capture one type of output from a stored procedure? What if you want an OUTPUT variable, a result set, and the value returned from the RETURN statement, all from the same call? Are you saying that .NET can't handle this?
krayknot (4/15/2009)
The same thing you can also do with return but if you will use return then how you will get the result inside the .NET code as there is no OUTPUT varialbe in the stored procedure. Thus, here there is no requirement of the return command.
The OP's code has an OUTPUT parameter, his problem is most likely in calling the sproc from his app. He's not interested in the value returned by the RETURN statement. He does however require RETURN in his sproc for it to work correctly.
krayknot (4/15/2009)
Then where to use return??If you want to fetch a dataset from the stored procedure, use return as
Select from table
return
So... .NET can't get a dataset from a stored procedure unless it's got a RETURN statement after the SELECT?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2009 at 7:33 am
Chris Morris (4/15/2009)
krayknot (4/15/2009)
If you are using any variable(s) as an OUTPUT varialbe in stored procedure, it means that variable will carry the output of the stored procedure.Are you saying that you can only capture one type of output from a stored procedure? What if you want an OUTPUT variable, a result set, and the value returned from the RETURN statement, all from the same call? Are you saying that .NET can't handle this?
In my experience it can, though I'm not a .NET expert.
So... .NET can't get a dataset from a stored procedure unless it's got a RETURN statement after the SELECT?
That it definitely can do. I never have a RETURN in my procs and so far I've had no hassles with them in .NET. Of course, I may be doing something wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 7:52 am
kryknot is wrong.
SQL will always return RETURN value. You have no control over it. The only thing you can do is to change the default value of 0 to anything else. And the RETURN keyword is not required to retrieve anything from the database.
On the original problem. You must declare a SQLParamater in your .NET code for your SP output parameter. In SQL the OUTPUT parameter is not output only, If it is declared without a default value then the SQL will treat it as an required parameter and will give you an error if you do not supply it. You must be careful to clear it at the begining of your SQL code because if for whatever reason your .NET code assigns a value to it before you call the SP it will be passed into your SP.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 15, 2009 at 8:14 am
This is how I would write the procedure:
alter procedure [dbo].[checkComms]
@CommID int,
@username varchar(50),
@status varchar(200) OUTPUT
as
begin
set nocount on;
set @status = 'Does Not Exist';
if exists (select 1
from
[Northwind].[dbo].[Comms]
where
storeCommID=@CommID
and [UserName] = @username)
begin
set @status = 'Already Exists';
end
else begin
Insert into [storeComms] (CommID, UserName)
Values (@storeCommID,@UserName)
end
end
What I haven't seen, and was requested, is the SQL being used to call the procedure.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply