Can Store Procedure Return Rows?
Feb 28, 2006Can store procedure return rows?
If store procedure can return rows, how to use ASP.NET to receive it? thanks!
ADVERTISEMENT
Store Procedure Does Not Return All Rows Sometimes, Slow Execution...!!!
Oct 14, 2007Dear 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 RelatedStore Procedure Can Return More Than One Value?
Dec 8, 2005Can the store procedure can return more than one value? If i need to return 2 values, how to do that??
View 1 Replies View RelatedReturn From Store Procedure.
Sep 12, 2005Dear 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 RelatedUsing Return Value From A Store Procedure ?
May 9, 2008Dear 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
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Nov 8, 2007Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
How To Return A String Value From The Store Procedure
Jun 3, 2008Hi 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 RelatedReturn String From Store Procedure?
Aug 14, 2006How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.
Handle Store Procedure Return 2 Table
Sep 20, 2006I 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 RelatedHow To Get Store Procedure's Return Value And Output Parameters From BLL?
Oct 10, 2007ex:
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?
How To Code ASP.NET Page With Return Value Store Procedure?
Apr 12, 2005Does 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!
Dataset Store Procedure Return Values
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
I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar
Mar 21, 2008My 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 RelatedTransact SQL :: Why Store Procedure Not Return Any Value / Result After Using Exec
Jul 22, 2015I 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] ....
Reporting Services :: Dataset Not Getting All Rows From Store Procedure
Jul 6, 2015I created a data set using SP. in ssms SP gets all records but in ssrs i am not able to get all records, getting only 5 row.
View 4 Replies View RelatedStored Procedure To Return All Rows In A Table
Jul 20, 2005Hi there,I need to create a stored procedure almost like "Current_user()" to returnto me the total rows in a table.. Is this possible? plz helpRudi
View 1 Replies View RelatedReturn 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.
Store Procedures Return
Apr 28, 2000Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.
Thank you
Return The New Id From A Store Proceedure
Mar 12, 2007i have a store procedure which i need to get the returned id from how do i do this??
the sp:
CREATE PROCEDURE createpost(
@userID integer,
@categoryID integer,
@title varchar(100),
@newsdate datetime,
@story varchar(250),
@wordcount int
)
as
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
im calling it from asp, maybe i need to do something here as well im a bit lost really.. any help be great!
the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();
cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@userID", userID);
cmdselect.Parameters.Add("@categoryID", categoryID );
cmdselect.Parameters.Add("@title", title.Text );
cmdselect.Parameters.Add("@newsdate", newsdate.Text );
cmdselect.Parameters.Add("@story", story.Text );
cmdselect.Parameters.Add("@wordcount", "1" );
int userinserted = cmdselect.ExecuteNonQuery();
Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id=1");
Store Return Result From Sp_columns
Oct 31, 2007I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah
Call Store Procedure From Another Store Procedure
Nov 13, 2006 I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Return Two Rows From One Rows Data
Jul 20, 2005I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies View RelatedHow Can I Store Data In One Row Instead Of 10 Rows?
Jan 1, 2007Hi
I have this stored procedure using Table Variable.
-------------------------------------------------------------------------------------------------------------------------------
create procedure sp_matching @pid Int
ASdeclare @tbl table (MAID int, image varchar(100))
insert @tblselect MAID, image from productswhere pid in (select top 10 mid from matching1 where pid=@pid order by newid())
select * from @tbl
GO
---------------------------------------------------------------------------------------------------------------------------------
When I run this sp, i have table with 10 rows. But what I really need to do is to store all data in one row like:
PID, MAID1, Image1, MAID2, Image2, ..... MAID9, Image9, MAID10, Image10
How can I do this? Please help me out for this.
Thanks.
Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015I 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?
Need Help In Return How Many Rows
Dec 14, 2004HI All,
I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
CREATE procedure dbo.ph_getphonebookoption
@country nvarchar(100),
@company nvarchar(100),
@office nvarchar(100)
as
select
Ext,
FName,
LName,
Title
from
phonebook
where country =@country
and company=@company
and office =@office
select count (*)
GO
How To Return A Specified # Of Rows
Oct 29, 1999Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
What other options do I have?
Thanks,
John
O Rows Return
Feb 19, 2008is there is any way to find out whether my query return 0 rows inside a stored procedure.
View 2 Replies View RelatedReturn Rows Other Than Top 10
Oct 5, 2007Hi All,
I have a question,
Select top 10 * from employee
the above statement return top 10 row.
but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
Thanks for the help
Thanks
Bhaskar
Limit To The Number Of Rows A Dataset Can Store?
Feb 11, 2004hI,
I am using visual c# 2003 and sqlserver 2000 and i am trying to query a column in the sql server and store it into a dataset but i got an error msg:
The number of rows for this query will output 90283 rows.
--------------------------------------------------------------------------------
Query :
SELECT L_ExtendedPrice, COUNT (*) AS Count FROM LINEITEM GROUP BY L_ExtendedPrice ORDER BY Count DESC";
---------------------------------------------------------------------------------
Error msg :
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
----------------------------------------------------------------------------------
is there a limit to the number of rows a dataset can store?
Store Procedure Not Store
Nov 20, 2013My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Sql Help With Number Of Rows Return Value
May 14, 2008hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
Exec An SP To Return Rows To Another SP
Apr 8, 2004Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
What Is Better? Return All Rows Or Nested TOP X?
Feb 24, 2006I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder