I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.
But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?
I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.
(fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)
I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know
In SQL Server SELECT query Result displays tablePlease tell how to create stored procedure for a single row at a time i.e. all only ONE row is displayed at a time exselect name from useswhere name is display is tabular format,but i need in row with coma anc,d,f,f,e,g,h,jin this wayhow can i write a stored procedure for that
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
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]
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.
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.
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
Hello evry1. i m new to SQL Server2000. plzz tell me where i m wrong in this SP . this procedure is not giving any error but it is not showing any result. plzz help me n give me any idea if u know how i can solve my problem best regards sadaf n here is the procedure
begin select * from [profile] where [user_name] like '%@user_name1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@gender1 is not null and len(@gender1) > 0) begin select * from [profile] where gender = @gender1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@email_address1 is not null and len(@email_address1) > 0) begin select * from [profile] where [email-address] like '%@email_address1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@relegion1 is not null and len(@relegion1) > 0) begin select * from [profile] where relegion = @relegion1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@political_view1 is not null and len(@political_view1) > 0) begin select * from [profile] where political_view = @political_view1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@passion1 is not null and len(@passion1) > 0) begin select * from [profile] where passion like '%@passion1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@sports1 is not null and len(@sports1) > 0) begin select * from [profile] where sports like '%@sports1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@activities1 is not null and len(@activities1) > 0) begin select * from [profile] where activities like '%@activities%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@books1 is not null and len(@books1) > 0) begin select * from [profile] where books like '%books1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@music1 is not null and len(@music1) > 0) begin select * from [profile] where music like '%@music%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@tv_shows1 is not null and len(@tv_shows1) > 0) begin select * from [profile] where tv_shows like '%@tv_shows1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@movies1 is not null and len(@movies1) > 0) begin select * from [profile] where movies like '%@movies1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@cuisines1 is not null and len(@cuisines1) > 0) begin select * from [profile] where cuisines like '%@cuisines1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@intrested_in1 is not null and len(@intrested_in1) > 0) begin select * from [profile] where intrested_in = @intrested_in1 and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@education1 is not null and len(@education1) > 0) begin select * from [profile] where education like'%@education1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@college_university1 is not null and len(@college_university1) > 0) begin select * from [profile] where institution like '%@college_university1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@occupation1 is not null and len(@occupation1) > 0) begin select * from [profile] where occupation like '%@occupation1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@industry1 is not null and len(@industry1) > 0) begin select * from [profile] where industry like '%@industry1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@job_desc1 is not null and len(@job_desc1) > 0) begin select * from [profile] where job_desc like '%@job_desc1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@career_intrest1 is not null and len(@career_intrest1) > 0) begin select * from [profile] where career_intrest like '%@career_intrest1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end GO
i made a new stored procedure and execute it. it works great but where can i see the result ? i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ?
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)
Hi All, I have to execute one of the stored procedure within another stored procedure. And have to store it into some table variable... How to do that.pls give me the syntax... Thanks and reagards A
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
Hello all, I have the following Stored Procedure that has been working perfectly for the last year: CODE ==================================================== DELETE FROM tblReportMainMembers INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444' UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users. But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again. Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it. Any ideas? Thanks in advance.Richard M.
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12) AS BEGIN declare @str nvarchar(2000) declare @a varchar(10)
create table #t (paycode varchar(7), hrs varchar(255)) insert into #t exec sp_executesql @str select @a = hrs from #t where paycode='ST' drop table #t print @a
if i call exec sp_execute @str, it will output this PAYCODE HRS ------- -------- ST 08: 30 OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure. HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..
I got this results from running (sp_dropallindex) like this:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO DROP INDEX table1.index1 GO DROP INDEX table1.index2 GO
I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...
I tried something like this:
DECLARE @DROP AS VARCHAR(8000) SET @DROP='exec sp_drop_allindex' EXECUTE (@DROP)
and i see the same out , but my indexes and PK still there ... i'm confused about it ..
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so: SELECT region_code, OperGroup_Code, country_name, MajorGroup_Name, RptgEntity_Name, shell_code, CRC, ' ' as right_type INTO #tmpTShell FROM TShell WHERE 1=0
But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.
Has anyone ever experienced anthying like this? Any help would be greatly appreciated!
Hi, I have a problem with dealing with result sets returned from stored procedures.
I have a procedure like: CREATE PROCEDURE SampleProcedure AS BEGIN SELECT * FROM SampleTable END GO
By executing this stored porocedure is returned result set containing data from SampleTable table. (EXECUTE SampleProcedure)
The returned resultset can be seen in Query Analyzer and can be handled from ADO.NET without any hesitate.
But I can't use this result set from other stored procedure. I tried: SELECT * FROM (EXEC SampleProcedure) But there is sintax error in select statement.
Does anybody know, how to store the result set into a teporary table or select it by SELECT statement?
Hi,I have one stored procedure that calls another ( EXEC proc_abcd ). I wouldlike to return a result set (a temporary table I have created in theprocedure proc_abcd) to the calling procedure for further manipulation. Howcan I do this given that TABLE variables cannot be passed into, or returnedfrom, a stored procedure?Thanks,RobinExample: (if such a thing were possible):DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )...../*Insert a test value into the first temporary table*/INSERT INTO @myTempTable1 VALUES ( 1234 )...../*Execute a stored procedure returning another temporary table ofvalues.*/EXEC proc_abcd @myTempTable2 OUTPUT......../*Insert the values from the second temporary table into the first.*/SELECT * INTO @myTempTable1 FROM @myTempTable2
I am trying to get data back from stored procedure output arguments. I am in a Visual C++ enviroment calling the stored procedure via the ODBC function SQLExecDirect. When the stored procedure has only a single output argument, everything works fine. When there are multiple output arguments, I get an error "Invalid Cursor State' from the SQLFetch. This is telling me there is no result set. Below is a pseudo code fragment which shows what I am doing. I want to use SQLGetData to convert the data into C variables. I do not want to use SQLBindParameter or Parameter Markers. Sql = "DECLARE @outarg1 int DECLARE @outarg2 datetime {CALL ProcName('Inarg1','Inarg2',.......@outarg1 OUTPUT,@outarg2 OUTPUT ) } SELECT @outarg1,@outarg2 " ; rc = SQLExecDirect(hstmt,Sql,strlen(Sql) ) if( rc == SQL_SUCCESS) { rc == SQLFetch(hstmt); if(rc == SQL_ERROR) { SqlGetDiagRec(.........); } } ..... When there is one out parameter, everything works OK.
When there are multiple out parameters, I get a SQL error state 24000 which is "Invalid Cursor State". This tells me there is no result set for the Fetch to act on. The stored procedure is doing a select for each out parameter. Any Idea why I get an error when using more than one out parameter
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */ SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */ SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/
I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.
How can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().
I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.
I've created a hierarchal listbox form that drills down From
Product - Colour - Year.
based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.
So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.
I want that product number to be used to populate the grid view. Is there away for me to do this?
My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.
Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK
I want to use the returned table name and update the set eh FK's value = null.
Some how it is not working , can some one point me out on correct solution.
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure. I hope i'm being clear. Any help will be appreciated. Here is an example of how i am finding my number Employee is the name of my table and Number is the name of my column.
Hi, I have written a stored procedure to upload a file to a table. I am quering this table and want to download the result to a file.Can someone please help me to do this ?
