Want to enter negative values in input parameter in Db2 stored procedure
Want to enter negative values in input parameter in Db2 stored procedure, but it is not accepting negative values.. Please help.. ...
stored-procedures
how to Create simple stored procedure which returns resultset in mysql
Create simple stored procedure which returns resultset in mysql ...
How to return values from a dynamic SQL Stored Procedure to the Entity Framework?
I have a Stored Procedure which executes some dynamic SQL. I want to use this Stored Procedure in entity framework 4, but when I try to create a complex type the procedure returns no columns. Is there any way I can force it to return my values and get the entity framework to receive them? Here is a much-simplified example of what I want ...
sql simple sproc - filter
Hi, I have a table called tblProducts with 3 columns: intID, dateMain, intStatus. I have a stored procedure like so: ALTER PROCEDURE [dbo].[spList_Report] @id INT, @startDate DATETIME = NULL, @endDate DATETIME = NULL, @includeStatus1 BIT, @includeStatus2 BIT, @includeStatus3 BIT, @includeStatus4 BIT AS SET NOCOUNT ON ...
Progressive count using a query ?
I use this query to SELECT userId, submDate, COUNT(submId) AS nSubms FROM submissions GROUP BY userId, submDate ORDER BY userId, submDate obtain the total number of submissions per user per date. However I need to have the progressive count for every user so I can see how their submissions accumulate over time. Is this possible to i...
Stored Procedure with conditional results
I want to write a stored procedure that works something like this: SELECT * from T where T.A = @a and T.B = @b if that returns rows, return those rows, if not, return SELECT * from T where T.A = @a and T.B IS NULL Edit: It feels that there should be a way to create a procedure such that it runs the first query once and runs the se...
Nested Create Type in Stored Procedure Declaration T-SQL
I am writing a SQL Stored Proc which takes in a single table valued parameter. Is it possible for me to create the table type in the parameter definition, for example: CREATE PROCEDURE example ( @param (CREATE TYPE tableparameter ( column1 int, colunn2 varchar.... )) READONLY ) ...
How to fill a dataset from a stored procedure using MySql and Mono
I am trying to fill a dataset with the result of a stored procedure using MySql and Mono. The stored procedure takes in some values, creates a new row in a table and then selects some rows. When I try this I get back an empty dataset and my database does not have any new records. I have checked the basics: The Database is running and...
Getting metadata from another database in an sproc in SQL Server?
How do I create a stored procedure that exists in one database but runs the below code against another (any) database? SET @sql1 = N'INSERT INTO #Tables SELECT' + N' t.TABLE_NAME as TableName' + N',t.TABLE_SCHEMA as SchemaName' + N',(SELECT OBJECTPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + ''.'' + t.TABLE_NAME),''T...
How to send mail from Stored Procedure ?
Hi All, I need to send a mail from Stored Procedure. I knew it is possible. I studied it from this link . How do i authenticat in SMTP Server. Do you have any idea.? Thanks ...
Syntax error in ALTER PROCEDURE in a DB update script
I have a script to update my database for a new release of my web app . In this update i need to alter a strored procedure. I have an ALTER PROCEDURE script that works fine when run on its own, however when I drop it into my update script and run it I get the errors "Incorrect syntax near the keyword 'PROCEDURE'." and "Must declare the s...
Customise default 'New Stored Procedure' SSMS 2008 Template
I am trying to customise the default query which is put in place when you click New Stored Procedure... from the Object Explorer on SQL Server Management Studio 2008. I have found how to change the 'Create Stored Procedure (New Menu)' template from the Template Explorer, however this means I will have to keep opening the template explor...
Does this mySQL "spatial" query work in SQL Server 2008 as well?
Before I embark on a a pretty decent overhaul of my web app to use a spatial query, I'd like to know if this MySQL query works in SQL Server 2008: SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FR...
Call Oracle Stored Procedure Using createNativeQuery
I need to call a stored procedure using JPA and found this article: http://www.oracle.com/technology/pub/articles/vasiliev-jpql.html which explains how to use EntityManager.createNativeQuery. However, the example actually calls a function that has a return argument. I've tried searching for an example of calling a stored procedure th...
LINQ on a DataTable IN a CLR Stored Procedure.
Hi, does anybody know if it's possible (and how) to use LINQ on a DataTable inside a CLR Stored Procedure? I have no problems using LINQ on a DataTable in a standard WinForms proj however; I'm using a Database Project in VS2010 on SQL2005 and creating a Stored Procedure: (simplified listing) [Microsoft.SqlServer.Server.SqlProcedure] ...
jsf 2.0 spring stored procedure
Hello friends I am getting problem when injecting JSF bean into a bean that extends StoredProcedure abstract class... The Bean return null values... ...
How can we define output parameter size in stored procedure?
How can we define output parameter size in stored procedure? ...
check if record exists "update" if not "insert" stored procedure
hey guys I got this. I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table.. what am I doing wrong? It increases timeValid for +30 days but inserts another row too.. thank you.. Select @pre_var = count(*) From PREMIUM_...
Problems with Executing a DB2 External Stored Procedure
I am trying to call an external stored procedure (calls an RPG program). I keep getting the following error: "Exception Details: IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0104 Token @SSN was not valid. Valid tokens: :." Here is my code: using (iDB2Connection conn = new iDB2Connection(_CONNSTRING)) { conn.Open(); stri...
Is it possible to return a return value and a result set from a stored procedure using LINQ
Looking for suggestions on cleanest way to get the return value and the result set (without passing a referenced parameter to the stored proc). MY stored procs have return values to show errors, etc and they end with a select statement to get the information. With regular commands I would create an output parameter which would hold th...