I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of ...
I have an insert SP which takes many parameters, 2 of them are @FirstName, @LastName. And have another update SP which takes manay parameters, 2 of them are @FirstName, @LastName. What i want to do is: from inside the insert SP "at its end", call the update SP and send to it the @FirstName, @LastName. I don't know the right syntax to d...
I have a bit of a conundrum I need to deal with, and I'm trying to find a good solution to it. I'm using MS SQL server 2005, and given a table where a uniqueidentifier column contains a data key to other information on the server. What I need to do is select every row in this datatable (sproc from ASP.NET), but separated into tables ba...
I am new to oracle. I need to process large amount of data in stored proc. I am considering using Temporary tables. I am using connection pooling and the application is multi-threaded. Is there a way to create temporary tables in a way that different table instances are created for every call to the stored procedure, so that data from ...
Hi! I would like to get the the number of arguments required for a stored procedure in MySql. Is that possible? My first thought was to try SELECT * FROM information_schema.ROUTINES; but therein lies no information about the number of arguments as far as I can see. Next try was SHOW PROCEDURE STATUS; but that just seems to return...
One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a function, but it's possible I'm thinking of another product from a prior decade... online searc...
Hi, I have updated the subsonic reference from 3.0.0.0 to 3.0.0.3, when I try to execute a stored procedure then I get a following error Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. I am calling the store procedure as follows db.GetAllLoanApplications(orderBy, ...
Hi friend I am using MySQL as my database and java as frontend. I am using stored procedure if I want to retrieve the data. There is no synchronization concept. If I want to get the same result from database i am calling the same stored procedure passed the same value. So I got the result. But If I do it multiple times in fraction of s...
I have a stored procedure for SQL Server 2000 that can only have a single instance being executed at any given moment. Is there any way to check and ensure that the procedure is not currently in execution? Ideally, I'd like the code to be self contained and efficient (fast). I also don't want to do something like creating a global temp...
The procedure will return from a table where name's width is set to 255. So is there any advantage in using @Name nvarchar(255) over @Name nvarchar(Max) ...
Hi, Im calling a SQL stored procedure from a piece of C#.net code: SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters); where the sqlParameters variable is defined as: SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS]; Log.Logger.Debug(string.For...
Hi guys, I noticed the following: An ASP.NET MVC website under development gets an SQL error "Unclosed quotation mark ..." when it makes a LINQ call to a stored procedure that contains dynamic SQL. For example: SP GetEmployees called with parameter [filter_name] that has value [n'for] throws this error I can fix the problem by doing...
Hello everyone, I am using SQL Server 2008. I have a table which has a datetime type column called CreateTime. I need to select all the records from this table whose CreateTime is more than 3 days and half an hour from current time (UTC time). I am using T-SQL store procedure. BTW: The CreateTime column is some time in the past time. ...
The following bits of code do not seem to be searching the entire routine definition. Code block 1: select top 50 * from information_schema.routines where routine_definition like '%09/01/2008%' and specific_Name like '%NET' Code Block 2: SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINI...
Hi, I have a mssql stored procedure question for you experts: I have two tables [table1] which retrieves new entries all the time. I then have another table [table2] which I need to copy the content of [table1] into. I need to check if some of the the rows already exists in [table2], if it do, just update the Update-timestamp of [table...
Are there any patterns for writing stored procs? like: Should we write 1 Sp for update, 1 for insert and 1 for select for each table. When is it good to create views. How to manage business rules in SP? How to reduce duplicate code etc etc.. Any good article or book to read about these patterns... thanks ...
Linq-To-Sql enables calling SPs. If this SP executes an update/delete/insert, do I need to SubmitChanges() after it? ...
Hey all. Is it possible to step through a sproc on sql server 2000 server using vs 2008? When I go through the server explorer and choose to step into the sproc, it asks me to enter the param values and then it gives me the output of the sproc, without stepping into it. ...
ok so this is what I am trying to achieve is to have two SELECT statements and then join results of each so I have something like this SELECT table.ID, tst.Value FROM blah AS table JOIN results AS tst ON tst.RUNID = table.RUNID WHERE table.RUNID IN ( ...// nothing important but in the end i get my tst.Value ) second statement is ...
I was wondering if anyone can give me a link on following a best practice pattern for inserting data in a SQL db using stored procedures. I mean, what procedures should we take to ensure validated data to be passed through stored procedures. ...