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...
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...
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'...
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'. ...
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...
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 ...
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...
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...
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...
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...
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...
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...
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 ...
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...
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...
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...
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?
...
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...
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...
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 ...