stored-procedures

Entity Framework 4 - Calling a Select / Paging Stored Procedure

Hi guys and girls, I am just starting out with the Entity Framework 4.0 and ASP.NET MVC 2 and have a few questions regarding the use of stored procedures and paging. You can map the Insert, Update, and Delete actions to stored procedures and I have already done this. However for my paging to work I need to map the Select action. Now ...

PL/SQL Sum by hour

Hi, I have some data with start and stop date that I need to sum. I am not sure how to code for it. Here are is the data I have to use: STARTTIME, STOPTIME, EVENTCAPACITY 8/12/2009 1:15:00 PM, 8/12/2009 1:59:59 PM, 100 8/12/2009 2:00:00 PM, 8/12/2009 2:29:59 PM, 100 8/12/2009 2:30:00 PM, 8/12/2009 2:59:...

Do I have to use Stored Procedures to get query level security or can I still do this with Dynamic Sql?

I'm developing an application where I'm concerned about locking down access to the database. I know I can develop stored procedures (and with proper parameter checking) limit a database user to an exact set of queries to execute. It's imperative that no other queries other then the ones I created in the stored procedures be allowed to ...

MySQL db Audit Trail Trigger

I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the proce...

Passing a DataTable into a Stored Procedure. Is there a better way?

Can A datatable somehow be passed into SQL Server 2005 or 2008 ? I know the standard way seesm to be passing XML to a SP. And a datatable can easily be converted to XML somehow to do that. What about passing a .NET object into a SP ? Is that possible ? I remember hearing about SQL and CLR working together in 2008 somehow but I never...

Is it possible to run an alter on all my database objects to test them

I'm in the course of doing some schema migrations, and would like to know if it's possible or advisable to run every stored procedure, view and function in my database as an alter statement to "compile" them all to make sure nothing is completely broken. ...

SubSonic missing stored procedures in StoredProcedures.cs when generated with SubCommander sonic.exe

We have been using SubSonic to generate our DAL with a lot of success on VS2005 and SubSonic Tools 2.0.3. SubSonic Tools do not work on VS2008 (as far as we can work out) so we have tried to use SubCommander\sonic.exe and are now hitting some problems. When we regenerate the project using SubCommander\sonic.exe and try to compile we ge...

Integer in MySQL Subqueries in store procedure

I made simple procedure just to demonstrate CREATE PROCEDURE `demo`(demo_int int) BEGIN DECLARE minid INT; SELECT min(id) FROM (SELECT id FROM events LIMIT demo_int,9999999999999999) as hoo INTO minid; END$$ The problem is with demo_int, if i change it to LIMIT 1,9999999999999999 it works but LIMIT demo_int,9999999999999999 D...

How do I get a scalar value from a stored proc using Nettiers

I have a really simple stored procedure that looks like this: CREATE PROCEDURE _Visitor_GetVisitorIDByVisitorGUID ( @VisitorGUID AS UNIQUEIDENTIFIER ) AS DECLARE @VisitorID AS bigint SELECT @VisitorID = VisitorID FROM dbo.Visitor WHERE VisitorGUID = @VisitorGUID --Here's what I've tried RETURN @VisitorID 'Returns an IDataReader SE...

How do I use Entity Framework in a CLR stored procedure?

I am looking forward to move all the logic (which is implemented as manipulating Entity Framework 4 objects) to a server side. It looks going to be simple (thanks to the application structure) and beneficial (as all I have is one oldy laptop as a client and one tough server which runs SQL Server 2008, and building a separate service for ...

How to declare a variable in SQL Server and use it in the same Stored Procedure

Im trying to get the value from BrandID in one table and add it to another table. But I can't get it to work. Anybody know how to do it right? CREATE PROCEDURE AddBrand AS DECLARE @BrandName nvarchar(50), @CategoryID int, @BrandID int SELECT @BrandID = BrandID FROM tblBrand WHERE BrandName = @BrandName INSERT INTO tblBrandinCategor...

Stored Procedure Parameters Not Available After Declared

Hi All, Pasted below is a stored procedure written in SQL Server 2005. My intent is to call this sproc from my ASP.NEt web application through the use of a wizard control. I am new to SQL Server and especially to stored procedures. I'm unsure why my parameters are not available to the web application and not visible in SSMS treeview ...

Have I to count transactions before rollback one in catch block in T-SQL?

I have next block in the end of each my stored procedure for SQL Server 2008 BEGIN TRY BEGIN TRAN -- my code COMMIT END TRY BEGIN CATCH IF (@@trancount > 0) BEGIN ROLLBACK DECLARE @message NVARCHAR(MAX) DECLARE @state INT SELECT @message = ERROR_MESSAGE(), @state = ERROR_STATE() ...

Error: Too Many Arguments Specified when Inserting Values from ASP.NET to SQL Server

Good Afternoon All, I have a wizard control that contains 20 textboxes for part numbers and another 20 for quantities. I want the part numbers and quantities loaded into the following table: USE [Diel_inventory] GO /****** Object: Table [dbo].[QUOTEDETAILPARTS] Script Date: 05/09/2010 16:26:54 ******/ SET ANSI_NULLS ON GO SET QUOT...

OdbcCommand on Stored Procedure - "Parameter not supplied" error on Output parameter

I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error: Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied. @ID is the OUTPUT parameter for my procedure, there is an input @machine which is specified and is set to null in the stored...

join condition depends on the parameter

Hi. I'm a sql newbie, I use mssql2005 I like to do join Action depnding on input parameter. CREATE PROCEDURE SelectPeriodicLargeCategoryData @CATEGORY_LEVEL CHAR(1), @CATEGORY_CODE VARCHAR(9) AS ... JOIN CATEGORY_AD_SYS CAS WITH(NOLOCK) ON CA.CATEGORY_ID = [[[[[ HERE ]]]] above the sql. if @CATEGORY_LEVEL = 'L' the...

where condition depending on parameter

Hi. I'm a sql newbie, I use mssql2005 I like to do select with the condition which is depending on the input parameter I've tried this. WHERE CF.PROCESS_DATE = '2010-05-05' AND CASE @CATEGORY_LEVEL WHEN 'L' THEN CAS.MCATEGORY_ID = '' AND CAS.SCATEGORY_ID = '' WHEN 'M' THEN CAS.SCATEGORY_ID = '' END but didn't work and ha...

How do I get an stored procedure output into a variable inside a function in T-SQL?

I've got a task which can be only accomplished by construction a QUERY at runtime and executing it with sp_executesql. The result has to be a boolean (0/1 integer) value which I need to return as a result of the function. The only way I found to capture an SP's output is "INSERT INTO [table] EXECUTE [sp]" query, but functions forbid thi...

How to return table name from stored procedure in dataset.

I used a dataset to store 15 tables that I need at the time of loading. When i filled all the tables using stored procedure it returns me all the table but name of the table doesn't comes as that of actual table name in a database. It takes all the table with table name as Table1, Table2, Table3... I want them to be with the name as t...

Java class in DB

Hi! Can anyone explain why was idea to store Java class in Db? What it is good for? And how to create stored procedure with Java class? Best regards! ...