stored-procedures

How to pass value to a stored procedure using C#

--Stored procedure ALTER PROCEDURE [dbo].[Test] @USERID varchar(25) AS BEGIN SET NOCOUNT ON IF NOT EXISTS Select * from Users where USERID = @USERID) BEGIN INSERT INTO Users (USERID,HOURS) Values...

Profile Stored procedures in MySQL

I am working with MySQL and using stored procedures. I have a profiling tool that I am using to profile the code that communicates with MySQL through the stored procedures and I was wondering if there was a tool or capability within MySQL client to profile stored procedure executions. What I have in mind is something that's similar to ru...

SQL Query Assistance

This is not a homework question, just something at work that's bugging me. I'm trying to write a query against these tables below. The two tables, history and code are joined to the historyassignment table by the historyid and codeid columns. Table: HistoryAssignment | AssignmentID | HistoryID | CodeID | |--------------|-----------...

MySQL Stored Procedure - IF/ELSE on the data returned

Essentially what I need to do is pull someone's age from the database which is stored as an integer and then perform a check as to whether they are over 18 or not. The simple select of the row returns their age. The goal is to have the Row return either a "yes" or "no" if they are over the age of 18. Normally I'd do this logic when the...

Calling a Stored Procedure dynamically using Groovy SQL

I've a grails application service where I'm trying to call a stored procedure dynamically. Here is a sample snipper of what I'm trying to do: myService.callProcedure (procedureName, inputParams) My stored procedure takes input and out params. How I can call a procedure on the fly without registering output parameters and then get all...

Sending parameters into stored procedure in SQL Server 2008

I have this stored procedure Usp_temp @temp nvarchar(50) Where city in (@temp) and I try to send a num of parameters like this Usp_temp '1,3,5' what is the right way to do this? ...

MYSQL Error 1064 when importing stored procedures

I'm importing a stored procedure which I just exported from my development server into my production one and I ran into the following error in phymyadmin. SQL query: Documentation $$ CREATE DEFINER = `devuser`@`localhost` FUNCTION `get_refundable_amount` ( enrol_id INT ) RETURNS double( 10, 2 ) READS SQL DATA BEGIN DECLARE refundable_a...

Test if Table has at least certain Stored Procedure, else fail test c#

This test checks if only one SP ends with Insert, Load, or Save, then it's successful. //Query to find SPs for a certain Table name string check_SP = "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " + "FROM INFORMATION_SCHEMA.ROUTINES " + "WHERE ROUTINE_DEFINITION LIKE '%CLNT_" + Table_Name + "%' " + "AND ROUTINE_TYPE='PROCEDU...

Handling tree in a MySQL procedure

The idea is simple - I have two tables, categories and products. Categories: id | parent_id | name | count 1 NULL Literature 6020 2 1 Interesting books 1000 3 1 Horrible books 5000 4 1 Books to burn 20 5 NULL Motorized vehicles 1000 6 ...

Stored Procedure and Permissions - Is EXECUTE enough?

I have a SQL Server 2008 database where all access to the underlying tables is done through stored procedures. Some stored procedures simply SELECT records from the tables while others UPDATE, INSERT, and DELETE. If a stored procedure UPDATES a table does the user executing the stored procedure also need UPDATE permissions to the affec...

What happens when I update SQL column to itself? -OR- simplified conditional updates?

I would like to call an "update" stored procedure which won't necessarily include all columns. There is probably a better way to handle this.... As you can see, if I do not pass in the column parameters their value is NULL. Then, using the ISNULL, I set the columns either to their new values or their existing values. CREATE PROCEDURE [d...

different between cfstoredproc and cfquery

I've found previous programmers using cfstoredproc in our existing project about insert records into database. Just example he/she used like that: <cfstoredproc procedure="myProc" datasource="myDsn"> <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="ppshein" dbvarname="username"> <cfprocparam type="In" cfsqltype="CF_SQL_CHA...

MySql stored procedures, transactions and rollbacks

I can't find an optimal way to use transactions in a MySql Stored Procedure. I want to rollback if anything fails: BEGIN SET autocommit=0; START TRANSACTION; DELETE FROM customers; INSERT INTO customers VALUES(100); INSERT INTO customers VALUES('wrong type'); COMMIT; END 1) Is autocommit=0 required? 2) If t...

Java: Send array to a PL-SQL function

I need to pass an array of objects to a PL-SQL function My ORACLE code: CREATE OR REPLACE TYPE uu.ITEMTAB AS TABLE OF ITEMREC; / CREATE OR REPLACE TYPE uu.ITEMREC AS OBJECT (ID NUMBER, NAME VARCHAR2(30)) / Java class public class ITEMREC { private int ID; private String NAME...

Simple Stored Procedure Question

I am creating a simple stored procedure in VS 2010/SQL Server 2008 as follows: CREATE PROCEDURE ReturnPrice @carID int @price decimal(18,2) output AS SELECT @price = Price FROM Cars WHERE CarID = @carID and I am receiving the following error message when attempting to save: Incorrect syntax near '@price' Must declare the scalar ...

SQL stored procedure equivalent to ODS e.InputParamters[] = x ?

Morning all, hope everyone is ok. I have an ODS that uses a combination of query string and Selecting event parameters. For the ODS, I'd input a paramater in the selection event a la: protected void oDs_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { e.InputParameters["memberid"] = memberid; } How w...

Stored procedure syntax Error(MSSQL)

Below mentioned stored procedure is giving error while creating Msg 156, Level 15, State 1, Procedure crosstab, Line 23 Incorrect syntax near the keyword 'pivot'. Can anyone please tell me the mistake? Below is the script: CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(...

More efficient double coalesce join alternative

I have a procedure with a (slightly more complex) version of the below: CREATE PROC sp_Find_ID ( @Match1 varchar(10), @Match2 varchar(10) ) AS DECLARE @ID int SELECT @ID = ID FROM Table1 WHERE Match1 = @Match1 AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'') SELECT @ID ID Essentially Match1 is a mandatory m...

Entity Framework 4 Function Import not working

I am using Entity Framework 4 with the POCO code generator. I have a stored procedure that does an INSERT and returns the @@IDENTITY of the inserted record. I am trying to import the stored procedure as a function in my .edmx file, but I am having trouble using it. In the model browser, I can see the stored procedure under the database ...

How to return bool from stored proc

Hi, I'm trying to work out how to write a store procdure which returns a boolean value. I started off writing the following one which returns an int. USE [Database] GO /****** Object: StoredProcedure [dbo].[ReturnInt] Script Date: 09/30/2010 09:31:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[Ret...