stored-procedures

MySQL: Passing procedure params to EXECUTE USING statement

This is MySQL 5.1. (Note: I realize there are better ways of doing this particular example, this is not my real code) Here is what I want to do: The below procedure gets created, but when I CALL it, I get "ERROR 1210 (HY000): Incorrect arguments to EXECUTE" DELIMITER // CREATE PROCEDURE get_users_by_state(IN state CHAR(2)) READS S...

What Access Rights am I Missing on my Stored Procedure

I'm trying to run a stored procedure from my website that disables a trigger. Here is the code for the trigger: CREATE PROCEDURE [dbo].[DisableMyTrigger] AS BEGIN alter table dbo.TableName DISABLE TRIGGER TriggerName END I've also set the permissions on the stored procedure with: Grant Exec on dbo.DisableMyTrigger To DBAccountNam...

How to create 20 databases at a time in sqlserver?

Hi, I have written the following procedure to create a series of 20 databases. But it is not creating them. The database is not created even when I go to my H: drive and didnot find the ldf and mdf named bharath1 bharath2 like... What did I do wrong with this? USE master GO DECLARE @command varchar(1000), @i int while @i < 5 SET @...

MYSQL Stored Procedures If statement Problem

I'm working with Mysql 5.1.28-rc on freebsd. I've just decided to use stored procedures in MYSQL and created a test procedure as below: DELIMITER $$ DROP PROCEDURE IF EXISTS test $$ CREATE PROCEDURE test( IN test VARCHAR(22) ) BEGIN DECLARE count INT(11); SET count = (SELECT COUNT(*) FROM Test WHERE test_column = test ); SELECT coun...

SQL Server Transactional Replication - How do I use a stored procedure to synchronise a subscriber

Hi, I have two servers, one in the office and another on a dedicated server that drives our website. The office runs SQL Server 2005 and the dedicated server is 2005 Express. Until now I've automated backing up our office database, FTP'ing it to the dedicated server and restoring it. This has worked fine but it's now taking around 45 ...

Oracle - return newly inserted key value.

We have a table with a Primary Key that gets populated on insert by a trigger on the table - the trigger gets the next sequence number from a sequence we created for the table and uses that for the value of the key on insert. Now we would like to be able to return that value in our insert procedure (PL\SQL), similar to select @@scope_ide...

Dynamic order direction

I writing a SP that accepts as parameters column to sort and direction. I don't want to use dynamic SQL. The problem is with setting the direction parameter. This is the partial code: SET @OrderByColumn = 'AddedDate' SET @OrderDirection=1; . . . ORDER BY CASE WHEN @OrderByColumn='AddedDate' THEN CONVERT(varchar(50),AddedDate)...

how to retrieve thousands of rows from a store procedure efficiently

Hello everyone, I am using VSTS 2008 + C# + .Net 3.0 + ADO.Net + SQL Server 2008. And from ADO.Net I am invoking a store procedure from SQL Server side. The store procedure is like this, SELECT Table1.col2 FROM Table1 LEFT JOIN Table2 USING (col1) WHERE Table2.col1 IS NULL My question is, how to retrieve the returned rows (Table1.col...

What is the best way of determining whether our own Stored procedure has been executed successfully or not.

Hi I know some ways that we can use in order to determine that whether our own Stored procedure has been executed successfully or not. (using output parameter, putting a select such as select 1 at the end of the stored procedure if it has been executed without any error, ...) so which one is better and why? ...

MERGE output cursor of SP into table?

Hi, i have a Stored Procedure which returns output as a ref cursor. I would like to store the output in another table using the MERGE statement. I'm having problems however mixing all the statements together (WITH, USING, MERGE etc..). Can somebody assist? Thanks! This is the table i want the output in (STOP_TIME is left out on purpo...

SQL user-defined functions vs. stored procedure branching

Hello. I currently am working on a legacy application and have inherited some shady SQL with it. The project has never been put into production, but now is on it's way. During intial testing I found a bug. The application calls a stored procedure that calls many other stored procedures, creates cursors, loops through cursors, and many ot...

Cursor-fetched variable in prepared statement within a stored procedure?

Hullo, Can any help me shed some light on what's going on when I try to prepare a statement from a string that contains a cursor-fetched variable? Below is the procedure that I'm trying to create. When I create it without the prepared statement, and replace those three lines with SELECT @export_sql_stmt, the output verifies that the S...

Is there any perfomance issue with Inner Join?

Hi, Currenlty I am using lot of inner join's(around 7) in my sp, does it have any impact on sp perfomance. does left outer join gives better perfomance then inner join. one more thing if i m joining two tables a and b which has column id and id1, both r not nullable. i suppose here i can go for inner join as these columns r indexed. ...

Update DB column that is specified at runtime

Hi, I'm trying to update a column in a database, but the column needs to be specified at runtime. For example, given the table: | PKEY | ColumnA | ColumnB | ColumnC | I want to write an stored procedure that will update a given column per PKEY. Such as: updateColumn(pkey, columnName, Value); This could easily be done using java/JDBC...

Invalid length parameter passed to SUBSTRING function

Hey all, Having a bit of a run in with SQL and Transactions... hoping some of you can shine a light on the problem. (and maybe even fix my screwed html! Does this site support formatting for different languages? Perl, SQL, Java?) This server runs on SQL Server 2005 and was very recently upgraded from SQL Server 2000. I'll keep an eye on...

SP: Get 5 random records plus 1

Hi, I need to get 5 random records from a table plus a further record based on data from the users preferences as stored in a second table. Here are the two statements i have created so far: Gets favourite charity based on key set in TBL_MEMBERS: SELECT DISTINCT TBL_CHARITIES.* FROM TBL_CHARITIES JOIN TBL_MEMBERS ON TBL_CHARITIES.cha...

A better way of passing parameters to a TADOStoredProc (Delphi)

Hi, I am needing to convert a large amount of SQL queries into stored procedures. I have some code that updates about 20 or 30 values at one time in one Delphi procedure. I can handle creating a stored procedures to do such a thing. The problem is my way to pass parameters to stored procedures is very bulky like this: with stored_pr...

SQL Server Stored Procedure Folders/Grouping

We are currently using SQL Server 2000 but will soon be moving to 2008. I am looking for a way to group related stored procedures into folders. SQL Server 2000 does not seem to have this ability and from my searches it looks like 2008 does not either. This seems like a basic feature that most users would want. Wouldn't it make sense to...

Strange problem in SQL job and stored procedure

I have a stored procedure which will pick MQ files from receive location, process it and drop it in save location. Currently, this stored procedure is not working when I execute it manually. But files are picked up and saved in the save location automatically at a particular time of day daily. I don't know which stored procedure is cal...

How to retrieve machine IP adress via SQL Stored Procedure

Hi, I wanted to know if we can retrieve IPAddresses of any machine (can be local or remote) via Microsoft SQL Stored Procedure WITHOUT using INSERT EXECUTE. ...