DROP PROCEDURE IF EXISTS HaveSomeFun; CREATE PROCEDURE HaveSomeFun(user_id CHAR(50),house_id CHAR(50),room_id CHAR(50),fun_text TEXT,video_url CHAR(100)) BEGIN DECLARE query_full TEXT; SET @fields_part = 'INSERT INTO fun(FunKey,UserKey,FunBody,LastModified'; SET @values_part = CONCAT(') VALUES( NewBinKey(), KeyToBin(\"', user_id, '\")...
I’m using Microsoft Chart extensions that ship with VS 2010. This suits my needs well, but I’ve run into an annoyance and am looking for a more elegant solution. When charting a line graph, to achieve a continuous line, I require data for all X coordinates. My dataset is for number of sales by employee by month, where sales count is o...
Hi I need some help in combining a stored procedure with another function. I've got a stored procedure that pulls latitudes and longitudes from a database. I've got another function that checks whether a point is inside a polygon. My goal is to combine the two functions, so that I can check whether the latitude and longitude points p...
Hello I have stored procedure returning XML. XML returned not as parameter but as result of SELECT: create procedure #xml_test as select 1 as a for xml raw go I'm trying to put this XML in a variable: declare @xml as nvarchar(max) But I can't find how to do it. My best idea was INSERT INTO ... EXEC, but I get error 'The FOR XML ...
Hi a simple question Does nHibernate support multiple record-sets returning from an SP, if so can someone show me some basic implementation? Thanks, Mark ...
Hi, i have a stored procedure which takes lot of time to execure .Can any one suggest a better approch so that the same result set is achived. ALTER PROCEDURE [dbo].[spFavoriteRecipesGET] @USERID INT, @PAGENUMBER INT, @PAGESIZE INT, @SORTDIRECTION VARCHAR(4), @SORTORDER VARCHAR(4),@FILTERBY INT AS BEGIN DECLARE @ROW_STAR...
I have two tables that need to be updated, Master and Identifiers: Master --MasterID (PK) --ModifiedDate --ModifiedBy Identifiers --IdentifierID --MasterID (FK to Master) --Identifier --IdentifierType --ModifiedDate --ModifiedBy The Master table's sole reason for existence is to tie different Identifiers to a single person. I receive...
I want to know why I can't set default value to SP datetime parameter to getdate() as below : Create PROCEDURE [dbo].[UPILog] ( @UserID bigint, @ActionID smallint, @Details nvarchar(MAX) = null, @Created datetime = getdate() ) if I try to save it will give me a compiler error Msg 102, Level 15, State 1, Procedure ...
Dear all, i try to do a little weighting of my data by using a stored procedure. Basically this means multiplying certain columns with their respective weights and adding them up in the end. I have written the following stored procedure: CREATE PROCEDURE test () BEGIN DECLARE w1 DOUBLE; DECLARE w2 DOUBLE; DECLARE res1 DOUBLE; DECLAR...
. Hi, We are building an ASP.NET application with C#.net language and Oracle 9i database. Here we installed Oracle 9i client software on our PCs. We never worked on Oracle 9i database. It’s the first time. We are planning to build stored procedures in Oracle database and call them from our ASP.NET application. Does working with Ora...
i got this error , When i execute this below sp snippet , DROP PROCEDURE get_DETAIL_STATE// CREATE PROCEDURE get_DETAIL_STATE(IN stateName VARCHAR(255)) BEGIN sELECT cFname,cLname FROM med_patient WHERE cState = stateName; END DELIMITER ; my updated snippet , DELIMITER ; CREATE PROCEDURE get_DETAIL_STATE(IN...
Hello, I was looking at the EntLib 5.0 video on their website and I saw a preview that uses like a different IDataReader to fill-in objects to work like a LINQ-to-Objects shortcut (instead of a LINQ-to-SQL). I think it uses sprocs class, but I couldn't find it on the library. Any idea where I can read more about it? Google isn't cooper...
Hey All, I am trying one stored procedure as follows. CREATE OR REPLACE FUNCTION "public"."get_fees" (VARCHAR(5000)) RETURNS text[] AS $body$ DECLARE student_ids ALIAS FOR $1; studetFee text[]; BEGIN FOR studetFee IN SELECT * FROM Student_fee WHERE student_id IN ( student_ids::VARCHAR(5000) ) LOOP **** some *** END LOOP; END; $b...
Hello All! I'm having an issue trying to set variable in SQL in a stored procedure. I'm checking to see if a record is set to active, and the idea is to set it to inactive or vice versa. Admittedly I'm not the best with SQL and have tried CASE statements along with my example, but nothings working... Could someone show me what I'm mis...
Hello all, I'm currently tasked with reading some data that stored in a flat file into my database and run reports against it. The one problem I'm running into is checking to see if a file actually exists. Is there a simple function to check if the file exists? Thanks! ...
Any thoughts/comments on a database with over 3,000 stored procedures querying/accessing over 1,400 tables (overly normalized). Really want to use technologies such as Entity Framework, preferrably 4.0, and get the business logic out of those sproc's? Appreciate any real-life practical experience & feedback if you decide to share. This...
Through Oracle queries is it possible to find out which views/synonyms/tables a stored procedure use? In PL/SQL Developer if you collapse out a stored procedure it will show "References" which shows all the tables/views/synonyms that the stored procedure uses. I am trying to implement this functionality into a script which will com...
The only way that I know of to make an async call using Linq to SQL is to compose an IQueryable and then ask the data context for the equivalent SqlCommand using GetCommand, which provides the BeginExecuteReader method. This approach didn't work for stored procedures and required some hackery to essentially use reflection to generate the...
Hello, I've a SP using a LIKE. Let's simplify it to: SELECT * FROM customers WHERE name LIKE '%' + @query + '%'. I'd like to use it for an (optional) exact match search without altering the SP but with a tricky parameter ;) Is there a way to "cancel" the 2 '%' with a clever @query? Thanks. ...
I would like to return some data as a Blob from a DB2 stored procedure written in Java. This is the code to generate the procedure on the DB2 server: CREATE PROCEDURE CLUB.P_CLUB_GET_BACKUP ( IN CLUBID INTEGER, IN BNR INTEGER, OUT BACKUP BLOB(50000000) ...