I've done this before but can't find where :/ I want to create a variable inside a stored proc and return its value which will be set depending on whether or not other selects return results. basically something like this: @myVar int = 0 BEGIN IF SELECT SomeThing FROM SomeTable @myVar = 1 ELSE IF SELECT SomeOther From SomeO...
Is there a way to determine programmatically if a SQL Server stored procedure parameter has a default? (Bonus points if you can determine what the default is.) SqlCommandBuilder.DeriveParameters() doesn't even try. Thanks in advance for your help! EDIT: I honestly don't care if it's a SQL Query, an SMO object, etc. ...
I need to use a variable to indicate what database to query in the declaration of a cursor. Here is a short snippet of the code : CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT) cdr_records:BEGIN DECLARE cdr_record_cursor CURSOR FOR SELECT cdrs_id, called, calling FROM dbName.cdrs WHERE lrn_...
Is there an easier way of cleaning up a database that has a ton of stored procedures that I'm sure there are some that aren't used anymore other than one by one search. I'd like to search my visual studio solution for stored procedures and see which ones from the database aren't used any longer. ...
I am calling an 2005 MS SQL Server stored procedure using the MS JDBC Driver and want to access the output parameters before processing the result set as follows: proc = "{call mySproc(?,?,?)}"; conn = ds.getConnection(); callableStmt = conn.prepareCall(proc); callableStmt.setString(1,inputParam); ...
I have a stored procedure that has multiple result sets. How do I advance to the 2nd result set in mysqli to get those results? Let's say it's a stored proc like: create procedure multiples( param1 INT, param2 INT ) BEGIN SELECT * FROM table1 WHERE id = param1; SELECT * FROM table2 WHERE id = param2; END $$ The PHP is something li...
I'm using a stored procedure to handle search on my site, it includes full text searching, relevance and paging. I also wanted it to return the total number of results that would have been returned, had paging not being there. So I've now got my SP returning 2 select statements, the search and just SELECT @totalResults. Is there any way...
This is the script that is taking a very long time USE [r_prod] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Drew Borden -- Create date: 4/16/2009 -- Description: Procedure to populated subdivision extract table -- ============================================= IF EXIST...
I've got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that's retrieved by a (rather large) series of queries...but I'm attempting to speed things up by using a recursive stored procedure. (As I understanding, using CT...
Hi, Can anyone please point out what im doing wrong with this Stored Procedure please. I cant get it to compile and my software isnt giving any useful clues as to what is wrong with it. CREATE PROCEDURE web.createSubscriptions ( @Member_Id BIGINT, @Trans_type VARCHAR(100), @Payment_Status VARCHAR(100), @Payment_Date DATE...
I want to be able to have a Stored Procedure that can only be used from a particular page, without having to create a permissions / role for a user, when it is just a single stored procedure I want accessed in this way. For example I want to have a proc_GetCustomerItems stored procedure which takes the Parameter of CustomerID and then...
How do you create a temporary table exactly like a current table in a stored procedure? ...
I have a stored proc .sql file on my system. I want to be able to move this file into database as a sp from C# code. i could open the file, read it in as a string, and execute it but I feel like there should be a better way. ...
I have a program (flex) that queries a database and gets back a dataset (value, timestamp). In the program I then put each value in the set through an algorithm to get a new value resulting in all the values being transformed. Instead of having to do this transformation of data in my program I would like mysql to do it and send the res...
One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using most of percentage. First is 68% of "Clustered Index Scan" of one main tables for reporting, This table has primary key of two columns and 200...
When you add pass a new job_type to sys.sp_cdc_add_job @job_type, (which is of type nvarchar(20)) You can pass the argument as either N'cleanup' cleanup Are there any reasons or benefits to use the former syntax using N' to pass the argument to stored procedures? ...
I am simply trying to call a store procedure (SQL Server 2008) using C# and passing XMLDocument to a store procedure parameter that takes a SqlDbType.Xml data type. I am getting error: Failed to convert parameter value from a XmlDocument to a String. Below is code sample. How do you pass an XML Document to a store procedure that is e...
I'm using the Publish to Provider option in my SQL Project in Visual Studio 2008. I only check the box to script stored procedures leaving all others unchecked, yet the script produced includes all objects. Why is this and how do I stop it? Cheers, Breandán ...
Hi All, I have an XML file and I open that in SQL Server using OPENXML and then read the values in the XML file and insert them into the table. Assume that the XML structure is like this "<Student><name>XYZ</name><id>123</id><fathersname>XYS</fathersname><fathersid>3489</fathersid></Student>". Now I need to add this as two different row...
Hi, I am using SQL 2000. I have a stored proc (spGetApplicantList) which cannot be modified. I need to get the unique LastNameInitials of all the records in that stored proc, so the list of applicants can be sorted alphabetically. Basically what I need is Select DISTINCT LEFT(LastName, 1) as [LastNameInitial] from spGetApplicantList Or...