Running A Stored Procedure Using Output Result.

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


ADVERTISEMENT

Transact SQL :: Server Is Cutting Off Result-output Running A Powershell Script

May 7, 2015

I want to run a powershell script using xp_cmdshell, put the results into a temp table and do some additional stuff.I'm using:

CREATE TABLE #DrvLetter (
iid int identity(1,1) primary key
,Laufwerk char(500),
)
INSERT INTO #DrvLetter
EXEC xp_cmdshell 'powershell.exe -noprofile -command "gwmi -Class win32_volume | ft capacity, freespace, caption -a"'
select * from #DrvLetter
SQL server is cutting off the output, what I get is (consider the 3 dots at the end of a line):
 
[code]....

View 2 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

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.

View 9 Replies View Related

Access Result Set From Storede Procedure W/in A Stored Procedure

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

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

No Result From Stored Procedure

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

Stored Procedure Result Set

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

Seeing Result Of Stored Procedure

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

Storing The Result Of Stored Procedure...

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

Two Result Sets From Stored Procedure..

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

Passing A Result Set To A Stored Procedure

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

Strange Stored Procedure Result

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

Help...i Need To Store The Result Of A Stored Procedure...

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

Query Result Set Of Stored Procedure

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

Executing A Stored Procedure Result

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

SQL Stored Procedure Not Returning Result Set

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

Return Result Set From Stored Procedure

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

Dealing With A Result Set From A Stored Procedure

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

Can A Stored Procedure's Result Set Be Returned

Apr 4, 2004

Pls give some samples

View 3 Replies View Related

Manipulating The Result Set Of One Stored Procedure From Another....

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

How Do You Get A Result Set Back From A Stored Procedure

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

Iterate Result Set Inside Stored Procedure

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

Is It Possible To Recieve Result Of SQL Stored Procedure To Web Page?

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

SQL Server Stored Procedure&#39;s Result Columns

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

How To Use The Stored Procedure Result To Update The Table

Feb 11, 2008

Hi,

I am new to stored procedure.

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.



CREATE PROCEDURE [dbo].[sp_delete_data]
AS
DECLARE @update_Var table (
REF_Table nvarchar(50),
FK_Column varchar(25),
PK_Table varchar(25),
PK_Column varchar(25),
Constraint_Name varchar(50));

insert into @update_Var
SELECT
FK.TABLE_NAME,
CU.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

WHERE PK.TABLE_NAME='usertable';

Select * from @update_Var



Thanks
Santosh Maskar

View 7 Replies View Related

Store Result Of Stored Procedure Into A Variable

Oct 16, 2007

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.

SELECT Max(Number)
FROM Employee

View 5 Replies View Related

How To Download The Result Of SQL In Stored Procedure To A File On PC?

Oct 1, 2007



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 ?

Thanks,
Naguveeru

View 4 Replies View Related

Multiple Result Sets From A Stored Procedure

Sep 28, 2006

I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '



CREATE PROCEDURE [dbo].[SomeSP]

@SomeParam int

AS

BEGIN

SET NOCOUNT ON;

declare @SomeScalar int

exec @SomeScalar = SomeSPThatReturnsAnIntAndAResultSet @SomeParam

if @SomeScalar = 0

BEGIN

SELECT SomeField FROM SomeTable

END

END

View 6 Replies View Related

Strange Result In Stored Procedure Or PROBLEM

Dec 23, 2007

this is my problem in stored procedure
1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE
2) I need to see only the employee than in the table v_un
3) i see all the employee
4) problem in this line ( [new date] = @mydate2,[new_shift2] = )
i see 2 rows of result from fields [new date] + ,[new_shift]
how to see only 1 field for each field



new date new_shift new date empid name
---------------------------------------------------------------
2007-12-01 99 2007-12-02 99 2568947 aaa
2007-12-01 99 2007-12-02 99 2845209 bbbb
2007-12-01 99 2007-12-02 99 4807756 ccc
2007-12-01 99 2007-12-02 99 9819590 ddd
2007-12-01 99 2007-12-02 99 10055648 eee
2007-12-01 99 2007-12-02 99 10815413 ffff
2007-12-01 99 2007-12-02 99 11070042 gggg
2007-12-01 99 2007-12-02 99 11162047 hhh
-------------------------------------------------------------------------------
i need to see only one for each field

new date new_shift id name
---------------------------------------------------------------
2007-12-01 99 2568947 aaa
2007-12-01 99 2845209 bbbb
2007-12-01 99 4807756 ccc
2007-12-01 99 9819590 ddd
2007-12-02 99 10055648 eee
2007-12-02 99 10815413 ffff
2007-12-02 99 11070042 gggg
2007-12-02 99 11162047 hhh





Code Block
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @day2 [varchar](2)
DECLARE @day3 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @mydate2 [datetime]
set @yeara ='2007'
SET @month1 ='12'
Set @day1 ='1'
Set @day2 ='2'
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)
SELECT
SilokE
CASE
WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 2
else 99
END,
[new date] = @mydate2,[new_shift] =
CASE
WHEN (empid IN (SELECT empid FROM v_un
WHERE (shift = 11 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))
THEN 2
else 99
END,
SilokE.empid, SilokE.Fname
FROM SilokE





View 1 Replies View Related

Output Stored Procedure

Oct 18, 2006

 1 public static List<string> viewtree(int root)
2 {
3 SqlConnection con = new SqlConnection(mainConnectionString);
4 con.Open();
5 try
6 {
7 List<string> ids = new List<string>();
8 SqlCommand command = new SqlCommand(@"ShowHierarchy2", con);
9 command.Parameters.AddWithValue("@root", root);
10 command.Parameters.Add(new SqlParameter("@outstring", SqlDbType.VarChar));
11 command.Parameters["@outstring"].Direction = ParameterDirection.Output;
12 command.CommandType = CommandType.StoredProcedure;
13 //command.ExecuteScalar();
14 //ids = command.Parameters["@outstring"].Value.ToString();
15
16 SqlDataReader dr = command.ExecuteReader();
17 while (dr.Read())
18 {
19 ids.Add((dr["@outstring"].ToString()));
20 }
21 //command.Parameters.Clear();
22
23 return ids;
24 }
25 finally
26 {
27 con.Close();
28 }
29 }
 Can someone tell me why i'm getting the following error:String[1]: the Size property has an invalid size of 0. Thanks in advance

View 7 Replies View Related

How To Get Stored Procedure Output ?

Apr 3, 2004

I have a variable @NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @NetPay. How can I do That?

Set @NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

View 3 Replies View Related

How To Use OUTPUT In Stored Procedure

Jul 4, 2001

I'm calling a stored procedure from outside program using
Execute SPid(a stored procedure).

In the SPid procedure i want to return a value using
Output.

Can any one tell me how do it since i never used it before ?

View 3 Replies View Related

Stored Procedure Output

Sep 27, 2000

Hi
I am running a stored procedure which first puts the data in a temp table and then gives the output...
the output is supposed to generate a report based on data from temp table

However when i run it, the first 2 statements are

(15345 row(s) affected)


(407 row(s) affected)

abd then the select statement runs...due to this, the report in ASP returns an error...does anyone know how i can suppress the first 2 lines and get only the actual data as output

any help will be appreciated

regards,

View 3 Replies View Related







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