stored-procedures

Coding stored procedure for search screen with multiple, optional criteria

I've got a search screen on which the user can specify any combination of first name, last name, semester, or course. I'm not sure how to optimally code the SQL Server 2005 stored procedure to handle these potentially optional parameters. What's the most efficient way? Separate procedures for each combination? Taking the items in as null...

MySQL Stored Procedure add row and return key

The question I have been asked for this assignment is: Using a sample data, add a new incident and a call record in a single transaction. However I know it doesn't read to well and that's because of the language that my lecturer uses. But from what I gather he would like me to add a record to an incident table and...

SQL Server SELECT/UPDATE Stored Procedure Weirdness

I have a table I'm using as a work queue. Essentially, it consists of a primary key, a piece of data, and a status flag (processed/unprocessed). I have multiple processes trying to grab the next unprocessed row, so I need to make sure that they observe proper lock and update semantics to avoid race condition nastiness. To that end, I'...

Counting results of stored procedure

Hi, I have a stored procedure returning ID, Name, Descriptions and takes no input parameters. However, I am interested in how many results do I get. I expected something like this work: SELECT COUNT(*) FROM EXEC MyStoredProcedure But I get the following error in SqlServer Managment Studio: Incorrect syntax near the keyword 'EXEC'. ...

Triggers and stored proc in MySQL

I used MSSQL stored procedures and triggers for a while; MySQL is getting me absolutely crazy about how to write even the simpler procedure. Why I get a syntax error in this so stuoid trigger? CREATE TRIGGER set_prio_default BEFORE INSERT ON categories FOR EACH ROW BEGIN set @my_prio := 1; SET new.prio := @my_prio; END In facts...

how save SQL stored prodecures to .sql files via batch

Hello! I woud like to save my MS SQL Server 2005 stored procedures to .sql files automatically (would prefer a tool which I can call via .bat) so I dont have to click each single sproc manually and save it. I have already found SMOscript from devio IT, but it gathers all tables and sproc which takes some time. Is there any similar tool ...

Stored Procedure parameter inserting wrong value

Good afternoon everyone, I am having an issue with a stored procedure inserting an incorrect value. Below is a summarization of my stored procedure ... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[InsertDifferential] @differential int = null AS BEGIN TRY BEGIN TRANSACTION UPDATE Dif...

How do I suppress the results from a stored procedure from within a stored procedure?

I've got a stored procedure (we'll call it A) that calls another stored procedure (we'll call this one B). B includes a SELECT that I do not want to send back to the caller of A. Here is some really rough pseudocode, but it should get the idea across. PROCEDURE A CURSOR CALL B -- I WANT TO SUPPRESS THE RESULTS FROM B EN...

Cursor with Stored Procedure Question

Hi everyone, this is my first time using this site. OK, i need to use a cursor to call a stored procedure that has 2 parameters that i need to pass into from Customers table. Here is what i mean; My goal is to pass all the CustomerID and CustomerName from Customers table into my stored procedure called AddCustomers which has 2 paramet...

How to Manage SQL Source Code?

I am in charge of a database. It has around 126 sprocs, some 20 views, some UDFs. There are some tables that saves fixed configuration data for our various applications. I have been using a one-big text file that contained IF EXIST ...DELETE GO CREATE PROCEDURE... for all the sprocs, udfs, views and all the insert/updates for the confi...

Can someone please help with this SQL 2005 stored proc?

I am having trouble with a stored proc (SQL 2005). I have a table called tbrm_Tags with two columns, TagID and TagName. I want to pass a TagName value to the stored proc and then I want to : 1) Check if the Tagname exists and if it does return the TagID 2) If the Tagname does not exist I want it to insert into the table and return th...

C#/SQL - What's wrong with SqlDbType.Xml in procedures ?

Hi, I've asked few people why using xml as a parameter in stored procedure doesn't work and everyone said , that's just the way it is. I can't belive that. command.Parameters.Add("@xmldoc", SqlDbType.Xml); That's where compiler returns error and I can't use NVarChar beacouse it's limiteed to 4k sings. XML would be perfect as it can b...

VS 2005 cannot view Sybase SPROCs?

Hi, Im using the VS2005 Table Adapter query wizard to design my datasets. However, I cannot see any SPROCS on my sybase database when selecting ' use existing stored procedure ' In the drop down supplied by the wizard it allows me to see all the functions that are in Sybase but not the SPROCS. Im logging also as DBA purely to see if ...

Useful system stored procedures in SQL Server

I recently discovered that I could use the sp_help to get a table definition and have been hooked onto it since then. Before my discovery, I had to open up the Object explorer in SQL Management studio, manually search for the table name, right click on the table and select Design. That was a lot of effort! What other system stored proce...

Delete in Stored procedure

My delete query does not work conditionaly, it deletes all the records from tne table PROCEDURE "SP_NEW" ( logon_id IN VARCHAR2, id IN VARCHAR2, key IN VARCHAR2, error_code OUT NUMBER, error_message OUT VARCHAR2) ... PROCEDURE delete_counts(str_logon_id IN VARCHAR2) IS BEGIN DELETE FROM TMS_ENTITY_COUNT WHER...

how to avoid sqlcmd blank line between Resultsets?

hello, refering my last question on extracting SQL stored procedures into .sql files (see here) I have another question: how to avoid or delete the sqlcmd blank line between Resultsets? Reason (see MSDN) When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. This means that stored procedures...

SQL Server Procedure returns multiple tables - Insert results into tables

I have a procedure that returns multiple tables; eg: PROCEDURE Something AS BEGIN SELECT 1,2,3 SELECT 4,5 SELECT 9,10,11 END I would like to take each table from the result and insert it into a series of tables/temp tables - one for each record set. Is this possible? ...

Performance implications of sql 'OR' conditions when one alternative is trivial ?

I'm creating a stored procedure for searching some data in my database according to some criteria input by the user. My sql code looks like this: Create Procedure mySearchProc ( @IDCriteria bigint=null, ... @MaxDateCriteria datetime=null ) as select Col1,...,Coln from MyTable where (@IDCriteria is null or ID=@IDCriteria) ... and (@Max...

What is the best approach to calculate a formula which changes value each day?

I use the following columns stored in a SQL table called tb_player: Date of Birth (Date), Times Played (Integer), Versions (Integer) to calculate a "playvalue" (integer) in the following formula: playvalue = (Today - Date of Birth) * Times Played * Versions I display upto 100 of these records with the associataed playvalue on a webpa...

SQL: Select TOP N Marks Per User (In a List of Users)

I am having difficulty writing a Stored Procedure that will query a list of students with their associative marks. Retrieving a List of Students - trivial Retrieving the top five marks per student - trivial...SELECT TOP (5) * WHERE StudentID = X Combining these two, I am a bit confused. I would like the Stored Procedure to return two ...