Hey SO PHPmyAdmin keeps rejecting this mySQL im pretty sure its right and don't actually need it to run, its uni work and just have to hand in, it looks right to me. the actual error I get is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...
How do you guys maintain your stored procedures? I'd like to keep versions of them for a few different reasons. I also will be setting up cruisecontrol.net and nant this weekend to automate builds. I was thinking about coding something that would generate the create scripts for all tables/sprocs/udf/xml schemas in my development dat...
At work someone said "when we design and optimize the stored procedures, we have to take into account that fact that they will be running on large database servers". I'm confused by that statement in a number of respects: Does "large database server" imply a large volume of data and, if so, how does that impact the design of the store...
Hi all, I'm having some trouble with the following sproc Create PROCEDURE GetMatchingUsers @id int = NULL, @lastName varchar(50) = NULL, @firstName varchar(50) = NULL AS BEGIN SET NOCOUNT ON DECLARE @q nvarchar(4000), @paramlist nvarchar(4000) SELECT @q = 'SELECT Id , LastName , FirstName ' SELECT @q = @q + 'FROM User...
I have two procedures - two huge sets of selects with several sub-select and unions. I need to union results from these procedures and I still need them to exists separately. Something like that: if @Param = 1 Then PROCEDURE1 if @Param = 2 THEN PROCEDURE2 if @Param = 3 Then PROCEDURE1 union PROCEDURE2 I read that it's i...
I want to create a stored procedure to do some combined keyword search using CONTAINS,something like below: SELECT theContent FROM FtsTest WHERE CONTAINS (theContent, ' FORMSOF (INFLECTIONAL, keyword1) AND FORMSOF (INFLECTIONAL, keyword2)'); and he number of keywords may vary, so I tried to pass the whole 'FORMSOF... AND FORMSO...
Hi, Hi have a stored proc that always returns a single row depending of a parameter: IF @bleh = 1 SELECT TOP 1 Xyz FROM Abc ELSE SELECT TOP 1 Def FROM Abc I must use SqlMetal to generate the DataContext but this stored procedure returns a IMultipleResults, which is an error. Instead it should return a ISingleResult... If I remov...
Can anyone please explain what is the exact difference between stored procedures and user defined functions and in which context what is useful? ...
I was creating a temporary table in my procedure, but I always got an error "table already exists". Then I tried to create a random name to avoid collision but I don't know enough about how to execute SQL strings SET @tbName = CONCAT('temp', random_id); PREPARE stmt1 FROM 'CREATE TEMPORARY TABLE ? (`FIELDNAME` float NOT NULL);'; EXECU...
I'm making complete connection strings inside my procedure and would like to inject the name of the database server in them. Is there any way that I can detect the name from inside or am I doomed to passing it in? ...
Hi One Stored procedure returning multiple result sets and I need only the last result set, How do I achieve this without changing original procedure. am using the last reulst set in further processing in other Stored procedure. ...
back end java 1.4 with EJB 3.2 data base sql server 2000 How to make asynchoronous call to a procedure from java code using jdbc? ...
In the below procedure I want to set title_id into @v_title_id variable so that I can use it in the procedure. Any ideas thanx in advance. I'm using InnoDB engine type. DELIMITER // DROP PROCEDURE IF EXISTS sp_Title_SplitGenres // CREATE PROCEDURE sp_Title_SplitGenres ( p_genre_id INT, p_genre_str VARC...
I have an external stored procedure (SP) in AS400 with IN and OUT prameters.When my SP is called from webservice with IN and OUT parameters, it shows me an error (Attempted to read or write protected memory).But when the webservice has all the parm as IN mode and calls my SP it works properly. ...
hi..i wanted to know if there is a possibility to debug stored procedures in mysql..engine type = InnoDB ...
Hi Everyone, I've solved this issue but I'm just wondering why this works the way it does. I have a temporary table I am selecting from and am looking to display a a name, the number of records that match this name, and the percentage for that name of the total records. This is the way I originally had it: SELECT name, number, CASE...
Working solution: CREATE OR REPLACE FUNCTION my_search(tsq tsquery, translation_id integer, lang regconfig, count integer DEFAULT 10, skip integer DEFAULT 0) RETURNS TABLE(id int, chapter int, number int, headline text) AS $$ SELECT id, chapter, number, ts_headline($3, text, $1, 'StartSel = <em>, StopSel = </em>'::text) FROM ( ...
For the application I work on... we're creating a custom logging system. The user can view logs and apply "Tags" to them (Just like how you can apply tags to questions here!) In this example, I'm trying to get a list of all the Logs given a "Tag." I realize I can accomplish this by using joins... but this is also an exercise for me to l...
The password field in my user table (SQL Server 2008) is encrypted using HASHBYTES on insertion. I have a stored procedure with parameters for the username and plain-text password which does a SELECT using that username and the password sent through HASHBYTES, then returns the user record if it finds a match. The SP is always returning...
Is there a way for a SQL 2005 stored procedure to open a XML file in the file system (for exampe C:\Temp\Test.xml ) and edit a value within that file? I am working with a SQL 2005 database (containing metadata) that stores XML data separately in files (instead of using built-in SQL XML features). ...