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 ?
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.
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
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.
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,
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
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?
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 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
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 RelatedI 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?
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.
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?
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()
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 RelatedHi.
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
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
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
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?
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
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
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.
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?
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
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 RelatedCan 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
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'
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.
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 RelatedI 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
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
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...
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>
<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