Working In A Stored Procedure's Result Set In MSSQL 2000 (eg.: Selecting From)
Mar 4, 2004
Hy all.
My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign)
So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL.
Obviously I'd like to do this:
SELECT * FROM EXEC sp_fkeys @table_name = 'xy'
of course it is not working this way, but I'd like something like this.
Please help! Thanx!
View 3 Replies
ADVERTISEMENT
Mar 29, 2007
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with a column named
PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from
tblPercent.
I am getting an error message for the following query.
Have Two tables
1.CriticalDoctors
2.tblPercent
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated.
Pls give me reply on how to write a stored procedure so that I can equate the percentage column
with one value and also check the condition with one value.
Thanking you guys in advance.
madhav
View 4 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Jan 7, 2002
As far as I remember, there was a way of selecting from a stored procedure. For example: let's say we have a stored proceure as : "create procedure MYPROC as select * from MYTABLE". Can we select * from MYPROC?
Thanks in advance,
Mike
View 1 Replies
View Related
Jan 24, 2004
Hi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,
View 1 Replies
View Related
Sep 19, 2007
Hi iam Prameela,
I want to select some dynamic values from a table and store them to another table.
Let me give u an example,its like:
I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like:
Surevy Answers Table:
UID QID Option1 Option2 Survey Name---------These are the fields
1 1 1 0 Articles
1 2 0 1 Articles
2 1 1 0 Articles
2 2 0 1 articles
I need to add all these Options of particular QID and store them in Survey Count table,like
QID Opt1Cnt Opt2Cnt Survey Name
1 2 0 Articles
2 0 2 Articles
When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased
So this count should be modified in Survey Count Table,like:
If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as:
QID Opt1Cnt Opt2Cnt Survey Name
1 3 0 Articles
2 1 2 Articles
I need a Stored Procedure for this.
Please help me with this query.
View 7 Replies
View Related
Apr 28, 2008
Hi.
Now I am learning mssql stored procedure and I faced on some trouble.
I want to make some stored procedure following as...
CREATE PROCEDURE PeopleSearch
(
@name nvarchar(50),
@country nvarchar(50),
@city nvarchar(5),
@condition integer
)
My question from here...But I don't know how to build the code with IF conditions.
IF @condition = 0 THEN
"SELECT * FROM tbl_User WHERE fname='@name'"
IF @condition = 1 THEN
....SELECT Statement..
IF @condition = 2 THEN
....SELECT Statement..
I wish to make some condition in stored procedure like above style.
@condition is not field and it come from outside(vb behind-code).
Is it possible ? Please let me know....how do i have to do...
View 5 Replies
View Related
Jun 22, 2008
please help for MsSQL stored procedure for ASP.net/C#. Inside stored procedure i used 4 different statements in 'where' clause . first one is working but last 3's are not . Statements are given below-FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' -----workingFILENAME LIKE '+ @FILENAMETEXT +'%'' -----not workingSUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' ----not workingLEFT(FILENAME,4)='+@FILENAMETEXT+' -----not working bunch of code for 'where' clause are given below- DECLARE @FILENAMETEXT CHAR(4) DECLARE @CONDITION VARCHAR(2000) SET @FILENAMETEXT='http' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' please reply me as soon as possible
View 1 Replies
View Related
Dec 22, 2005
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
CREATE PROCEDURE Search12
( @signup char(50),
@user_name1 [char](50),
@gender1 [char](6),
@place1 [char](100),
@email_address1 [char](50),
@relegion1 [char](50),
@political_view1 [char](50),
@passion1 [varchar](150),
@sports1 [varchar](150),
@activities1 [varchar](150),
@books1 [varchar](150),
@music1 [varchar](150),
@tv_shows1 [varchar](150),
@movies1 [varchar](150),
@cuisines1 [varchar](150),
@intrested_in1 [varchar](150),
@education1 [char](100),
@college_university1 [char](50),
@occupation1 [char](50),
@industry1 [char](50),
@job_desc1 [char](50),
@career_intrest1 [char](100),
@latitude1 [decimal],
@longitude1 [decimal])
AS
if (@user_name1 is not null and len(@user_name1) > 0)
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
View 6 Replies
View Related
Jul 20, 2005
Hi!How can I save a result set from a stored procedure into a table?Can you show me some examples?Thank you!Darko
View 2 Replies
View Related
Nov 29, 2007
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 ?
my stored procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetTotalMovieCreations]
AS
select count(*)
from tbl_Animations
where statusID = 1
and ToolID = 1
and ToolID = -1
and isDeleted = 0
thanks...
View 4 Replies
View Related
Jan 24, 2008
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
View 1 Replies
View Related
Jun 12, 2008
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...
View 6 Replies
View Related
Apr 3, 2006
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?
View 11 Replies
View Related
May 17, 2006
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.
View 1 Replies
View Related
Aug 24, 2000
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)
select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs '
select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c '
select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') "
select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) '
select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")'
/*
exec sp_executesql @str
*/
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.
Thanks
View 2 Replies
View Related
Sep 28, 2007
I would like to make a selection of records returned by a stored procedure.
E.g.
SELECT Name FROM EXEC somestoredprocedure @age = 25
This is wrong, but is there a way to do this?
Maarten
View 7 Replies
View Related
Jul 22, 2004
Hi Guys..
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 ..
PLEASE HELP ME OUT :D
View 1 Replies
View Related
May 8, 2006
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!
Thanks.
View 5 Replies
View Related
Dec 8, 2011
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.)
View 3 Replies
View Related
Feb 20, 2004
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?
Thanks.
MarF.
View 5 Replies
View Related
Apr 4, 2004
Pls give some samples
View 3 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Oct 9, 2006
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
View 6 Replies
View Related
Sep 22, 2005
oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
View 2 Replies
View Related
Dec 14, 2006
Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.
View 2 Replies
View Related
May 21, 2008
Hi i have a stored procedure which deletes a row from a table.
Sometimes the proc will break the constraints for the table.
How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
Thanks in advance.
Gary
View 2 Replies
View Related
Jul 31, 2006
Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.
I am calling a stored procedure from asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.
if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors
This is the code from my control
System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);
------------------------THis is the code for my storedprocedure-------------
CREATE PROCEDURE [dbo].[generatePaylistTuned]
@periodStart datetime,
@periodEnd datetime,
@addedby varchar(40)
AS
begin transaction generatePayList
DECLARE @pensioner_id int, @dateadded datetime,
@amountpaid float,
@currentMonthlypension float,@actionType varchar(50),
@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,
@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,
@reportSummary varchar(500)
set @NoOfLoadedPensioners =0
set @NoOfDeadPensioners=0
set @NoOfEnrolledPensioners=0
set @DeactivatedPensioners=0
set @actionType ="PayList Generation"
DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId
where p.isActive = 1
OPEN paylist_cursor
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
WHILE @@FETCH_STATUS = 0
BEGIN
set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1
if(@isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@pensioner_id
set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners
set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)
set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners
end
-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
END
CLOSE paylist_cursor
DEALLOCATE paylist_cursor
set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')
if (@@ERROR <> 0)
BEGIN
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION generatePayList
END
commit Transaction generatePayList
GO
View 5 Replies
View Related
Oct 26, 2007
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*/
SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO
View 7 Replies
View Related
Jul 14, 2004
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.
View 2 Replies
View Related
Aug 4, 1999
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().
Any solutions?
View 1 Replies
View Related
May 12, 2008
Hey guys!
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?
Thanks in advanced everybody!
View 6 Replies
View Related
