How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer

Jul 20, 2004

I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:

CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO

If I run the query in Query Analyzer directly, I can display @UserName:

DECLARE @UserName as nvarchar(100)

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password

Select @UserName

But how can I display @UserName if I call stored procedure:

exec UserLogin 'email', 'password', ''

I believed it return int, right?

Thanks in advance for help.

View 2 Replies


ADVERTISEMENT

Can I Return Output From Stored Procedure Into An HTA To Display In Browser?

Jul 20, 2005

Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!

View 3 Replies View Related

Return Output Parameter From Stored Procedure?

Feb 6, 2008

I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26

Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End

What am I doing wrong?  How do I properly capture the QuestionID from the SP?  Running the SP from command line works fine.
Thanks

View 2 Replies View Related

Return Val Of Stored Procedure In Query Analyzer

Jul 20, 2005

hi ng,i am newbie to sqlserver and my problem seems simple, but i didn't findinformation about it:How can i display theRETURN @xvalue of a stored procedure in the sql analyzer of the sqlserver?thanks a lot,d

View 2 Replies View Related

Stored Procedure With Parameters OUTPUT And RETURN VALUE

Oct 26, 2000

Hi

How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?

thank you in advance

View 1 Replies View Related

T-SQL (SS2K8) :: How To Display Stored Procedure Output In Html Table Format

Mar 16, 2014

i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.

View 2 Replies View Related

.NET Framework :: Stored Procedure Can't Return Output Param Value

Oct 8, 2015

My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...

alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)

[Code] .....

View 3 Replies View Related

Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access

Jan 12, 2007

For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.

ddave

View 2 Replies View Related

Stored Procedure Output Parameter

Aug 19, 2007

  I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
  1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
  If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas

View 6 Replies View Related

Stored Procedure && Output Parameter

Feb 3, 2008

I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record.  The procedure works fine, but I'm unable to get that return value in my code.  Here's what I have:
 IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY() 
 
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
 I'm getting the error that I have "Too many arguments specified" in my Insert() method.  When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions?  Don't I need to declare that return value in my .NET code as an output parameter?

View 2 Replies View Related

OUTPUT Parameter From Stored Procedure

Sep 2, 2000

Hi

I should validate a USER and if the user is not a valid user it should
return a message to the front end application for this I have a procedure
like this .

Basically I need to pass an output parameter to the front end application
returned by this procedure.

Create procedure test
@userid VARCHAR(20)
AS
Declare c1 cursor AS
Select userid from USERS_TBL

Open Cursor C1
FETCH NEXT from C1 INTO @Temp
While @fetch_status = 0
BEGIN
If @Temp <> @Userid
-- IF the USER is not a valid USER ...It should not execute
the Futher code(statement) it should exit the procedure
by returning the error statement.
---
---
---
FETCH NEXT from C1 INTO @Temp
END


Thanks
VENU

View 1 Replies View Related

Output Parameter In Stored Procedure

Dec 3, 2004

can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter.
ie insert record and get its id column value in output parameter.

any one knows the way?

View 1 Replies View Related

Output Parameter Of A Stored Procedure

Mar 7, 2004

I have a stored procedure (named Insert_SRegister) that calls another
stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one.
The program goes like this:
The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored
procedure.

The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;

The code of the first procedur is :

Create Procedure dbo.Insert_SRegister

@YGroup VarChar (3),
@YDate VarChar (4),
@YTerm Char(1),
@SName VarChar(30),
@SSecondName VarChar(30),
@SFirstName VarChar(30),
@SGender TinyInt,
@SBDate DateTime,
@SBPlace VarChar(50),
@SOCountry VarChar(50),
@SOPassCount VarChar(50),
@PassNo VarChar(50),
@SOLang VarChar(50),
@MOLang VarChar(50),


@MName VarChar(50),
@MHAdd VarChar(50),
@MHT1 VarChar(20),
@MHT2 VarChar(20),
@MHT3 VarChar(20),
@MHMob VarChar(20),
@MWAdd VarChar(50),
@MWT1 VarChar(20),
@MWT2 VarChar(20),
@MExt VarChar(10),
@MWMob VarChar(20),


@FName VarChar(50),
@FHAdd VarChar(50),
@FHT1 VarChar(20),
@FHT2 VarChar(20),
@FHT3 VarChar(20),
@FHMob VarChar(20),
@FWAdd VarChar(50),
@FWT1 VarChar(20),
@FWT2 VarChar(20),
@FExt VarChar(10),
@FWMob VarChar(20),


@EAdd VarChar(50),
@ETele VarChar(20),
@SchName VarChar(50),
@SAdd VarChar(50),

@FBNo Smallint,
@FSNo Smallint,
@FONo VarChar(50),
@EMedical VarChar(1000),
@FDetails VarChar(1000),
@FRDetails VarChar(1000),
@PName VarChar(50),
@StudentStatus VarChar(50) OUTPUT,
@ID VarChar (50) OUTPUT

As
Begin
Declare @Exists Int, -- Return Value
@StudentCode VarChar(50)

-----------------------------------------
-- here I call the second proc and assgin it output to variable
--@StudentCode
-----------------------------------------------------------------------------------
exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT

----Do work and insert info in db

End



Code of the second stored procedure is:
ALTER Procedure dbo.Generate_Student_Code

@YGroup VarChar (3),
@YDate VarChar (4),
@newID VarChar (50) OUTPUT
As
Begin
set nocount on
Declare @Exists int, -- Return Value
@Mv varchar(5),
@IdLen int
If Exists(Select SID
From SRegisteration)
Begin
select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration
Set @IdLen = DATALENGTH(@Mv)

Set @Mv =
case
when @IdLen = 1 then '0000' + @Mv
when @IdLen = 2 then '000' + @Mv
when @IdLen = 3 then '00' + @Mv
when @IdLen = 4 then '0' + @Mv
else @Mv
end

Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv
---------Here I return the ID value
select @newID
--------------------------------------
Select @Exists = 1

End

Else

Begin
Select @Exists = 0
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001'
---------Here I return the ID value
select @newID
------------------
Return @Exists
End

End

View 2 Replies View Related

Help! !SQL Stored Procedure Output Parameter.

Feb 24, 2006

I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter.

Code:
CSTRING strSQL;
strSQL.Format("BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT('%s', '%s', '%d'); END;",(LPCSTR)lpEQNum,(LPCSTR)lpMoveTo, result); <-Failing
at '%d' result.

Thanks.

View 1 Replies View Related

Use Of OUTPUT Parameter In Stored Procedure?

May 12, 2015

I am not clear about the use of OUTPUT parameter in stored procedure as we can return any value using select query.

View 2 Replies View Related

Return Stored Procedure Parameter

Apr 27, 2007

I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);


SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;

SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);

lbl_ERROR.Text = Convert.ToString(sp);

conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
 My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
 Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn =  ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn

View 5 Replies View Related

Stored Procedure That Will Only Run In Query Analyzer.

Jul 28, 2004

Hi all,

I have a problem with a stored procedure that executes properly when running it in Query Analyzer. When I call the SP from an ASP.NET application, it doesn't seem to run properly. I have verified that the parameter values are correct, but there is one update command that does not update any rows when it executes although it should. I tried stepping through the SP from within Visual Studio and it still does not work properly even though all parameters have the correct values.

Why would a SP execute properly when used in QA but not when an application executes it?

View 4 Replies View Related

BCP Stored Procedure From Query Analyzer..HOW??

Apr 17, 2001

I am trying to see if there is anyway to BCP a stored procedure from SQL query analyzer. The statement works fine from the command prompt or from within DTS but not from SQL QA.

The bcp statement is as follows:
master..xp_cmdshell "bcp "exec pubs.dbo.sp_employee" queryout dev01e$emp.txt /c /o dev01e$emp.out /T /SDEV01"

sp_employee has the script:
SELECT * FROM EMPLOYEE

Any help is appreciated. Thanks.
AJ

View 2 Replies View Related

Retrieve Output Parameter From Stored Procedure

May 18, 2007

Hi,
 I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
 my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO

View 1 Replies View Related

Is It Possible To Use Table Output Parameter Of Stored Procedure? If Possible, How?

Jun 28, 2007

is it possible to use table output parameter of stored procedure? if possible, how?
 
thanks

View 2 Replies View Related

Retrieving Output Parameter From A Stored Procedure

Feb 28, 2008

Lets say I have the following stored procedure

Code:

View 2 Replies View Related

Stored Procedure Output Parameter Problem

Apr 22, 2008

I am trying to run a stored procedure in a C# class I built for this purpose, and get its output parameter.  I get the following error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'sp_visitor_add' expects parameter '@visitor_id', which was not supplied.I've pasted the code below.  The error occurs on line 24.   I don't understand why I am getting this error because I am supplying the "@visitor_id" OUTPUT parameter in line 17. 1 public class SetSession
2 {
3 public object AddVisitor(object webSiteNumber, string sessionId)
4 {
5 string _conString = null;
6 SqlConnection _con = null;
7 SqlCommand _cmdSelect = null;
8
9 _conString = WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
10 _con = new SqlConnection(_conString);
11
12 _cmdSelect = new SqlCommand("sp_visitor_add", _con);
13 _cmdSelect.CommandType = CommandType.StoredProcedure;
14
15 _cmdSelect.Parameters.Add("@web_site_number", SqlDbType.Int).Value = webSiteNumber;
16 _cmdSelect.Parameters.Add("@session_id", SqlDbType.VarChar, 32).Value = sessionId;
17 _cmdSelect.Parameters.Add("@visitor_id", SqlDbType.Int).Direction = ParameterDirection.Output;
18
19 int result = 0;
20
21 try
22 {
23 _con.Open();
24 _cmdSelect.ExecuteNonQuery();
25 result = (int)_cmdSelect.Parameters["@visitor_id"].Value;
26 }
27 finally
28 {
29 _con.Close();
30 }
31 return result;
32 }
33 }
Here is the top of my stored procedure for reference:
CREATE PROCEDURE [dbo].[sp_visitor_add]   @web_site_number int    = NULL,   @session_id varchar(50) = NULL,   @visitor_id int OUTPUTAS
...
Can anyone suggest a solution?

View 5 Replies View Related

Parsing The Output Parameter Of A Stored Procedure

Apr 29, 2008

 Hi,
I am trying to cast the parameter returned from the stored procedure to an int32 type. But it is giving me  an error message as " Input string is not in the correct format " 
my code is
int valid  = Int32.Parse(command.Parameters["@Valid"].Value.ToString());
the @Valid parameter is an output parameter and is declared as int in the stored procedure....  the command is a DbCommand object
does anyone have an idea why this error is happening.
Naveen

View 2 Replies View Related

Value Of Stored Procedure Output Parameter Is Blank

Mar 31, 2004

Here's my stored procedure...


CREATE PROCEDURE [Get_WirelessProducts_By_Page]
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
ProductID int,
ProductCategoryID nvarchar(50),
ProductBandwidthKB int,
ProductOverridePrice nvarchar(50),
UseWirelessOverridePrice bit,
DedicationTypeID int,
DedicationTypeName nvarchar(50)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable
(
ProductID,
ProductCategoryID,
ProductBandwidthKB,
ProductOverridePrice,
UseWirelessOverridePrice,
DedicationTypeID,
DedicationTypeName
)
SELECT
W.ProductID,
W.ProductCategoryID,
W.ProductBandwidthKB,
W.ProductOverridePrice,
W.UseWirelessOverridePrice,
W.DedicationTypeID,
D.DedicationTypeName
FROM
tblWirelessProducts W
INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = D.DedicationTypeID
--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT
ProductID,
ProductCategoryID,
ProductBandwidthKB,
ProductOverridePrice,
UseWirelessOverridePrice,
DedicationTypeID
DedicationTypeName
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRec
--Return the total number of records available as an output parameter

SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts
GO
Here is the relevant VB Code:
Dim parmReturnValue As SqlParameter
'conProducts is already open
cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts)
cmdProducts.CommandType = CommandType.StoredProcedure
cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage)
cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize)
parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int)
parmReturnValue.Direction = ParameterDirection.Output
conProducts.Open
dgrdProducts.DataSource = cmdProducts.ExecuteReader()

If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then
Response.Write(cmdProducts.Parameters("@TotalRecords").Value)
End IfThe rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.

View 1 Replies View Related

NText To Stored Procedure Output Parameter

Mar 28, 2005

Greetings all!  I'm writing a small content management program for a website that I'm putting together.   One of the critical aspects of this projects is that users want to be able to cut n' paste from Word in to a FreeTextBox control and save it to the database.  Well, we've run in to some problems.  These entries are running well over 4000 characters, and I'm going to have to use an ntext datatype in order to the field to accomodate the amount of text they want to post.
My input stored procedures work great and it's dumping it well in to the table.  But the problem I'm having is with the output.  Apparently, you can't pass the values to an output parameter or reference the field directly in a stored procedure.  I've scoured the internet and purchased several books just to deal with this issue.  Can someone point me in the right direction or offer some examples of getting ntext data out through a stored procedure? I'm in a real squeeze here.
TIA
Matt

View 3 Replies View Related

T-SQL (SS2K8) :: Stored Procedure With OUTPUT Parameter

Oct 15, 2014

Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure
Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter

DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1)
DECLARE @parmRET1 int
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType

SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';
EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1

SELECT @parmRET1

--The parameter @parmRET1 returns NULL instead of 1 .
The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)

Not sure what is wrong here...

View 2 Replies View Related

Stored Procedure Output Parameter Error

Sep 18, 2014

Stored procedure displays the follwing error.

Procedure or function 'getFavoriteList' expects parameter '@category_name', which was not supplied.

I have code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getFavoriteList]
@customer_id int,
@category_name varchar(200) OUTPUT

[Code] ....

View 6 Replies View Related

Creating Stored Procedure With Output Parameter

Dec 5, 2005

Hi

Being a rookie to stored procedures the following cry for help will be posted.

The stored procedure below is supposed to create a dynamic string as an output parameter

create procedure spGetFieldNameList
@TableName varchar(256),@String varchar(8000) output
As
select @String = @String + sc.name + ','
from syscolumns as sc(nolock) join sysobjects as so(nolock)
on sc.id = so.id
and so.name = @TableName
order by colid asc

Creating the stored procedure does not trigger an error.

Calling the stored procedure as: spGetFieldNameList 'OfferCondition'.
Causes the following message:
Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0
Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied.

Calling it this way:
declare @String as varchar(8000)
set @String = ''
set @String = spGetFieldNameList 'OfferCondition'.

Causes:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'OfferCondition'.

Any help is highly appreciated.

Thanks

View 6 Replies View Related

Stored Procedure Output Parameter Issue

Aug 27, 2007

Hi,
the stored procedure returns only the first letter of the ouput parameter i.e 'e'. spvr_ErrorDescription='error while updating data'. Actually It must return 'error while updating data'. can anybody help
me to solve this issue.How to set the size of output parameter
in dataaccess layer.

Below is the actual code




public string UserCostCenter_Upd(string spvp_Offering,
string spvp_UserId,
string spvp_CostCenter,
DateTime spvp_StartDate,
DateTime spvp_ExpiryDate,
ref string spvr_ErrorDescription)
{
// The instance ds will hold the result set obtained from the DataRequest
DataSet ds = new DataSet();
RequestParameter[] parms = new RequestParameter[7];
parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);
parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);
parms[1].Value = spvp_Offering;
parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);
if (spvp_UserId == null)
parms[2].Value = DBNull.Value;
else
parms[2].Value = spvp_UserId;
parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);
if (spvp_CostCenter == null)
parms[3].Value = DBNull.Value;
else
parms[3].Value = spvp_CostCenter;
parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);
if (spvp_StartDate == null)
parms[4].Value = DBNull.Value;
else
parms[4].Value = spvp_StartDate;
parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);
if (spvp_ExpiryDate == null)
parms[5].Value = DBNull.Value;
else
parms[5].Value = spvp_ExpiryDate;
parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);
parms[6].Value = spvr_ErrorDescription;

// Create an instance of DataSQLClient

AirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;
// typDSRef holds the ref. to the strongly typed dataset
DataSet typDSRef = (DataSet)ds;
DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);
// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSet
DataHelper helper = new DataHelper();
if (!helper.MapResultSet(ref typDSRef, resultDataSet))
ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed.
//returnCode = (int)parms[0].Value;
spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);
return spvr_ErrorDescription ;
}

View 1 Replies View Related

How To Execute A Stored Procedure With Output Parameter

Apr 17, 2008

Hi guys. I have a procedure with just one parameter. That's a output parameter. It's type is NVARCHAR
I think there's no problem with the procedure. Now I want to execute it. Can I declare that variable passed by parameter to the procedure ? Must I use the Execute command ?

thanks a lot.

View 3 Replies View Related

Output Parameter In CLR Stored Procedure Written In C++

Oct 15, 2006

How can I declare output parameter in stored procedure, written in C++/CLR?
In C# it is proc_name( out SqlInt32 value ).
In VB.NET it is proc_name( <Out()> ByRef value As SqlInt32 ).
And in C++ ?

Thanks a lot for support.

View 7 Replies View Related

Insert Stored Procedure With Output Parameter

Mar 2, 2007

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

View 5 Replies View Related

Stored Procedure Multi-value Parameter And Return Value

Feb 12, 2008

What is the best way to design this stored procedure for speed?

I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?

input parameter
ID
0
1
2

return value
ID, CurrentDue, PastDue
0, 100, 0
1, 100, 100
2, 123, 0

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved