How To Get Store Procedure's Return Value And Output Parameters From BLL?
Oct 10, 2007
ex:
myprocedure(@Cusname varchar(50), @Cusid int output)
as
Insert into Customer(Cusname) values (@Cusname)
SELECT @cusid = @@IDENTITY
i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?
View 1 Replies
ADVERTISEMENT
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Mar 21, 2008
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
View 8 Replies
View Related
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
Feb 18, 2006
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
View 5 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Feb 24, 2006
naturale02 writes "Here is my SP:
CREATE procedure test as
select top 10 count (*) as Download, a.item_code as Item_Code, b.item_title as Item_Title
into report..ten
from tbl_statistics_archive a , shabox..tbl_items b
where a.item_class = '10'
and convert(char(10),a.created,111) >= convert(char(10),getdate()-7,111)
and convert(char(10),a.created,111) <= convert(char(10),getdate()-1,111)
and a.item_code = b.item_code and a.item_class = b.item_class
group by a.item_code, b.item_title
order by 1 desc
GO
I have create the table called 'ten', and i schedule this SP, but when i start the job, there was nothing shown on 'ten' table, which part i have been doing wrong, kindly help me.
Thank you"
View 1 Replies
View Related
Dec 8, 2005
Can the store procedure can return more than one value? If i need to return 2 values, how to do that??
View 1 Replies
View Related
Sep 12, 2005
Dear All, I read through all the post and flipped through the books but Istill can't find the answer to my problem.I'm inserting a new record via a stored procedure and want to return the idvia scope_identity, which I thought would be preety straight forward.The code I'm using is below and this keeps giving me "Multiple-step OLE DBoperation generated errors. Check each OLE DB status value, if available. Nowork was done."How do I pick the returning id value and could anyone see were I'm goingwrong below.Many thanks for any help you can offer.CREATE PROCEDURE [sp_insert_address]@ADDR_NAME_2 [char](70),@ADDR_NO_3 [char](10),@ADDR_ROAD_4 [char](50),@ADDR_DISTRICT_5 [char](50),@ADDR_TOWN_6 [char](50),@ADDR_BOROUGH_7 [char](50),@ADDR_PCODE_8 [char](12),@addr_id [int] OUTPUTAS INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]([ADDR_NAME],[ADDR_NO],[ADDR_ROAD],[ADDR_DISTRICT],[ADDR_TOWN],[ADDR_BOROUGH],[ADDR_PCODE])VALUES(@ADDR_NAME_2,@ADDR_NO_3,@ADDR_ROAD_4,@ADDR_DISTRICT_5,@ADDR_TOWN_6,@ADDR_BOROUGH_7,@ADDR_PCODE_8)set @addr_id = scope_identity()GO
View 10 Replies
View Related
May 9, 2008
Dear all,
I have a store procedure called ValidateUser which retrtive an authneticated ID from a database.
This procedure is defined as follow :
Code Snippet
ALTER PROCEDURE [dbo].[ValidateUser]
-- Add the parameters for the stored procedure here
@LoginId int = 0 OUTPUT,
@UserName varchar(50),
@pw varchar(60),
@LineId varchar(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- return the User id as validted
SELECT @loginID = Id
FROM [NomosConfig].[dbo].Users
WHERE [UserName] = @UserName
AND [PassWord] = @pw
AND [LineId] = @LineId
-- if no record returns, the request user is not allowed
if @@ROWCOUNT < 1
SELECT @loginID = 0
END
Runing this procesdure alone work well and return correct LoginId
What I would like to do is retrieve that return LoginID inside an other procedure. For that I am doinmg as follow :
Code Snippet
DECLARE @AuthenticatedUserId int;
-- verify that user is authorized to access to PSU login
EXECUTE @AuthenticatedUserId= [ValidateUser]
@UserName ='cal'
,@pw ='cal'
,@LineId='PR495250'
SELECT @AuthenticatedUserId
What is strange is that the @AuthenticatedUserId is always retunring 0 ..
Did I write something wrong ?
thnaks fro help
serge
View 3 Replies
View Related
Jan 24, 2006
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)
Any help would be appreciated.
Thanks
View 1 Replies
View Related
Jan 23, 2004
How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?
For example,
CREATE PROCEDURE dbo.sp_test AS
--- returns all words not in Mastery Level 0
EXEC sp_anothertest
--- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults
SELECT * tblFinalResults
GO
Thanks!
View 1 Replies
View Related
Jun 3, 2008
Hi All, I have to return a string value from the store procedure. If condition success BEGIN RETURN 'Success' END ELSE BEGIN RETURN 'Fail' ENDI am retrieving this value with ExecuteReturnQuery() method. But it gives me the error like "Conversion failed when converting the varchar value to data type int."can anyone please help me for this?Thank you.Regards.
View 5 Replies
View Related
Feb 28, 2006
Can store procedure return rows?
If store procedure can return rows, how to use ASP.NET to receive it? thanks!
View 2 Replies
View Related
Aug 14, 2006
How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.
View 8 Replies
View Related
Oct 22, 2005
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
View 1 Replies
View Related
Sep 20, 2006
I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?
View 1 Replies
View Related
Apr 12, 2005
Does anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
View 6 Replies
View Related
Apr 1, 2008
Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.
Thanks in advance.
Glenn
View 5 Replies
View Related
Jan 2, 2008
Hi,I actualy have another problem with a store procedure. By runnuing a aspx-Site I was toldthat there were to much parameters given for transfering data to the store procedure. Thisparamter I have:ArtikelID, NewsID, Ueberschrift_D, Ueberschrift_E, Text_D, Text_E all are defined within a <asp:SQLDatasource> as <asp:parameter..> within <UpdateParameters>When I kill all parameters I was told that the @ArtikelID-Parameter was not declarated. If I havedefined all parameters expect the Text_E I was told that the @Text_E-Parameter was not declarated.When I declarate the Text_E-Parameter as last parameter that should be nessessary I get the abovementioned error-message.What can be wrong?Regards
View 6 Replies
View Related
May 28, 2014
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
View 2 Replies
View Related
Jan 28, 2008
Hi all,
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test
as
begin
select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end
I'm expecting something like this...
declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.
Thanks,
View 5 Replies
View Related
Oct 14, 2007
Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select *From Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi
View 2 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
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
Feb 11, 2008
i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.
View 3 Replies
View Related
Aug 3, 2004
Hi
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?
View 3 Replies
View Related
Dec 3, 2005
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
View 2 Replies
View Related
Mar 23, 2007
Hi,
I hope someone can help.
I've written a stored procedure that returns a value via an output parameter.
I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.
Here's how I'm calling the stored procedure several times
begin
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid
end
And this is the start of the stored procedure
Create PROCEDURE [dbo].[GetInstrumentId]
(@Ticket varchar(250), @InstrumentId int output)
AS
If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.
Sean
View 3 Replies
View Related
Nov 19, 2006
Hi All,
The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?
Also, I cannot see how the value being returned by OUT is being iterated...
Any help on the obove two matters is appreciated.
Thank You
Chris
BEGIN SNIPPET----------------------------------------------------------------------------------------------------------
--The following example creates the Production.usp_GetList
--stored procedure, which returns a list of products that have
--prices that do not exceed a specified amount.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
--- USE
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
----------------------
--- Partial Result Set
----------------------
--Product List Price
---------------------------------------------------- ------------------
--Road-750 Black, 58 539.99
--Mountain-500 Silver, 40 564.99
--Mountain-500 Silver, 42 564.99
--...
--Road-750 Black, 48 539.99
--Road-750 Black, 52 539.99
--
--(14 row(s) affected)
--
--These items can be purchased for less than $700.00.
View 4 Replies
View Related
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
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
Jan 25, 2008
I am stuck on how to syntactically retrieve an output value (@ProdCount) from a stored procedure. The SPROC works fine: the value of @ProdCount appears correctly in the output window. However, I can't retrieve it in the DAL handler (value remains 0). Does anyone have an idea on how to properly extract the return value. TIA for any pointers.SPROC (abridged): ALTER PROCEDURE and_Store_GetProductsByProdCatID_SortPage (@ProdCatID INT,...@ProdCount INT OUTPUT
)
ASSELECT @ProdCount=(SELECT COUNT(*) FROM and_StoreProduct WHERE ProdCatID= @ProdCatID)DECLARE @SQL nvarchar(4000)SET @SQL = 'WITH tmpProd AS ( SELECT ROW_NUMBER() etc.. )SELECT ProdID, etc..FROM tmpProdWHERE Row BETWEEN etc..ORDER BY etc..'
EXECUTE(@SQL)RETURNDAL handler (abridged): Public Overloads Shared Function GetProductListByCatID(ByVal ProdCatID As Integer, ..., ByVal ProdCount As Integer) As List(Of Product) Dim productList As List(Of Product) = New List(Of Product) Try
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conAnders").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("and_Store_GetProductsByProdCatID_SortPage", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ProdCatID", ProdCatID) '...
cmd.Parameters.AddWithValue("@ProdCount", ProdCount) ' Output parm. Add dummy value.
Dim objProduct As Product 'Temp Product.
con.Open()
Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) While myReader.Read() objProduct = New Product() With objProduct .ProdID = myReader.GetInt32(myReader.GetOrdinal("ProdID")) 'etc..
End With
productList.Add(objProduct)
End While Dim tmp As Object = cmd.Parameters("@ProdCount").Value ' <-- Not updated
myReader.Close() End Using End Using Catch ex As Exception Throw ' Pass up the error.
End Try Return productList End Function
View 8 Replies
View Related
