There are two major problems with your code DELIMITER is not a valid sql statement. It’s just a MySql client command. So just don’t use it. BTW the error you get tells you exactly that. You can’t use DB::statement to execute CREATE PROCEDURE code because it uses prepared statement source code for Connection. You can … Read more
First, SqlCommand has a ExecuteXmlReader method, not ExecuteXMlReader as you wrote (this is misspelling). Second, SqlCommand.ExecuteXmlReader method returns a value of type XmlReader, not a DataReader as is in your example. So changing your code to: using (XmlReader reader = cmd.ExecuteXmlReader()) { while(reader.Read()) { string s = reader.ReadOuterXml(); // do something with s } } … Read more
I think that this is related to parameter sniffing and the need to parameterize your input params to local params within the SP. Adding with recompile causes the execution plan to be recreated and eliminates much of the benefits of having a SP. We were using With Recompile on many reports in an attempt to … Read more
Our server calls stored procs from Java like so – works on both SQL Server 2000 & 2008: String SPsql = “EXEC <sp_name> ?,?”; // for stored proc taking 2 parameters Connection con = SmartPoolFactory.getConnection(); // java.sql.Connection PreparedStatement ps = con.prepareStatement(SPsql); ps.setEscapeProcessing(true); ps.setQueryTimeout(<timeout value>); ps.setString(1, <param1>); ps.setString(2, <param2>); ResultSet rs = ps.executeQuery();
Concepts such as “loops” (for-each, while, etc) and “branching” (if-else, call, etc) are procedural and do not exist in declarative languages like SQL. Usually one can express one’s desired result in a declarative way, which would be the correct way to solve this problem. For example, if the testProc procedure that is to be called … Read more
You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value: ORDER BY CASE WHEN @OrderDirection = 0 THEN 1 ELSE CASE WHEN @OrderByColumn = ‘AddedDate’ THEN CONVERT(varchar(50), AddedDate) WHEN @OrderByColumn = ‘Visible’ THEN CONVERT(varchar(2), Visible) … Read more
I sometimes use NULLIF like so… IF NULLIF(@PreviousStartDate, ”) IS NULL There’s probably no reason it’s better than the way suggested by @Oded and @bluefeet, just stylistic preference. @danihp’s method is really cool but my tired old brain wouldn’t go to COALESCE when I’m thinking is null or empty 🙂
Thanks @twoleggedhorse. Here is the solution. First we created a function CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER AS BEGIN DECLARE @id INTEGER set @id= (select TOP(1) id From tbl where col=@parm) RETURN @id END then we do the select query Select col1, col2, col3, GetAIntFromStoredProc(T.col1) As col4 From Tbl as T Where col2=@parm
If you’re likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement. Generally, there’s a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability … Read more
here is a portion of a procedure I use on my system to find text…. DECLARE @Search varchar(255) SET @Search=”[10.10.100.50]” SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like ‘%’+@Search+’%’ ORDER BY 2,1