I have a stored procedure that has a bunch of input and output parameters because it is Inserting values to multiple tables. In some cases the stored proc only inserts to a single table (depending on the input parameters). Here is a mocked up scenario to illustrate. Tables / Data Objects: Person Person.Id Person.Name Person.Address N...
I have a situation where I want to have a stored proc returning a table that calls itself recursively as part of its calculation. Unfortunately SQL Server is having none of this and gives me an error along the lines of both being unable to declare a cursor that already exists and about not being able to nest and insert exec statement....
I have a SQL Server 2008 stored procedure that updates values in a table. I would like to have the stored procedure return an integer value indicating that the update was successful (return 0) or not (returns error number). What would be the best way to accomplish this via ADO and VBA? Here some of my code in simplified form that perform...
SQL Server 2000. Is there any SP can list what databases are owned by a specific login(role)? Like: EXEC sp_xxxx 'myloginname' I want to see a set of database names that's owned by myloginname. Thanks. ...
Hi all, In banking sector, they use stored procedures for business logic. Their logic is moved in the db instead in business logic layer. What is the reason that the banks insists for stored procedures ? Regards ...
I am using System.data.Oracleclinet.OracleCommand.ExecuteNonQuery() to run my stored procedure. I observer the following strange thing. When execution runs throught COmmand.executeNonQuery it is giving 1 as the return value 1. But the stored procedure is not run and the output variable shows empty value({}). But if I run the same(comm...
How To process a result in a stored procedure Suppose i have sql = "SELECT fname, lname FROM students WHERE name ='Sam' "; How will i fetch this result in variable and loop in a SP like we do in PHP as: $sql = "SELECT fname, lname FROM students WHERE name ='Sam' "; $qSql = mysql_query($sql); if ($qSql ) { $fArr = mysql_fetch_array(...
Goal Invoke a CREATE TEMPORARY TABLE statement in Hibernate without using native SQL. That means using HQL or Hibernate APIs only. Save objects to the temporary table. Invoke a stored procedure which makes use of existing tables and the temporary table. DROP the temporary table when finished. (I know it's not necessary, but I think it'...
I don't think it is possible to do so what I would but I ask anyway. I've found that I include the same variables in the top of every Stored Proc I make. These variables are used for logging and error handling. They don't change between stored procs, there meaning if fixed but primary use is to help readability and have a consistent sty...
Hi there, I'm bugfixing someone else's code where it takes ages to return the complete dataset in the following code: DataTable dt = someLib.GetDataTable("EXEC [dbo].[CMS_Content_GetAllContents]"); // Copy the DataTable data to list. foreach (DataRow dr in dt.Rows) { ContentInfo aContentDetail = new ContentInfo( (in...
I have a stored procedure which is declared as follows: ALTER PROCEDURE dbo.thisProc @ID int,@TypeID int, @DocID int, @Section varchar(10) What I need is to be able to do this: If @ID is supplied, execute a particular if block if @ID is not supplied then move check if @TypeID is input and then execute another if block. I don't ...
Hi I am trying hard to write a stored procedure in ISeries DB2 but having errors. create procedure pakretst.fttest2 (IN fExpression CHARACTER(10)) language sql reads sql data dynamic result sets 1 begin declare stmt VARCHAR(50); declare x cursor for sl; If ftExpression IS NOT NULL set stmt='select * from pakretst.uwftrtystp W...
Hello guys, getting really close to running my first stored procedure. This one compiles but when I run it with call test.fttest5('YEAR'); it throws an error SQL State: 22001 Vendor Code: -303 Message: [SQL0303] Host variable *N not compatible. Cause . . . . . : A FETCH, SELECT, CALL, SET, VALUES INTO, GET DIAGNOSTICS, GET DESCRIPTO...
I have the following table in my database: tbl1 PK ClientID ScheduleDay Time1Start Time1Stop Time2Start Time2Stop Time3Start Time3Stop Status Here is some sample data ID ClientID ScheduleDay Time1Start Time1Stop Time2Start Time2Stop Time3Start Time3Stop -- -------- ----------- ---------- --------- ---------- --------- ---------- -...
I'm working on a .NET component that gets a set of data from the database, performs some business logic on that set of data, and then updates single records in the database via a stored procedure that looks something like spUpdateOrderDetailDiscountedItem. For small sets of data, this isn't a problem, but when I had a very large set of ...
I know that you can return multiple results from a stored procedure and through the method generated by the designer. However, I'm trying to do the same using ExecuteQuery but it doesn't seem like it's possible. Has anyone tried or know whether this is possible? Basically I'm trying to run an ad-hoc stored procedure. By ad-hoc, I mean...
Does anyone know where I can find a list of supported features for SQL Server Compact Edition? I am interested in using the new version with my web app but am trying to determine if it can do what I need? specifically I want to know if it can support views and stored procedures but the microsoft site was futile. thanks in advance Edit:...
I have a need to run a recursive CTE within a stored proc, but I can't get it past this: SQL0104N An unexpected token "with" was found following "SET count=count+1; ". Expected tokens may include: "". LINE NUMBER=26. My google-fu showed a couple of similar topics, but none with resolution. The query functions as expected outside of the...
I have the strange aversion to passing in multiple ID parameters to a single stored procedure. For example, this feels just wrong: GetMyObject(ListofIDs, OtherParam1, OtherParam2, ...) I understand HOW to do it (correctly if I must).. but I don't feel like I should do it. I feel like it defeats the purpose of a "get item" stored proced...
I have an SP like so (using SQL Server): ALTER PROCEDURE [dbo].[sp_ClientNotes_update] @id uniqueidentifier, @ordering smallint = NULL, @title nvarchar(20) = NULL, @content text = NULL AS BEGIN SET NOCOUNT ON; UPDATE tbl_ClientNotes SET ordering=@ordering, title=@title, content=@content WHERE id=@id END ...