Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies


ADVERTISEMENT

'SELECT Clause Does Not Return Rowset From Sp'

May 3, 2004

I have sp20, simplified, as:

ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS

SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID

SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate

SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID

This works fine in EM.

When I try to execute it from MS Access ADP Project I get

'Stored Procedure excuted succesfully, but did not return any records'

Although, in EM it returns the right number of records.

Thank you in advance - Rehman

View 2 Replies View Related

Using A UDF To Return Values For A Dynamic WHERE IN () Clause

Mar 2, 2006

Greetings,

I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...

The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.

The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()

RETURNS TABLE

AS

BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END

Then I could call this function by doing something such as:

SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())

I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.

Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"

(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()

RETURNS @t1 TABLE (i INT)

AS

BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END

Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.

Any help is very much appreciated.

- Jeff

View 3 Replies View Related

Select .. NOT IN Clause Doesn't Return Anything?

Nov 3, 2005

Hi there,

It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.

I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)

however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???

It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?

thank you so much for any help!



Grazi

View 6 Replies View Related

Return Result Not Working With IN Clause

Feb 28, 2008

I have a form that has many checkboxes (more than 40) that provide information about companies.

For example. Company ABC
Checkbox 1 (Windows XP)
Checkbox 2 (Windows Vista)
Checkbox 3 (Windows NT4)
etc.

I save these in a SQL table like the following
Row 1 - Col 1 (CompanyID), col2 (checkbox value)
Row 2 - Col 1 (CompanyID), col2 (checkbox value)
etc.. so a comapany can have multiple checkbox selected..

I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.

This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.

I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx)
INTERSECT
(SELECT CompanyID from xx WHERE CheckboxVal = xx)
etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.

Any suggestions on this please...this is my 4th day working on this..

View 8 Replies View Related

Output Parameter Not Return The Value

Nov 7, 2007

 In my SP I have an output parameter,
 
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
 
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
 thanks! 

View 3 Replies View Related

Return Output Parm

Oct 15, 2004

The stored Procedure I am using

CREATE PROCEDURE GetUserID
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserID int OUTPUT
)
AS
SET @UserID = (SELECT fldResourceID
FROM tblResources
WHERE fldEmail = @Email AND fldPassword = @Password)

GO

The Code Behind to call it.

Dim intUserID As Integer = 0
Dim strConnectionString As String = dbConnect()
Dim objConnection As SqlConnection = New SqlConnection(strConnectionString)
objConnection.Open()

Dim objCmd As SqlCommand = New SqlCommand("EXEC GetUserID '" & strEmail & "','" & strPassword & "', " & intUserID, objConnection)

intUserID = objCmd.ExecuteScalar()

objCmd = Nothing
objConnection = Nothing

Return intUserID

However nothing is returned from the objCmd.ExecuteScalar line. Can anyone help me out here. All I need it to return is a single value. Thanks

View 1 Replies View Related

Output Parameter Or Return Value

May 2, 2005

I'm having a terrible time getting a return value from my stored procedure. The sp sets the value  and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value.  I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter.  Either way:  nada.  What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14)  AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory  WHERE b.Bldg=@Dorm AND a.Status='Active'  AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
 SET @Result='3'ELSE IF @Assigned<@Capacity   SET @Result='1' ELSE    SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
 

View 11 Replies View Related

Best Way To Return Records In A Date Range Using Where Clause?

Dec 3, 2007

Say I want to return only records with dates that fall within the next 6 months.  Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005. 

View 1 Replies View Related

Return Output Values From View

Jan 2, 2006

How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?

View 2 Replies View Related

Return 4 Output From A Function In Sqlserver

Jun 16, 2008

Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.

View 7 Replies View Related

Return BIGINT OUTPUT Param To VB!?

May 9, 2006

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function


PLEASE HELP.

Regards,

Sander

View 1 Replies View Related

Transact SQL :: Return Value 1 For Output Parameter

Oct 7, 2015

I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.

Here is my code:
  
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)

[Code] ....

View 4 Replies View Related

WHERE Clause On Nullable Field Not Return Null Records?

Nov 10, 2014

I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.

That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?

Code:
create table #nullTest (
name varchar(50)
,revenue int)

INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)

SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'

Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:

Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'

View 4 Replies View Related

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 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

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

SP Syntax Help - Return New Identity Value As Output Param

Feb 4, 1999

I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?

My current sp looks like this:

CREATE PROCEDURE sp_SaveNewLabel

-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)

AS

INSERT INTO tbLabel
(LabelType
, Logo
, Field01)

VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)


How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...

Zack

View 2 Replies View Related

Return Resultset As Output Parameter To VB Application

Nov 27, 2014

I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.

I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:

Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow

[Code] .....

This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:

Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();

[Code] ....

Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.

View 3 Replies View Related

Using SSIS To Output SP Return Data To A Share

May 4, 2008



I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.

Thanks,
Siva

View 5 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar

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

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

.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

SqlCommand Return And Output Parameters Not Working, But Input Does?

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

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

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 View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

OUTPUT Clause

Nov 7, 2007

Hi,

I'm copying records from table A to Table B. On "insert" being successful I want to delete the copied records from table A. I want to use one single transaction. Could I use OUTPUT to delete all records in table A which key matches the ones on "inserted" ?. I believe it could be possible but I have tried several ways without any luck.

INSERT INTO Table A (Field 1, Field 2)
OUTPUT (DELETE Table B WHERE Key = (SELECT Key FROM Inserted))
SELECT (Field1, Field 2) FROM TABLE B

Or something like that,

Thank you

View 1 Replies View Related

Question On Output Clause !

Feb 29, 2008

I would like to add more columns on output clause while insert....

Following are the table formats used here ...

Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)

Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable

Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>

Is there any way to achieve this....

Thanks...

View 1 Replies View Related

Question About OUTPUT Clause

Nov 14, 2006

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @insertedRecords

values('row 20')



select ID from @insertedRecords





TIA,



Barkingdog



.

View 3 Replies View Related







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