Convert Stored Procedure's Returned Output To Varchar From Int?
Jun 23, 2006
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @S AS VARCHAR(1000)
EXEC @S = PDT.DBO.SP_RPT
'ABC'
SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email
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)
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.
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
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()
Hi Guys I am wondering if you could spare some time and help me out with this puzzle. I am new to this stuff so please take it easy on me.
I’m trying to create procedure which will take 2 input parameters and give me 1 back. Originally there will be more outputs but for this training exercise 1 should do. There are 2 tables as per diagram below and what I’m trying to do is Verify username & password and pull out user group_name.
For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.
'==================================================== ALTER PROCEDURE dbo.try01 ( @UserName varchar(50), @Password varchar(50), @Group varchar Output ) AS SET NOCOUNT ON; SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME, TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID FROM TBL_USERS INNER JOIN TBL_USER_GROUPS ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID WHERE (TBL_USERS.USERNAME = @UserName) AND (TBL_USERS.PASSWORD = @Password) '====================================================
and this is what i'm getting in VS.Net while trying to save.
'==================================================== ADO error: A select statement that assigns a value to variable must not be combined with data-retrieval operation. '====================================================
I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Hi, I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value. Anybody could help me with the problem? Thanks.
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
I have an asp.net 1.1 website that uses sql server 2000 and vb.I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run itin the query analyzer the correct value is returned. Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB
' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID"))
' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID)
Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer
' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID)
Dim parameterOrderCustID As New SqlParameter("@customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID)
'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()
' Return the customer_id (obtained as out paramter of SPROC)
If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If
End Function
the stored procdure is CREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID] ( @order_id int, @customer_id int OUTPUT ) AS
/* Return the customer_id from the Orders. */ SELECT @customer_id = customer_id
FROM nlb_Orders
WHERE order_id = @order_id GO
I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2. Any ideas would be appreciated ThanksPete
The following query only brings back too much data in the table - there are duplicates and the lab it shows is wrong. The problem is: AND a.calLab = f.ID in the Where statement. Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0. tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null I need to get the rest of the data. Any suggestions? Thanks..... Zath
SELECT a.assignedID, b.Manufacturer, a.modelNumber, a.serialNumber, e.equipmentType, a.Description, c.Location, d.Status, a.modifiedDate, a.modifiedBy, a.Notes, a.Picture, f.LabName, a.calibrationRequired, a.calDate, a.CalDueDate, a.assetNumber, a.ID FROM tblEquipments a, tblManufacturers b, tblLocation c, tblStatus d, tblEquipment_Type e, tblLabs f WHERE a.manufacturer = b.manufacturerID AND a.location = c.locationID AND a.Status = d.statusID AND a.EquipmentType = e.ID AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0) ORDER BY a.ID
I have a stored procedure that selects * from my table, and it seems to be working fine: USE myDB GO IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL DROP PROCEDURE GetAll GO CREATE PROCEDURE GetAll AS DECLARE ref_cur Cursor FOR SELECT * FROM myTable Open ref_cur FETCH NEXT FROM ref_cur DEALLOCATE ref_cur
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values. Can anyone help me finish this?public class dbGet_base { public dbGet_base() { _requestId = 0; }
public dbGet_base(Int64 RequestId) { this._requestId = RequestId; getDbValues(RequestId); } public void getDbValues(Int64 RequestId) { getDbValues(RequestId, "GetAll"); } public void getDbValues(Int64 RequestId, string SP_Name) { using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"])) using (SqlCommand Command = new SqlCommand(SP_Name, Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId; Command.Parameters.Add(?? } }
@UserIdvarchar(50), @Passwordvarchar(50) AS SELECT tblAdvertisers.UserID, tblAdvertisers.Password FROM tblAdvertisers WHERE ((tblAdvertisers.UserID = @UserId) AND (tblAdvertisers.Password = @Password))
I can run it in Query Analyzer and it returns one record as it should. I want it in ASP.NET to return the amount of rows that are effected, e.g that the login is correct.
The code I have is:
public bool ValidateUserIdAndPassword(string userId, string password) { sqlCommand.CommandType = CommandType.StoredProcedure; //the name of the stored procedure sqlCommand.CommandText = "spValidateUserIdAndPassword";
SqlParameter myParam;
//add the param's to the SP
//userId information for the user myParam = new SqlParameter("@UserId", SqlDbType.VarChar, 50); myParam.Value = CStr(userId); sqlCommand.Parameters.Add(myParam);
//password information for the user myParam = new SqlParameter("@Password", SqlDbType.VarChar, 50); myParam.Value = CStr(password); sqlCommand.Parameters.Add(myParam);
I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?
I have the following stored procedure. The good thing is I finally got the syntax to the point where it doesn't blow up, but I was under the impression the "print" statement would print my result. In this case all I get is the statement ' 1 Row Affected ' which by the way is not the answer I was expecting.
Here's the code: ' CREATE PROCEDURE createevents AS
declare @myvariable varchar(700)
declare @myvar varchar(700)
begin Select @myvariable = d.id from table1 d, table2 dc where d.class = dc.class
If @myvariable > 0 begin Select @myvar =dp. class from table3 dp, table2 dc where dp.class = dc.class
If @myvariable >= 1 begin print @myvariable + (',') + @myvar end end
else begin print @myvariable + ('is empty') end end
I am using the following stored procedure to insert a value into the database. I am new to stored procedures so I need help. Basically this stored procedurewill only insert a value for "CustomerName" if it is not a duplicate value. So I noticed in My C# when I call this stored procedure, a negative one "-1"is returned if the insert is not performed because of a duplicate value. Is that correct? Should I be getting back a negative one "-1" ? You seeI thought that a Zero "0" would be returned if the insert was not performed.Not a negative one? SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[CustomerCreate]( @CustomerDescription nvarchar(100), @CustomerName nvarchar(100), @LastUpdateDate datetime, @LastUpdateUser nvarchar(32), @inserted_record smallint output) AS if exists (select 1 from Customer where CustomerName = @CustomerName) BEGIN set @inserted_record = 0 END ELSE BEGIN INSERT INTO Customer ( CustomerDescription, CustomerName, Active, LastUpdateDate, LastUpdateUser ) VALUES ( @CustomerDescription, @CustomerName, @LastUpdateDate, @LastUpdateUser ) END
I am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.
I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?
Private Sub Form_Open(Cancel As Integer) dim ...
Set add_bag_results = Nothing With add_bag_results .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "spSampling_add_bag_results" .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) '.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1)) .Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n) .. Set rs_add_bag_results = .Execute End With
Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNThese are the results that are returnedDateCreated OrderID UserID OrderTotal Sent Delivered Paid CustomerName ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 14:49:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNthen there is no problem. I get:41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(2 row(s) returned)
Hi, I need to write a select query that will run all returned results through a separate stored procedure before returning them to me. Something like.... SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID() ......and run all the selected userids through a stored procedure like..... CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate()) Can anyone help me to get these working togethsr in the same qurey?
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks. Query SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.
Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.
But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.
I have a novice question. How does one suppress result sets returned from a stored procedure?
I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.
Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.
The purpose of procA is to query several subsystems and identify which ones need attention.
Cursor While Loop exec @ProcName @ObjectName,@userID,@syncDate set @recs = @@rowcount;
I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5..... (Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
In the SqlDbType enumeration there is no value for the new (max) types, only varchar. If Im passing a large string, it will get cut off at 8K. So how do I specify my varchar parameter as being of the max type ?
Hi All, I have a Stored procedure as below.. create procedure sp_sample @name varchar(12) as begin set @name = (select name from mytable where id = 1) select @name end
how can i return the varchar value from the above Stored procedure? I want to capture it in SQLFetch ODBC call.
I'm having the following (abbreviated) stored procedure:
Code Snippet
CREATE PROCEDURE proc_SomeSmartName @SomeVariable VARCHAR AS BEGIN SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = @SomeVariable END Now my problem: It doesn't seem to work if I give the @SomeParameter a string to work with, neither via SqlCommandObject nor directly in the Management Studio. The following returns zero rows:
Code Snippet
DECLARE @return_value int EXEC @return_value = [dbo].[proc_SomeSmartName] @SomeVariable = 'MyText' SELECT 'Return Value' = @return_value Funny enough, when I have the following query, it works perfectly:
Code Snippet
SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = 'MyText' Returning one row as it should. SomeField is an NVarChar field, but I tried casting it to VarChar without any benefit, and I also supplied the parameter as NVarChar to test, both without further success. And 'MyText' does exist in the database, in both cases when I run the stored procedure and when I run the SQL statement directly.
I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO ALTER PROCEDURE [dbo].[GetProductsByCategoryId] @Category VARCHAR(255), @Range INT, @PageIndex INT, @NumRows INT, @CategoryName nvarchar(255) OUTPUT, @CategoryProductCount INT OUTPUT AS
BEGIN
/* Get product count */ SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/* get full list of products */ With ProductEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2 FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar WHERE Range=@Range AND Category = @Category )
/*get only needed rows */ SELECT field1, field2 FROM ProductEntries WHERE Row Between @startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes. If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables Thanks for any suggestions.
