Let's assume, we create a stored procedure that is supposed to retrieve customer details and that will be used in some sort of dashboard view. Since there are a couple of other stored procedures relevant for the dashboard as well, one might think about visually grouping the SPs by naming them accordingly: DASH_GetCustomerDetails DASH_....
Our development policy dictates that all database accesses are made via stored procedures, and this is creating an issue when using LINQ. The scenario discussed below has been somewhat simplified, in order to make the explanation easier. Consider a database that has 2 tables. Orders (OrderID (PK), InvoiceAddressID (FK), DeliveryAddre...
How do you find out how many store procedures that you have in the database and is it a bad practice to have too many store procedure? how many are too many? ...
I'm using Zend Framework 1.7.2, MySQL and the MySQLi PDO adapter. I would like to call multiple stored procedures during a given action. I've found that on Windows there is a problem calling multiple stored procedures. If you try it you get the following error message: SQLSTATE[HY000]: General error: 2014 Cannot execute queries ...
Is there a SQL command that could be used in a query, stored procedure, function, that would work against a Binary Type similar to the following C# code? if (someBinaryArray[index] == 0) { ... I'm wanting to check if an index of a position in the binary is a certain value instead of pulling the entire array down and doing the compari...
I have two SQL Server 2005 databases, one is for development and the other is on the final production server. I would like to know the fastest way of ensuring that the production database has the exact same stored procedures (number and most recent version). Assumptions: Databases have same table schema. Production database is curren...
I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting optio...
I have an Informix stored procedure that returns two columns and multiple rows. I can use "EXECUTE FUNCTION curr_sess(2009,'SP')" fine, but how do I get the results into a temp table. EDIT: We are on version 10.00.HC5 Testing Jonathan Leffler's idea didn't work. EXECUTE FUNCTION curr_sess(2009,'SP') works fine. Then I did CREATE...
Hello everyone. So I'm trying to migrate a MySQL-based app over to MS Sql Server 2005 (not by choice, but that's life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently. group_concat, by the way, does this: given a tabl...
Let's say I have 'myStoredProcedure' that takes in an Id as a parameter, and returns a table of information. Is it possible to write a SQL statement similar to this? SELECT MyColumn FROM Table-ify('myStoredProcedure ' + @MyId) AS [MyTable] I get the feeling that it's not, but it would be very beneficial in a scenario I have wi...
How could a stored procdure run in 10 seconds via Management Studio, but take 15 minutes via a TableAdapter for the same inputs? It is repeatable, meaning I have run it at least three times in each environment, and Management Studio is consistently about 100 times faster. I'm using .net 2.0 and SQL Server 2000 In SQL Server Management...
I am using Asp.NET MVC as a basic webservice for several searchengine type asp/ jquery sites. Database searching is straightforward: Model - Sql Server FullText Sproc returning XML View - N/a Controller - Authorise user/ Parse input return Content (Model.XML) The returned XML contains four resultsets - item list, category breakdown,...
Is it possible to write a stored procedure with dynamic parameters in sql server 2005 ...
I have problem compilin this code..can anyone tell whats wrong with the syntax CREATE PROCEDURE spGenericInsert ( @insValueStr nvarchar(200) @tblName nvarchar(10) ) AS BEGIN DECLARE @insQueryStr nvarchar(400) DECLARE @insPrimaryKey nvarchar(10) DECLARE @rowCountVal integer DECLARE @prefix nvarchar(...
Can anyone tell how correct the following code below. Iam tryin to create a stored procedure that returns the rowcount of a table whose name is passed to it. CREATE PROCEDURE spROWCOUNTER ( @tablename nvarchar(20) @rowCountVal int OUTPUT ) AS DECLARE @strQuery nvarchar(300) SET @strQuery = 'SELECT @rowCountVal=COUNT(*) FROM '...
The following code generates the primaey key for the new record to be inserted and inserts the record into a table, whose name and the values to be inserted are given as parameters to the stored procedure. I am getting a runtime error. I am using Visual Studio 2005 to work with SQL Server 2005 Express Edition ALTER PROCEDURE spGenericIn...
I have a basic "property bag" table that stores attributes about my primary table "Card." So when I want to start doing some advanced searching for cards, I can do something like this: SELECT dbo.Card.Id, dbo.Card.Name FROM dbo.Card INNER JOIN dbo.CardProperty ON dbo.CardProperty.IdCrd = dbo.Card.Id WHERE dbo.CardProperty.Id...
What is a stored procedure? How do they work? What is the make-up of a stored procedure (things each must have to be a Stored Procedure)? ...
Is it possible to store the results of a call to exec sp_executesql in a 'table parameter'. The parameter value is used in another SQL Stored procedure (SQL 2000/2005) ...
Is the following stored procedure code robust for multi-user application. It is working fine. I was wondering if there is any better way to do it and if there is any performance issues. The proc has three sql statements together like 1.Updating hardware status in Allocation Table 2. Calculating Next appropriate Primary Key value for new...