stored-procedures

Design query for following scenario

I have two tables - Table1: id name number ------------------ 1 x1 123 2 x2 234 ...and Table2: tbl1id title rank -------------------- 1 t1 3 1 t2 2 2 t1 3 1 t3 1 Is there a way I can join them to return result as showing max title based on min rank for given user: id name ...

Maintaining a metadata table in SQL

Can someone help giving me some direction to tackle a scenario like this. A User table which contains all the user information, UserID is the primary key on User Table. I have another table called for example Comments, which holds all the comments created by any user. Comments table contains UserID as the foreign key. Now i have to rank...

Getting the Return Value from JDBC MSSQL

I'm connecting to SQL Server (2005) through Java using the Microsoft SQL Server JDBC Driver 2.0. How do I get the return value from a stored procedure? I'm doing something like: Connection connection = dataSource.getConnection() CallableStatement proc = connection.prepareCall("{ call dbo.mySproc() }"); proc.execute(); Should I be us...

Views or table functions or something else.

I designed 5 stored procedures which almost use same join condition but parameters or values in where clause change for each on different runs. Is it best solution to create a view with all join conditions without where clause and then query from view or work on view? Can views auto update itself if i create view? Can i do sub-queries o...

Parameter passing Vs Table Valued Parameters Vs XML to SQL 2008 from .Net Application

As We are working on a asp .net project there three ways one can update data into database when there are multiple rows updation / insertion required Let's assume we need to update employee education detail (which could be 1,3,5 or 10 records) Method to Update Data Pass value as parameter (Traditional approach), If 10 records are the...

Batch Stored procedures insertion in database with Callable statement

Hi All, We have 4 stored procedures which we are using to insert the entries in database, These 4 are interdependent, If any of this fails Whole operation has to be rolled back, only if everything goes well I want to commit the transaction. How do I achieve that. Thanks, Rohit. ...

Using Stored Procedures in Rails

As I said in a previous post, our Rails app has to interface with an E-A-V type of table in a third-party application that we're pulling data from. I had created a View to make the data normal but it is taking way too long to run. We had one of our offshore PHP developers create a stored procedure to help speed it up. Now we run into ...

Check date interval with SQL Server

Hi in a stored procedure I want to select rows that are in an interval of a month where @Anno is the year of the start and @Mese is the month of the date start. I try this: WHERE FinePeriodo >= CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime) AND FinePeriodo < DATEADD(month, 10, CAST( (@Anno + '-' ...

Critique this SQL stored procedure.

Hey guys, below is a massive stored procedure that a contract developer wrote for me, I feel like I am picking on the developer but it is just terrible. What are the main issues you can see with it? CREATE PROCEDURE [dbo].[usp_SHS_XXXX] ( @request_identifier varchar(255), @category_guids varchar(4000), @url varchar(500)...

Insert binary data into SQL from c# without a stored procedure

Does anyone know whether if its possible to insert binary data into an SQL field from c# without using a stored procedure? For example - convert a byte array into base64 or something like that and then use a text command like the following... String.Format("update A set B = {0} where C = D", Convert.ToBase64String(b)); where b is a b...

How do I return a Unicode value in this SQL example?

I need to return Russian text from a SQL Server 2005 database table. In the following example which is a simple way of describing my dilemma, the @Test variable will print out question marks: DECLARE @Test nvarchar(max) SET @Test = 'Баннер' PRINT @Test (Note that the @Test value is Russian text, for those who don't have the font ins...

specify what columns not to insert values into mysql table

I have a stored procedure that inserts a row into a table with an autoincremented column. It gives me a warning but succeeds if i simply put "" for the value of the autoincremented column. The way I get rid of the error currently is to specify which columns the insert values are for... "insert into app_table(app_id,name,...) values(...)"...

How to retrieve multiple rows from a stored procedure with Linq to SQL?

I've recently started to work with Linq to SQL and wondered how to get multiple rows as a result of executing a stored procedure,here's a simple sp i want to work with: CREATE PROCEDURE gsp_ftsmultiple @SearchKey varchar(100) AS BEGIN SET NOCOUNT ON; SELECT Label, theContent FROM FtsTest WHERE FREETEXT( theContent, @Se...

Begginer Mysql 5.0 Stored Procedure Syntax question

I'm just trying to create my first mysql stored procedure and I'm trying to copy some examples almost directly from the documentation, but it isn't working: mysql> delimiter // mysql> CREATE PROCEDURE ghost.test (OUT param1 INT) INSERT into admins SELECT COUNT(*) FROM bans; END// ERROR 1064 (42000): You have an error in your SQL syntax;...

Mysql Command to view the definition of stored procedure ?

Mysql command to view the definition of stored procedure . I know sp_helptext is the command in MS-Sql to display the definition of stored procedure , is there a similar command in Mysql ? I am not looking for SHOW PROCEDURE STATUS which display the list of the procedures available for the particular database in mysql . Thanks ! ...

How do I exclude a specific row from a result set?

I want to let the user select a value from a combo box, and then exclude a row from the result set of a stored procedure based on that selection. Something like this: Select RegNo from Vehicle Except select VehicleID from Trip ...

Using sets as inparameters to function/sproc in SQL Server 2008?

Hello! Is it possible to use a set, like (1, 2, 3, 4, 5) for example, as inparameter to a Sproc, function or view in SQL Server 2008? What should I use of Sproc, function or View for this pice of SLQ? WITH Scores AS( SELECT ItemId, SUM(Score) AS Score FROM [Presenta].[presenta].[LpivScores] WHERE // HERE ...

Stored procedure not working in MYSQL

Hi All, I have created the following stored procedure but not working as expected.....Some error line 3 DELIMITER $$ DROP PROCEDURE IF EXISTS mytest.sp_insert_request $$ CREATE [email protected] PROCEDURE sp_insert_request(@RequestID varchar(250) = null, @Status varchar(45)= null,@Status_message varchar(9180)= null,@time_of_...

Problem using Query Analyzer to debug stored procedure

I am attempting to debug a stored procedure using the Debug option is SQL Query Analyzer (Version 8.00). However, the debugger run through the whole stored procedure as soon as I start and if add a break-point and try re-executing the sp, the break-point is ignored. What do I need to do to enable debugging? The server is SQL Server 2000 ...

jQuery AJAX & Multiple sp Result Sets

Is it possible to use a stored procedure that returns multiple result sets in json format and process them as part of one request using ajax calls in jquery? In other words, I have a stored procedure that returns several result sets that are to be used with a series of select boxes that are all being filtered by the same criteria. If...