Hi all How to call a CLR Stored Procedure from codebehind of an aspx page? I am using SqlCommand with commandtext as CLR Stored Procedure name and SqlHelper.ExecuteDataSet() as below. string connectionString = ConfigurationManager.AppSettings["ConnectDB"]; SqlConnection sn = new SqlConnection(connectionString); SqlParameter[] sqlPar...
Where I have more than one table in my database for use with (similar, but) different products. Is it possible to select a table to work with based on a parameter (example below)? (This would save me having multiple similar copies of the same procedure). Cheers. DELIMITER $$ DROP PROCEDURE IF EXISTS `dostuff` $$ CREATE PROCEDURE `...
I've got a stored procedure in Oracle: procedure Test(results OUT gencursor, id in number) is v_cursor gencursor; begin OPEN v_cursor FOR select id, name, age from tblcustomers s where s.id = id; results:=v_cursor; end Test; Now, i'd like to execute this procedure using nHibernate ISession.CreateSQLQuery. All the examples ...
I have two tables coming which has data from two different systems. I need to reconcile the data in these two tables The column mapping needs to be made configurable. E.g.: Table A Table B Col1A, Col2A Col1B, Col2B MappingTable Col...
Hi there ! I'm trying to query a database view that's not located on the same server as the stored procedure I'm running. I heard about using "linked servers", but I have no access to the server's configuration at all ... Thanks in advance ! ...
Can a regular expression be used inside a stored procedure? If it can, how? Can you give some examples of how to do it? ...
this is my sp: CREATE DEFINER=`root`@`localhost` PROCEDURE `getproductname`(in productid int , out productname varchar(200)) BEGIN select product_name from product where product_id=productid ; END and this is my php code: $mysqli = new mysqli(DB_HOST,DB_USER,DB_PWD,DB_NAME); $mysqli->query("CALL getproductname(2049,@productn...
Good Afternoon All, I have two tables in my SQL Server 2005 DB, Main and MSDRGWEIGHTS. I want to create a stored procedure that updates Main.RelWeight with the appropriate value from MSDRGWEIGHTS. I have written the following code as part of the stored procedure: UPDATE MAIN left outer join MSDRGWEIGHTS AS W ON MAIN.MSDRG=W.MSDRG SE...
I've inherited a web application which strictly uses stored procedures to do its job. I like the approach of making it impossible for frontend developers to break the database, but I've been tired of writing calls to SPs with plain SQLs and wanted to have something saner. While I've been looking for a decent ORM (for Perl in this case, b...
I'm trying to create a stored procedure that uses SELECT TOP 20 * from tblRecords .... I want the number of rows returned to be sent to the procedure as a parameter. For some reason it says I have a syntax error near the parameter I use: SELECT TOP @PARAM from tblRecords .... Is there a straight way to do it or will I need to const...
How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp without using a Cursor? ...
This is an SQL Question, I think it is difficult one - I'm not sure it is possible to achieve in a simple SQL sentence or a stored procedure: I want to find the number of the longest sequence of the same (known) number in a column in a table: example: TABLE: DATE SALEDITEMS 1/1/09 4 1/2/09 3 1/3/09 3 1/4/09 ...
hi i try to understand procedure..... but still dont..... i need simple sample for procedure. i need procedure that i'll insert Fname and Lname, and i get table with the result search how i can do it ? i need package ????..... or cursor ????........ work on oracle 10g thank's in advance ...
I have made stored procedures in oracle. I'm calling it through my asp.net code. The procedure is : PROCEDURE prc_GetNewQuestionNo(iNextQuestionNo IN OUT NUMBER) IS iQuestionNo NUMBER ; BEGIN Select MAX(QUESTIONNO)+ 1 INTO iQuestionNo from tblIFFCOQUESTIONMASTER; iNextQuestionNo:=iQuestionNo; END prc_GetNewQues...
when the optional parameters are null or empty how can i ignore that condition? e.g. declare @color nvarchar(50) declare @time datetime set @color = null select * from apples where apple.color = @color and apple.time = @time if @color or @time is null , i would like to ignore that condition. ...
I want to update some data in a specified case, else these fields are not to be updated. What can i write code in a stored procedure for this? ...
We have lots of stored procedures which all contain a lot of comments. Is there a way to extract the comments from the stored procedures for deployment so the users can't see them when they modify a stored procedure with SQL Server Management Studio? Note: We're using SQL Server 2008. ...
I have a complex sql query that I have in a stored procedure and am calling from C#. The procedure requires a date-time which I pass in as DateTime object from c#, the problem seems to occur with the format of the date. If I change the parameter to string and pass it in as 'yyyy-MM-dd' it works fine. Is there anyway to use the datetim...
I use the following stored procedure from my SQL Server 2008 database to return a value to my C#-Program ALTER PROCEDURE [dbo].[getArticleBelongsToCatsCount] @id int AS BEGIN SET NOCOUNT ON; DECLARE @result int; set @result = (SELECT COUNT(*) FROM art_in_cat WHERE child_id = @id); return @result; END I use a SQLCommand-...
I have a store procedure in SQL Server which returns a value: CREATE PROCEDURE [dbo].[insertProc] @value1 INT, @value2 INT AS BEGIN SET NOCOUNT ON; INSERT INTO table1(value1,value2) VALUES (@value1,@value2) RETURN SCOPE_IDENTITY(); END I connect to the DB from ASP.NET using SQL Data Source, which is configured li...