Problem With Stored Procedure Output

Jun 8, 2006

I am new to SQL server - Would appreciate any help.

I have a stored procedure (A) that creates a temp table with a single field of varchar(1000).

I populate this table with string data and with contents of another table obtained via the use of a cursor.

In a nutshell I am creating an XML document representation where each line of the temp table is a line of the xml document.

At the end of the stored procedure I select all of the fields from the temp table.

I have another stored procedure (B) that uses xp_sendemail and uses stored procedure (A) as the query for the attachment.

The problem is that the attachment appears to contain all unicode characters and will not open in windows explorer. It will open in XML SPY and other XML editors.

If I look at the data in a simple editor such as editplus, I see that each chracter is followed by a null character which is why I am assuming it is unicode.

Any idea how to get the select output / email attachment to exclude these null characters?

View 2 Replies


ADVERTISEMENT

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

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

Immediate Output From Stored Procedure.

Apr 11, 2002

When I run a script in query analyzer using a script (A "GO" statement exists after each SQL) I get the results on screen as soon as each query completes. When I run thru stored proc, I can get the result only after the whole procedure completes execution. Is there any way to get the outpout immediately as soon as each query completes? This will be useful in tracking thre progress of a stored proc.
Thanks
Satish

View 2 Replies View Related

Stored Procedure Output

Feb 8, 2007

I am using a stored procedure to query tables and format a record to write to a file. Below is the syntax I'm using to do the write.

set @cmd = 'echo ' + rtrim(@patient_rec) + ' >> f:output
ecall.csv'
exec master..xp_cmdshell @cmd

The procedure will write about 30,000 records or so and then quit writing to the file. Is there a setting I have to modify to write more records or is there a better way to do this?

View 6 Replies View Related

Output Stored Procedure

May 25, 2006

Dear All,How can I show the resultrecords of a SP.I can be done by doubleclick the SPname?But how to do it by code.I want the following interfaceIn my form the user1 selects a SP (combobox showing a userfrinly name)2 adds the related parameters3 and then click the show result-buttonbut the .execute command doen't show teh records.I want the same output as you have doublclicking the SPname in theobjectwindow.Thanks,Filip

View 1 Replies View Related

Stored Procedure Output Parameter

Aug 19, 2007

  I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
  1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
  If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas

View 6 Replies View Related

No Output From The Stored Procedure In The Dataset

Oct 23, 2007

HiI have this code snippet[CODE]   string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;";            SqlConnection connection = new SqlConnection(connstring);            //SqlCommand cmd = new SqlCommand("getInfo", connection);            SqlDataAdapter a = new SqlDataAdapter("getInfo", connection);            a.SelectCommand.CommandType = CommandType.StoredProcedure;            a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param;            DataSet s = new DataSet();                        a.Fill(s);            foreach (DataRow dr in s.Tables[0].Rows)            {                Console.WriteLine(dr[0].ToString());            }[/CODE] When I seperately run the  stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me? 

View 1 Replies View Related

Stored Procedure && Output Parameter

Feb 3, 2008

I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record.  The procedure works fine, but I'm unable to get that return value in my code.  Here's what I have:
 IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY() 
 
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
 I'm getting the error that I have "Too many arguments specified" in my Insert() method.  When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions?  Don't I need to declare that return value in my .NET code as an output parameter?

View 2 Replies View Related

Stored Procedure With Output Parameters

Feb 11, 2008

i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.

can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.

View 3 Replies View Related

Stored Procedure Output Parameters

Aug 3, 2004

Hi

I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.

I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?

View 3 Replies View Related

Stored Procedure Output Problem

Sep 27, 2005

Hi,Got an annoying SQL / .net error. I've created a stored procedure to take in an ID and map this to an ID table and get another ID out. So i have one ID in and one out. I get the following error:Procedure 'sp_SvrMapping' expects parameter '@rID', which was not supplied.Any idea why this happens? surely the output ID doens't need to be defined before its retrived? If so how?Thanksdrazic19p.s. code for both below.**************************************************CREATE PROCEDURE dbo.sp_SvrMapping
( @uID int, @rID int OUTPUT )
As
 SELECT  @rID = rID FROM tbl_mapping WHERE uID= @uIDGO**************************************************
Dim conStr As String = ConfigurationSettings.AppSettings("str_connAuth")
Dim myConn As New SqlConnection(conStr)
Dim myComm As New SqlCommand("sp_SvrMapping", myConn)
Dim test As String = "1"
myComm.CommandType = CommandType.StoredProcedure
myComm.Parameters.Add("@uID", CInt(test))
myConn.Open()
myComm.ExecuteNonQuery() <--- error called
If Not IsDBNull(myComm.Parameters("@rID").Value) Then
MappedServerAccount = myComm.Parameters("@rID").Value
End If
myConn.Close()

View 2 Replies View Related

Stored Procedure And Output Parameters

Dec 3, 2005

EXEC('SELECT COUNT(docid) AS Total FROM docs  WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?

View 2 Replies View Related

Randomized Output From Stored Procedure ?

Jul 11, 2001

Hi.

I'm using stored procedures in SQL7 to return a list of items to a website where they are presented. A new requirement is to present these items in a random order on the website. I have two approaches to this one, either making the result from SQL7 random or presenting the ordered results randomly. Since the presentation logic is used several times (different country subsites), it would be the easiest way to get a randomized result from the stored procedure, which would mean that I only have to change the procedure.

Any thoughts on how to accomplish this, that is returning a list of items but in random order, so that the presentation sequence would be different for each access to the website ??

Any help would be appreciated : )

Thanks!

Eirik Kjølsrud
Systems Developer, Apropos Internett - Norway

View 1 Replies View Related

Output Param With A Stored Procedure

Aug 21, 2001

Hello everyone,

I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.

I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.

create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go

Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?

declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname

Thanks
Kevin

View 2 Replies View Related

OUTPUT Parameter From Stored Procedure

Sep 2, 2000

Hi

I should validate a USER and if the user is not a valid user it should
return a message to the front end application for this I have a procedure
like this .

Basically I need to pass an output parameter to the front end application
returned by this procedure.

Create procedure test
@userid VARCHAR(20)
AS
Declare c1 cursor AS
Select userid from USERS_TBL

Open Cursor C1
FETCH NEXT from C1 INTO @Temp
While @fetch_status = 0
BEGIN
If @Temp <> @Userid
-- IF the USER is not a valid USER ...It should not execute
the Futher code(statement) it should exit the procedure
by returning the error statement.
---
---
---
FETCH NEXT from C1 INTO @Temp
END


Thanks
VENU

View 1 Replies View Related

Output Parameter In Stored Procedure

Dec 3, 2004

can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter.
ie insert record and get its id column value in output parameter.

any one knows the way?

View 1 Replies View Related

Output Parameter Of A Stored Procedure

Mar 7, 2004

I have a stored procedure (named Insert_SRegister) that calls another
stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one.
The program goes like this:
The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored
procedure.

The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;

The code of the first procedur is :

Create Procedure dbo.Insert_SRegister

@YGroup VarChar (3),
@YDate VarChar (4),
@YTerm Char(1),
@SName VarChar(30),
@SSecondName VarChar(30),
@SFirstName VarChar(30),
@SGender TinyInt,
@SBDate DateTime,
@SBPlace VarChar(50),
@SOCountry VarChar(50),
@SOPassCount VarChar(50),
@PassNo VarChar(50),
@SOLang VarChar(50),
@MOLang VarChar(50),


@MName VarChar(50),
@MHAdd VarChar(50),
@MHT1 VarChar(20),
@MHT2 VarChar(20),
@MHT3 VarChar(20),
@MHMob VarChar(20),
@MWAdd VarChar(50),
@MWT1 VarChar(20),
@MWT2 VarChar(20),
@MExt VarChar(10),
@MWMob VarChar(20),


@FName VarChar(50),
@FHAdd VarChar(50),
@FHT1 VarChar(20),
@FHT2 VarChar(20),
@FHT3 VarChar(20),
@FHMob VarChar(20),
@FWAdd VarChar(50),
@FWT1 VarChar(20),
@FWT2 VarChar(20),
@FExt VarChar(10),
@FWMob VarChar(20),


@EAdd VarChar(50),
@ETele VarChar(20),
@SchName VarChar(50),
@SAdd VarChar(50),

@FBNo Smallint,
@FSNo Smallint,
@FONo VarChar(50),
@EMedical VarChar(1000),
@FDetails VarChar(1000),
@FRDetails VarChar(1000),
@PName VarChar(50),
@StudentStatus VarChar(50) OUTPUT,
@ID VarChar (50) OUTPUT

As
Begin
Declare @Exists Int, -- Return Value
@StudentCode VarChar(50)

-----------------------------------------
-- here I call the second proc and assgin it output to variable
--@StudentCode
-----------------------------------------------------------------------------------
exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT

----Do work and insert info in db

End



Code of the second stored procedure is:
ALTER Procedure dbo.Generate_Student_Code

@YGroup VarChar (3),
@YDate VarChar (4),
@newID VarChar (50) OUTPUT
As
Begin
set nocount on
Declare @Exists int, -- Return Value
@Mv varchar(5),
@IdLen int
If Exists(Select SID
From SRegisteration)
Begin
select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration
Set @IdLen = DATALENGTH(@Mv)

Set @Mv =
case
when @IdLen = 1 then '0000' + @Mv
when @IdLen = 2 then '000' + @Mv
when @IdLen = 3 then '00' + @Mv
when @IdLen = 4 then '0' + @Mv
else @Mv
end

Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv
---------Here I return the ID value
select @newID
--------------------------------------
Select @Exists = 1

End

Else

Begin
Select @Exists = 0
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001'
---------Here I return the ID value
select @newID
------------------
Return @Exists
End

End

View 2 Replies View Related

Saving The Output From A Stored Procedure

May 7, 2008

Hi,

I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.

Is this possible ?

Sean

View 2 Replies View Related

Help! !SQL Stored Procedure Output Parameter.

Feb 24, 2006

I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter.

Code:
CSTRING strSQL;
strSQL.Format("BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT('%s', '%s', '%d'); END;",(LPCSTR)lpEQNum,(LPCSTR)lpMoveTo, result); <-Failing
at '%d' result.

Thanks.

View 1 Replies View Related

Stored Procedure And Output Parameters

Mar 23, 2007

Hi,

I hope someone can help.

I've written a stored procedure that returns a value via an output parameter.

I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.

Here's how I'm calling the stored procedure several times
begin
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid
end

And this is the start of the stored procedure

Create PROCEDURE [dbo].[GetInstrumentId]
(@Ticket varchar(250), @InstrumentId int output)
AS


If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.








Sean

View 3 Replies View Related

Output From Extended Stored Procedure

Jul 20, 2005

Hi allIve got a problem I was hoping someone may be able to help with.Im calling an extended stored procedure provided by a third party(master..xp_OrderHeader).This xp requires 3 inputs and is supposed to provie one output.When I call it in SQL Query Analyser all runs OK and I get a column result(single result) titled "Output_Info" with a value of say 300051Here is an example callexecute master..xp_OrderHeader @CustID, @TodayDate, @OrderID,@Output_Info OUTPUTI would have thought that my variable @Output_Info would hold the output,but all I get is NULL?Any ideas what Im doing wrong. Seems bizarre that the XP Ive been providedis displaying a result (be it a coumn I haven't named) ... but I can get itinto my variable for use.CheersCraig

View 1 Replies View Related

Stored Procedure With Output Variable

Jul 20, 2005

Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff

View 2 Replies View Related

How Can I Put The Output Of A Stored Procedure Into A Variable

Nov 8, 2007



hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is

[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like

Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'

View 22 Replies View Related

OUT And OUTPUT - Stored Procedure Parameters.

Nov 19, 2006

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET----------------------------------------------------------------------------------------------------------



--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @Product varchar(40)

, @MaxPrice money

, @ComparePrice money OUTPUT

, @ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;

-- Populate the output variable @ListPprice.

SET @ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);

-- Populate the output variable @compareprice.

SET @ComparePrice = @MaxPrice;

GO

--- USE

DECLARE @ComparePrice money, @Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@ComparePrice OUT,

@Cost OUTPUT

IF @Cost <= @ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'



----------------------

--- Partial Result Set

----------------------

--Product List Price

---------------------------------------------------- ------------------

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.





View 4 Replies View Related

Use Of OUTPUT Parameter In Stored Procedure?

May 12, 2015

I am not clear about the use of OUTPUT parameter in stored procedure as we can return any value using select query.

View 2 Replies View Related

Control Output Of Stored Procedure

Feb 19, 2008

I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.


CREATE PROC proc_Emp

@Sel_Country
AS
BEGIN


SELECT DISTINCT employee, hrid
FROM emp_table
WHERE country = @Sel_Country


@SEND_RESULTS =
CASE WHEN row_count = 0 THEN

SELECT 'No Country', 4444444444
ELSE

(send original results)
END


END

I need to:



Query for the employee list

If the row count is zero/null then return fixed values

Else return the orginal data from Step 1.
I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.

Rob

View 7 Replies View Related

Control The Output Of Stored Procedure

Nov 14, 2007



Hi all,
I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row.
The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.

Is there any way i could do this?


Exmaple:
In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!

Procedure dbo.AddressInsert(....)
as
BEGIN

Insert INTO Adress ....

SELECT * FROM AdressView WHERE AddressID = @@identity
END

Procedure dbo.CustomerInsert(...)
as
begin
-- transcation begin...

EXEC dbo.AddressInsert

INSERT INTO Customer....

-- end transcation

SELECT * FROM Customer WHERE CustomerID = @@identity

end

View 4 Replies View Related

Reading Stored Procedure Output

Apr 17, 2007

Hi all,



I've been wrestling with something for quite some time, and can't seem to get anything figured out. I'd appeciate some guidance.



I have adopted a couple of different approaches, but have been unable to get any of them to yield the information I'm looking for. In essence, I want to use the output of a Reporting Service report for archiving in another database. I have attempted to use the Reporting Services web service to achieve this, but got absolutely nowhere.



Today, I have created a stored procedure which mimics the behaviour of my report and have tried to use SSIS to read the result set from this stored procedure via both a DataReader Source and an OLE DB source. The problem is that the SP takes 4 parameters, and for the life of me it seems impossible to get these parameters down to the SP in a way that SSIS can parse and generate the columns.



I've reached a dead end in terms of my own knowledge. Has anyone done anything like this before and could advise me on the best way to achieve it? Do my aims even make sense, or do I need to rethink the process of using this kind of data?



At this stage, any help would be very much appreciated. Many thanks...

View 10 Replies View Related

SQL 2005 Stored Procedure Not Returning Output Value To Web App

Mar 20, 2007

I am having probelms trying to get a stored proedure to return an output value.
The outline of the specific request is that I supply a varchar which is unique within a set of tables (tables named in another table). I want to search each of the tables until I find the one that has the value and when found return the GUID of the row and the table name.
I have not been successful in getting the value of the GUID (an int) to be returned. If I run in debug and stop after the SQL call I can see the value in the SQL output parameter but it does not appear in the variable I specified in the SQL parameter setting. I am probably doing something simplly wrong but cannot see it.
Please help if you can.
Regards, Major (that is my Christian name ;-)
The code files are copied below.
Web app code
sing System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using EFormsLIB;
public partial class _Default : System.Web.UI.Page
{
String gstrConnectionString; // Connection to the SQL database, set in the config file to allow for changes while testing and running
protected void Page_Load(object sender, EventArgs e)
{
// Read the connection string from the config file
EFormsRoutines r = new EFormsRoutines();
gstrConnectionString = r.ReadConfigString("EForms21ConnectionString", "");
}
protected void btnCancel_Click(object sender, EventArgs e)
{
this.Dispose();
}
protected void btnGet_Click(object sender, EventArgs e)
{
// Get GUID for the form using UniqueDocID as the BlueWare ID.
int intGUID = -1;
SqlConnection SqlCDB = new SqlConnection(gstrConnectionString);
SqlCommand SqlCmd = SqlCDB.CreateCommand();
SqlCmd.CommandText = "GetGUIDfromBWID";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter BWIDParameter = new SqlParameter();
BWIDParameter.ParameterName = "@BWID";
BWIDParameter.SqlDbType = SqlDbType.VarChar;
BWIDParameter.Size = 30;
BWIDParameter.Direction = ParameterDirection.Input;
BWIDParameter.Value = TextBox1.Text;
SqlCmd.Parameters.Add(BWIDParameter);
SqlParameter GUIDParameter = new SqlParameter();
GUIDParameter.ParameterName = "@GUID";
GUIDParameter.SqlDbType = SqlDbType.Int;
GUIDParameter.Direction = ParameterDirection.Output;
GUIDParameter.Value = intGUID;
SqlCmd.Parameters.Add(GUIDParameter);
SqlCmd.Connection.Open();
int ret1 = SqlCmd.ExecuteNonQuery();
SqlCmd.Connection.Close();
Label1.Text = intGUID.ToString();
}
}
web app aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
<asp:Label ID="Label1" runat="server" Style="z-index: 100; left: 48px; position: absolute;
top: 24px" Text="Label"></asp:Label>
<asp:Button ID="btnGet" runat="server" OnClick="btnGet_Click" Text="Get" style="z-index: 101; left: 8px; position: absolute; top: 48px" />
<asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click" Text="Cancel" style="z-index: 102; left: 64px; position: absolute; top: 48px" />
<asp:TextBox ID="TextBox1" runat="server" Style="z-index: 103; left: 48px; position: absolute;
top: 0px" Width="48px"></asp:TextBox>
<asp:Label ID="Label2" runat="server" Style="z-index: 104; left: 0px; position: absolute;
top: 0px" Text="BWID"></asp:Label>
<asp:Label ID="Label3" runat="server" Style="z-index: 106; left: 0px; position: absolute;
top: 24px" Text="GUID"></asp:Label>
</div>
</form>
</body>
</html>
SQL procedure
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GetGUIDfromBWID]  @BWID varchar(30), @GUID int outputASBEGIN SET NOCOUNT ON;
 declare formscursor cursor for select formname from forms for read only declare @found int, @formname varchar(30)
 select @found = 0
 open formscursor fetch next from formscursor into @formname while (@found = 0) begin  if (@@fetch_status <> 0)   select @found = 2  else    begin    if @formname = 'UAT040'     begin      if (select count(*) from UAT040 where BWID = @BWID) =1      begin       select @GUID = (select GUID from UAT040 where BWID = @BWID)       select @found = 1      end     end    else if @formname = 'GEN001'     begin      if (select count(*) from GEN001 where BWID = @BWID) =1      begin       select @GUID = (select GUID from GEN001 where BWID = @BWID)       select @found = 1      end     end    else if @formname = 'GEN002'     begin      if (select count(*) from GEN002 where BWID = @BWID) =1      begin       select @GUID = (select GUID from GEN002 where BWID = @BWID)       select @found = 1      end     end    else if @formname = 'CFT001'     begin      if (select count(*) from CFT001 where BWID = @BWID) =1      begin       select @GUID = (select GUID from CFT001 where BWID = @BWID)       select @found = 1      end     end    fetch next from formscursor into @formname   end end deallocate formscursor if @found = 2  select @GUID = -16
 select @GUIDend

View 2 Replies View Related







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