Can I do something like
CREATE PROCEDURE ProcA
AS
DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT
END
GO
I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.
AB
Hello.
I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want).
Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return
exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla.
Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR exec MyProc
Thanks for any help!
Daniel Ronnqvist, Stockholm
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot.
select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else
---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan)
---but Shaggy has sponsored 2 others
select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy
---I also want to get their information ID,fname,lname
---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain:
---Let's assume that we have an OAK Tree that has 4 main branches
---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible
--the way I have it now taking way too long
--because in within so many (do while loop)
TIA
Please pardon me, I could not find better layout to explain this.
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END
CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
Thanks
Martin
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome
As
SET ROWCOUNT 0
Declare
-- Date types
@startdate smalldatetime
,@enddate smalldatetime
,@ProjectedDate smalldatetime
,@termination smalldatetime
,@effectivedate smalldatetime
-- Integer
,@Nums int
,@nums2 int
,@ClientId int
,@AssetId int
,@ProductID int
,@Policies int
,@product int
,@Per int
,@Projected int
-- String
,@debugtext varchar(150)
,@productid2 varchar(15)
-- float
,@rate float
,@Cap float
--bit
,@Override bit
--Money
,@AnnualPremium Money
,@Value Money
,@Premium Money
,@PaymentAmount Money
--Doubles
,@PremCalc int
,@HoldPrem int
,@HoldCom int
,@CumBal int
,@CumPrem int
,@MonthlyPrem int
,@XBal int
,@CapPrev int
,@PremTier int
,@Incriment int
--Declare cursor for System Variables
DECLARE SystemVar_cur cursor for
SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions
declare AssetDef_cur cursor for
SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions
declare CommisionDef_cur cursor for
Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections
declare projections_cur cursor for
Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table
OPEN SystemVar_cur
FETCH SystemVar_cur INTO @startdate,@enddate
CLOSE SystemVar_cur
DEALLOCATE SystemVar_cur
-- Open the AssetDefinition File and loop through
--
INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
While @@fetch_status = 0
Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL
BEGIN-- begin @termination IS NULL
SET @MonthlyPrem = (@AnnualPremium/12)
SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12'
INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate
SET @ProjectedDate = @effectivedate
Else
SET @ProjectedDate = @startdate
-- end if
SET @PremCalc = 0
SET @CumBal = 0
SET @XBal = 0
SET @HoldCom = 0
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
If @@fetch_status = 0
BEGIN
IF @override = 1
BEGIN-- begin @override = 1
SET @CumPrem = @premium
SET @CumBal = @paymentamount
SET @HoldPrem = @CumPrem
SET @HoldCom = @CumBal
END-- end @override = 1
Else
SET @HoldPrem = @MonthlyPrem
END
CLOSE projections_cur
While @ProjectedDate <= @enddate
BEGIN-- begin While @ProjectedDate <= @enddate
SET @CapPrev = 0 --reset cap balance
SET @XBal = 0
SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @productid2 = @productid
SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table
OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
IF @@fetch_status <> 0
BEGIN
SET @debugtext = 'ERROR? ' + CAST(@@error as Char)
INSERT INTO debug_table VALUES(@debugtext)
END
WHILE @@fetch_status = 0
BEGIN-- begin While CommisionDef Fetch = 0
SET @debugtext = 'Found Commision Record' + CAST(@product as Char)
INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0
BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev
If @PremTier > @Incriment
SET @XBal = @XBal + (@Incriment * @Rate)
Else
BEGIN-- begin @PremTier > @Incriment
If @PremTier >= 0
SET @XBal = @XBal + (@PremTier * @Rate)
END-- end @PremTier > @Incriment
SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10))
INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap
SET @PremTier = @PremTier - @Incriment
END-- end If @Per = 0
Else
BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12
SET @HoldCom = 0
SET @PremCalc = 0
SET @CumBal = @XBal
SET @debugtext = 'CumBal' + CAST(@CumBal as Char)
INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies
END-- end If @Per <> 0
FETCH CommisionDef_cur INTO
@product,@per,@cap,@rate,@value
END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur
-- Fetch the Projection Record
open projections_cur
fetch projections_cur INTO
@override,@premium,@paymentamount
IF @@fetch_status = 0
BEGIN -- begin Projection Fetch = 0
IF @override = 1
SET @HoldCom = @CumBal
ELSE
-- If not overridden, set the fields to Update the projection File
BEGIN-- begin @override <> 1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate
SET @HoldCom = @XBal
END-- end @override <> 1
END-- end Projection Fetch = 0
ELSE
BEGIN -- Begin Projection Fetch else
IF @@fetch_status = -1
BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100
SET @Premium = @HoldPrem - @PremCalc
SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR)
INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it'
INSERT INTO debug_table VALUES(@debugtext)
--Projection record was not found so write it
SET @override = 0
INSERT INTO Projections
(AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount)
VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0)
SET @HoldCom = @XBal
END-- end Projection Fetch = -1
END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate)
SET @PremCalc = @HoldPrem
-- Fetch the Projection Record
OPEN projections_cur
FETCH projections_cur INTO
@override,@premium,@paymentamount
IF @override = 1
BEGIN-- begin @override = 1
SET @CumBal = @paymentamount
SET @HoldPrem = @HoldPrem + @CumPrem
END -- end @override = 1
ELSE
SET @HoldPrem = @HoldPrem + @MonthlyPrem
CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate
END --End the If Termination is NULL
Fetch AssetDef_cur INTO
@termination
,@effectivedate
,@clientId
,@AnnualPremium
,@assetId
,@ProductId
,@Policies
END
CLOSE AssetDef_cur
DEALLOCATE AssetDef_cur
DEALLOCATE projections_cur
DEALLOCATE CommisionDef_cur
return
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
Existing Stored Procedure, has been running well on SQL since 7.0.(but needed some tweaking to migrate to 2000).Now all of a sudden after installing SP4 of SQL 2000,this process slows down, and SQL Spotlight shows the number of locksjust climbing throughout the processing run.According to the MS Knowledge Base Articles on KeyLocks .. this was aproblem that was *fixed* in the service pack ... where as for me it isnow broken.Article ID: 260652PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"May Hold Locks Longer http://support.microsoft.com/kb/260652/Article ID: 828096FIX: Key Locks Are Held Until the End of the Statement for Rows ThatDo Not Pass Filter Criteria http://support.microsoft.com/kb/828096/Anybody else have this issue, or have any "eazy" solutions?The proc cursors thru a list and runs a proc on each item in the "worklist".This is an existing systemwith no plans to turn the process into a set oriented one,as is going away shortly.
View 4 Replies View RelatedIn SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!
View 1 Replies View RelatedI have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
Hi,I'm having trouble getting a stored procedure to return a single
integer value. Here's a short
version:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set ANSI_NULLS ONset
QUOTED_IDENTIFIER ONGOALTER PROCEDURE
[dbo].[Perm_Import_CJ]AS/* bunch of stuff removed */DECLARE
@NoCategory intSELECT @NoCategory = COUNT(*) FROM table WHERE CategoryID IS
NULL/* print @NoCategory */RETURN
@NoCategory~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~If I uncomment "print
@NoCategory" it prints exactly the number it's supposed to, so there is no
problem with any of the queries in the stored procedure. Then, in the code,
this is what I'm doing:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Dim dbConn
As New
SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionName").ConnectionString)Dim
cmd As New SqlCommand("StoredProc", dbConn)cmd.CommandType =
CommandType.StoredProceduredbConn.Open()Dim intNoCategory As Integer =
CInt(cmd.ExecuteScalar())dbConn.Close()~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~But,
and here's the problem ---> Even though @NoCateogry prints as the correct number, by the time it gets to intNoCategory in the code, it is ALWAYS zero.I have no idea what I am doing wrong.
Thanks in advance for any help!Casey
I need a stored procedure to return a value to my .NET app (ascx). The value will tell the app if the stored procedure returned any values or not. For example, if the Select SQL statement in the stored procedure returns no rows, the stored procedure could return a zero to the .NET app, otherwise it could return the number of rows or just a one to the .NET app.Anyone know how to do this?Thanks!
View 1 Replies View RelatedHi Folks
How do I get a return value ( a string, not int32) from an insert on a proc? This is what I've done so far:
Thanks in advance
The proc:
CREATE PROCEDURE Proc_Course_Event@action varchar(30)='',@CourseID int='0'
AS
if @action = 'ins_event' begin INSERT INTO Tbl_Course_event ( CourseID) VALUES (@CourseID) Select ReturnEventID = EventID from Tbl_Course_Event where id = @@Identity
endGO
The SQLDataSource Control:
<asp:SqlDataSource ID="Sql_Course_Names" runat="server"
ConnectionString="<%$ ConnectionStrings:TNAConnectionString %>"
SelectCommand="Proc_Course" SelectCommandType="StoredProcedure"
InsertCommand="Proc_Course_Event" InsertCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="Course_Names" Name="action" Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter DefaultValue="ins_event" Name="action" Type="String" />
<asp:Parameter DefaultValue="CourseID" Name="CourseID" Type="String" />
<asp:Parameter Name="ReturnEventID" Type="String" Direction="Output" />
</InsertParameters>
</asp:SqlDataSource>
The code-behind:
Protected Sub Sql_Course_Names_Inserted(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _
Handles Sql_Course_Names.Inserted
Dim EventID As String = Convert.ToString(e.Command.Parameters("@ReturnEventID").Value)
Response.Write(EventID)
End Sub
Hi. I'm sorry to bother all of you, but I have spent two days lookingat code samples all over the internet, and I can not get a single oneof them to work for me. I am simply trying to get a value returned tothe ASP from a stored procedure. The error I am getting is: Item cannot be found in the collection corresponding to the requested name orordinal.Here is my Stored Procedure code.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoALTER PROCEDURE [dbo].[sprocRetUPC]@sUPC varchar(50),@sRetUPC varchar(50) OUTPUTASBEGINSET NOCOUNT ON;SET @sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =@sUPC)RETURN @sRetUPCENDHere is my ASP.NET code.Protected Sub Page_Load(ByVal sender As Object, ByVal e AsSystem.EventArgs) Handles Me.LoadDim oConnSQL As ADODB.ConnectionoConnSQL = New ADODB.ConnectionoConnSQL.ConnectionString = "DSN=BarcodeSQL"oConnSQL.Open()Dim oSproc As ADODB.CommandoSproc = New ADODB.CommandoSproc.ActiveConnection = oConnSQLoSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProcoSproc.CommandText = "sprocRetUPC"Dim oParam1Dim oParam2oParam1 = oSproc.CreateParameter("sRetUPC",ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamOutput, 50)oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")Dim resres = oSproc("sRetUPC")Response.Write(res.ToString())End SubIf I put the line -oSproc.Execute()above the "Dim res" line, I end up with the following error:Procedure or function 'sprocRetUPC' expects parameter '@sUPC', whichwas not supplied. I thought that oParam2 was the parameter. I was alsounder the assumption that the return parameter has to be declaredfirst. What am I doing wrong here?
View 8 Replies View Relatedhi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
HiI'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@@rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure. thanks
View 1 Replies View RelatedHow do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]
(
@QuarterString nvarchar(10),
@TheQuarterId int output
)
AS
BEGIN
SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString
END
I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out whatthe problem is? Can someone please help me out?
I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it withsuccess. If I trace into the C# code though only a negative one (-1) is returned.
On line 5 I have tried the alternate lines of code but this has not worked for me.
mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output; mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue;
Can someone please help me out. I have spent to much time trying to figure this one out.
// C# code to call stored proc.1 try2 {3 SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);4 mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;5 mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;6 mySqlCommand.CommandType = CommandType.StoredProcedure;7 RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();8 }
// Stored procedure below.USE [REALTOR]GO/****** Object: StoredProcedure [dbo].[GetRecordAssociatedWithRealtor] Script Date: 10/18/2007 13:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]( @RealtorId int, @TotalRecords int output)AS BEGIN DECLARE @HouseDetailRecords int DECLARE @RealtorRecords int SELECT @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId SELECT @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId SET @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords) RETURN @TotalRecordsEND
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
Good morning, all,
OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.
I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.
My dataflow has the following components:
An Excel Source, containing my test data, consisting of just one row of data,
which points to a
Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.
which points to a
Data Conversion Transformation, which takes care of all the datatype conversions
which points to a
OLE DB Command, which contains the following as the SQL Command:
exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
In the OLE DB Command, I have mapped returnValue, my user variable to @RETURN_VALUE.
Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.
How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.
Thanks,
Kathryn
I have a stored procedure that takes a computer name (nvarchar) and either updates a time stamp in a matching row or adds a new row when no match is found based on the computer name (replicates a set of rows in another table as well in the case of not found).
When the row is unmatched, an output param (int) is set to 1 indicating it is new. When found, a zero is placed into the output parameter.
This stored procedure worked fine until we recently upgraded to SQL Server Express (2005).
C++ code:
_bstr_t conn_str = CONN_STR;
conn_str += (LPCTSTR)_server_name;
try
{
_ConnectionPtr epi_conn;
_CommandPtr cmd("ADODB.Command");
_ParameterPtr param;
long addfg;
_variant_t var_addfg;
//
// connect to database
//
epi_conn.CreateInstance(__uuidof(Connection));
epi_conn->Open(conn_str,"","",adConnectUnspecified);
cmd->ActiveConnection = epi_conn;
cmd->CommandText = "qry_DBUpdWorkstation";
cmd->CommandType = adCmdStoredProc;
//
// setup stored procedure call
//
param = cmd->CreateParameter (_bstr_t("@s_computer_name"), adVarChar, adParamInput, 31);
cmd->Parameters->Append(param);
param->Value = client_name;
param = cmd->CreateParameter (_bstr_t("@l_addfg"), adInteger, adParamOutput, 4);
cmd->Parameters->Append(param);
//
// debug output
//
if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 1 client=%s",
cur_dt.Format(), client_name);
fflush(trace_fp);
}
cmd->Execute(NULL, NULL, adCmdStoredProc);
var_addfg = cmd->Parameters->Item[_variant_t("@l_addfg")]->Value;
addfg = var_addfg.iVal;
//
// done with stored proc
//
epi_conn->Close();
if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 2 var_addfg.vt=%d, var_addfg.iVal=%d",
cur_dt.Format(), var_addfg.vt, var_addfg.iVal);
fflush(trace_fp);
}
and the stored proc:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
-- Database Version 5.5.0
ALTER PROCEDURE [dbo].[qry_DBUpdWorkstation]
@s_computer_name nvarchar(31),
@l_addfg int OUTPUT
AS
SET NOCOUNT ON
DECLARE @d_update_dt datetime
DECLARE @l_msg_cd int
DECLARE @b_view_local bit
DECLARE @b_view_global bit
DECLARE @l_alert_type smallint
DECLARE @s_sound_file nvarchar(255)
DECLARE @l_wscd int
SELECT @d_update_dt=UpdateDT
FROM dbo.tblWorkstations
WHERE ComputerNameTxt=@s_computer_name
SET @l_addfg = 0
IF @d_update_dt IS NULL
BEGIN
INSERT tblWorkstations (ComputerNameTxt, UpdateDT, OnlineFg)
VALUES (@s_computer_name, GETDATE(), 0)
SET @l_wscd = SCOPE_IDENTITY()
DECLARE msg_cursor SCROLL CURSOR FOR
SELECT SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt
FROM tblMsgAlerts
WHERE WorkstationCd = -1
OPEN msg_cursor
FETCH FIRST FROM msg_cursor INTO
@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file
WHILE @@fetch_status = 0
BEGIN
INSERT tblMsgAlerts (WorkstationCd, SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt)
VALUES (@l_wscd, @l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file)
FETCH NEXT FROM msg_cursor INTO
@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file
END
CLOSE msg_cursor
DEALLOCATE msg_cursor
SET @l_addfg = 1
END
ELSE
BEGIN
UPDATE tblWorkstations
SET UpdateDT = GETDATE()
WHERE ComputerNameTxt=@s_computer_name
END
The stored proc ALWAYS returns 0 but will execute the code to insert the new row when not found and replicate the rows in the second table. Any ideas, suggestions?
Thanks
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.
I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.
The code that is not working is listed below. It does not work. It returns Null. I suck.
DECLARE @column_names varchar(8000)
SET @column_names = ''
SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'
SELECT @column_names
little help?
I have a search stored proc which fails to return results when called by more than one user.
I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.
This seems to be consistent.
We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.
The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.
Anyone know of any problems or can suggest anything to try?
Hi everyone,
Does anyone know of a SQL stored proc that when given a operating system filename (i.e. a text file), returns the size of the file in bytes.
Thanks in advance,
Jim
What is the recommended method of returning a formatted date from a stored procedure?
The date is held in a date time field. I wish to return the date formatted as:
dd/mm/yyyy hh:mm
for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:
dd/mm/yyyy hh:mm:ss
I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.
I was looking at:
select jobHeaders.DateTimeJobTaken AS [Job Taken],
CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))
from jobHeaders
but this gives :
8 /3 /2004 with spaces.
Before looking further I thought one of you guys may have the answer.
Thanks in advance
I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{ rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch { rowsAffected = -1;}
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
View 1 Replies View RelatedHi,
I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.
I know that in DTS you could use the return value of "execution status" to do this.
Is there a way in SSIS to loop based on a return value?
Thanks much
I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:
Code Snippet
Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.
Task failed: SendDataToAs400 1
Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Load_ELEP.dtsx" finished: Failure.
I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.
Dear All
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave