Search Results

Search found 17501 results on 701 pages for 'stored functions'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 9/701 | < Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >

  • Perl DBI execute not maintaining MySQL stored procedure results

    - by David Dolphin
    I'm having a problem with executing a stored procedure from Perl (using the DBI Module). If I execute a simple SELECT * FROM table there are no problems. The SQL code is: DROP FUNCTION IF EXISTS update_current_stock_price; DELIMITER | CREATE FUNCTION update_current_stock_price (symbolIN VARCHAR(20), nameIN VARCHAR(150), currentPriceIN DECIMAL(10,2), currentPriceTimeIN DATETIME) RETURNS INT DETERMINISTIC BEGIN DECLARE outID INT; SELECT id INTO outID FROM mydb449.app_stocks WHERE symbol = symbolIN; IF outID 0 THEN UPDATE mydb449.app_stocks SET currentPrice = currentPriceIN, currentPriceTime = currentPriceTimeIN WHERE id = outID; ELSE INSERT INTO mydb449.app_stocks (symbol, name, currentPrice, currentPriceTime) VALUES (symbolIN, nameIN, currentPriceIN, currentPriceTimeIN); SELECT LAST_INSERT_ID() INTO outID; END IF; RETURN outID; END| DELIMITER ; The Perl code snip is: $sql = "select update_current_stock_price('$csv_result[0]', '$csv_result[1]', '$csv_result[2]', '$currentDateTime') as `id`;"; My::Extra::StandardLog("SQL being used: ".$sql); my $query_handle = $dbh-prepare($sql); $query_handle-execute(); $query_handle-bind_columns(\$returnID); $query_handle-fetch(); If I execute select update_current_stock_price('aapl', 'Apple Corp', '264.4', '2010-03-17 00:00:00') asid; using the mysql CLI client it executes the stored function correctly and returns an existing ID, or the new ID. However, the Perl will only return a new ID, (incrementing by 1 on each run). It also doesn't store the result in the database. It looks like it's executing a DELETE on the new id just after the update_current_stock_price function is run. Any help? Does Perl do anything funky to procedures I should know about? Before you ask, I don't have access to binary logging, sorry

    Read the article

  • Query gives an unsorted result set when run from stored procedure using CTE

    - by irtizaur
    I am trying to create a paging query using CTE. It works fine when I execute it from Microsoft SQL Server Management Studio Query Editor. And the result set is perfectly sorted as I want. But when I modify it for a stored procedure it gives me a unsorted result and I don't have any clue why. Here is my Query, with items as ( select ROW_NUMBER() over (order by create_time desc) number , i.item_name item_name , i.create_time create_time , c.category_name category_name , i.category_id category_id from cb_item i, cb_category c where i.category_id = c.category_id and c.category_id = '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA' ) select item_name , create_time , category_name , category_id from items where number between 1 and 25 And this is the Stored Procedure Version, create procedure ItemPage @category_id uniqueidentifier , @from int , @to int , @sortby nvarchar(50) as begin with items as ( select ROW_NUMBER() over (order by @sortby) number , i.item_name item_name , i.create_time create_time , c.category_name category_name , i.category_id category_id from cb_item i, cb_category c where i.category_id = c.category_id and c.category_id = @category_id ) select item_name , create_time , category_name , category_id from items where number between @from and @to end exec itempage '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA' , 1, 25, 'create_time desc' The first one gives me sorted result but procedure gives me unsorted result. I don't know why?

    Read the article

  • SQL Server: Output an XML field as tabular data using a stored procedure

    - by Pawan
    I am using a table with an XML data field to store the audit trails of all other tables in the database. That means the same XML field has various XML information. For example my table has two records with XML data like this: 1st record: <client> <name>xyz</name> <ssn>432-54-4231</ssn> </client> 2nd record: <emp> <name>abc</name> <sal>5000</sal> </emp> These are the two sample formats and just two records. The table actually has many more XML formats in the same field and many records in each format. Now my problem is that upon query I need these XML formats to be converted into tabular result sets. What are the options for me? It would be a regular task to query this table and generate reports from it. I want to create a stored procedure to which I can pass that I need to query "<emp>" or "<client>", then my stored procedure should return tabular data.

    Read the article

  • OdbcCommand on Stored Procedure - "Parameter not supplied" error on Output parameter

    - by Aaron
    I'm trying to execute a stored procedure (against SQL Server 2005 through the ODBC driver) and I recieve the following error: Procedure or Function 'GetNodeID' expects parameter '@ID', which was not supplied. @ID is the OUTPUT parameter for my procedure, there is an input @machine which is specified and is set to null in the stored procedure: ALTER PROCEDURE [dbo].[GetNodeID] @machine nvarchar(32) = null, @ID int OUTPUT AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT * FROM Nodes WHERE NodeName=@machine) BEGIN SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine) END ELSE BEGIN INSERT INTO Nodes (NodeName) VALUES (@machine) SELECT @ID = (SELECT NodeID FROM Nodes WHERE NodeName=@machine) END END The following is the code I'm using to set the parameters and call the procedure: OdbcCommand Cmd = new OdbcCommand("GetNodeID", _Connection); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.Add("@machine", OdbcType.NVarChar); Cmd.Parameters["@machine"].Value = Environment.MachineName.ToLower(); Cmd.Parameters.Add("@ID", OdbcType.Int); Cmd.Parameters["@ID"].Direction = ParameterDirection.Output; Cmd.ExecuteNonQuery(); _NodeID = (int)Cmd.Parameters["@Count"].Value; I've also tried using Cmd.ExecuteScalar with no success. If I break before I execute the command, I can see that @machine has a value. If I execute the procedure directly from Management Studio, it works correctly. Any thoughts? Thanks

    Read the article

  • Why does stored procedure invalidate SQL Cache Dependency?

    - by Fabio Milheiro
    After many hours, I finally realize that I am working correctly with the Cache object in my ASP.NET application but my stored procedures stops it from working correctly. This stored procedure works correctly: CREATE PROCEDURE [dbo].[ListLanguages] @Page INT = 1, @ItemsPerPage INT = 10, @OrderBy NVARCHAR (100) = 'ID', @OrderDirection NVARCHAR(4) = 'DESC' AS BEGIN SELECT ID, [Name], Flag, IsDefault FROM dbo.Languages END But this (the one I wanted) doesn't: CREATE PROCEDURE [dbo].[ListLanguages] @Page INT = 1, @ItemsPerPage INT = 10, @OrderBy NVARCHAR (100) = 'ID', @OrderDirection NVARCHAR(4) = 'DESC', @TotalRecords INT OUTPUT AS BEGIN SET @TotalRecords = 10 EXEC('SELECT ID, Name, Flag, IsDefault FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderDirection + ') as Row, ID, Name, Flag, IsDefault FROM dbo.Languages) results WHERE Row BETWEEN ((' + @Page + '-1)*' + @ItemsPerPage + '+1) AND (' + @Page + '*' + @ItemsPerPage + ')') END I gave the @TotalRecords parameter the value 10 so you can be sure that the problem is not from the COUNT(*) function which I know is not supported well. Also, when I run it from SQL Server Management Studio, it does exactly what it should do. In the ASP.NET application the results are retrieved correctly, only the cache is somehow unable to work! Can you please help? Maybe a hint I believe that the reason why the dependency HasChanged property is related to the fact that the column Row generated from the ROW_NUMBER is only temporary and, therefore, the SQL SERVER is not able to to say whether the results are changed or not. That's why HasChanged is always set to true. Does anyone know how to paginate results from SQL SERVER without using COUNT or ROW_NUMBER functions?

    Read the article

  • how to use a parameterized function for the Default Binding of a Sql Server column

    - by Walt Gaber
    I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) at the time the new file is being recorded. I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding. I would like to avoid using a trigger. I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure. How can I call this function with values from the row that is being inserted? USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FileCatalog]( [id] [uniqueidentifier] NOT NULL, [catalog_timestamp] [datetime] NOT NULL, [primary_duplicate] nchar NOT NULL, [name] nvarchar NULL, [length] [bigint] NULL, [timestamp] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_id] DEFAULT (newid()) FOR [id] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_catalog_timestamp] DEFAULT (getdate()) FOR [catalog_timestamp] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_primary_duplicate] DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate] GO USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetPrimaryDuplicate] ( @name nvarchar(255), @length bigint, @timestamp datetime ) RETURNS nchar(1) AS BEGIN DECLARE @c int SELECT @c = COUNT(*) FROM FileCatalog WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P' IF @c > 0 RETURN 'D' -- Duplicate RETURN 'P' -- Primary END GO

    Read the article

  • Sending one record from cursor to another function Postgres

    - by PylonsN00b
    FYI: I am completely new to using cursors... So I have one function that is a cursor: CREATE FUNCTION get_all_product_promos(refcursor, cursor_object_id integer) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT * FROM promos prom1 JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id) WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date AND promo_objects.object_id = cursor_object_id UNION SELECT prom2.promo_id FROM promos prom2 JOIN promo_buy_objects ON (prom2.promo_id = promo_buy_objects.promo_id) LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0) AND prom2.active = true AND now() BETWEEN prom2.start_date AND prom2.end_date AND promo_buy_objects.object_id = cursor_object_id; RETURN $1; END; ' LANGUAGE plpgsql; SO then in another function I call it and need to process it: ... --Get the promotions from the cursor SELECT get_all_product_promos('promo_cursor', this_object_id) updated := FALSE; IF FOUND THEN --Then loop through your results LOOP FETCH promo_cursor into this_promotion --Preform comparison logic -this is necessary as this logic is used in other contexts from other functions SELECT * INTO best_promo_results FROM get_best_product_promos(this_promotion, this_object_id, get_free_promotion, get_free_promotion_value, current_promotion_value, current_promotion); ... SO the idea here is to select from the cursor, loop using fetch (next is assumed correct?) and put the record fetched into this_promotion. Then send the record in this_promotion to another function. I can't figure out what to declare the type of this_promotion in get_best_product_promos. Here is what I have: CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion record, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer) RETURNS... It tells me: ERROR: plpgsql functions cannot take type record OK first I tried: CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion get_all_product_promos, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer) RETURNS... Because I saw some syntax in the Postgres docs showed a function being created w/ a input parameter that had a type 'tablename' this works, but it has to be a tablename not a function :( I know I am so close, I was told to use cursors to pass records around. So I studied up. Please help.

    Read the article

  • How to call stored procedure by hibernate?

    - by user367097
    Hi I have an oracle stored procedure GET_VENDOR_STATUS_COUNT(DOCUMENT_ID IN NUMBER , NOT_INVITED OUT NUMBER,INVITE_WITHDRAWN OUT NUMBER,... rest all parameters are OUT parameters. In hbm file I have written - <sql-query name="getVendorStatus" callable="true"> <return-scalar column="NOT_INVITED" type="string"/> <return-scalar column="INVITE_WITHDRAWN" type="string"/> <return-scalar column="INVITED" type="string"/> <return-scalar column="DISQUALIFIED" type="string"/> <return-scalar column="RESPONSE_AWAITED" type="string"/> <return-scalar column="RESPONSE_IN_PROGRESS" type="string"/> <return-scalar column="RESPONSE_RECEIVED" type="string"/> { call GET_VENDOR_STATUS_COUNT(:DOCUMENT_ID , :NOT_INVITED ,:INVITE_WITHDRAWN ,:INVITED ,:DISQUALIFIED ,:RESPONSE_AWAITED ,:RESPONSE_IN_PROGRESS ,:RESPONSE_RECEIVED ) } </sql-query> In java I have written - session.getNamedQuery("getVendorStatus").setParameter("DOCUMENT_ID", "DOCUMENT_ID").setParameter("NOT_INVITED", "NOT_INVITED") ... continue till all the parametes . I am getting the sql exception 18:29:33,056 WARN [JDBCExceptionReporter] SQL Error: 1006, SQLState: 72000 18:29:33,056 ERROR [JDBCExceptionReporter] ORA-01006: bind variable does not exist Please let me know what is the exact process of calling a stored procedure from hibernate. I do not want to use JDBC callable statement.

    Read the article

  • C#: ExecuteNonQuery() returns -1 when execute the stored procedure

    - by user1122359
    I'm trying to execute stored procedure in Visual Studio. Its given below. CREATE PROCEDURE [dbo].[addStudent] @stuName varchar(50), @address varchar(100), @tel varchar(15), @etel varchar(15), @nic varchar (10), @dob date AS BEGIN SET NOCOUNT ON; DECLARE @currentID INT DECLARE @existPerson INT SET @existPerson = (SELECT p_ID FROM Student WHERE s_NIC = @nic); IF @existPerson = null BEGIN INSERT INTO Person (p_Name, p_RegDate, p_Address, p_Tel, p_EmergeNo, p_Valid, p_Userlevel) VALUES (@stuName, GETDATE(), @address, @tel, @etel, 0, 'Student' ); SET @currentID = (SELECT MAX( p_ID) FROM Person); INSERT INTO Student (p_ID, s_Barcode, s_DOB, s_NIC) VALUES (@currentID , NULL, @dob, @nic); return 0; END ELSE return -1; END Im doing so by using this code below. SqlConnection con = new SqlConnection(); Connect conn = new Connect(); con = conn.getConnected(); con.Open(); cmd = new SqlCommand("addStudent", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@stuName", SqlDbType.VarChar).Value = nameTxt.Text.ToString(); cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = addressTxt.Text.ToString(); cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = telTxt.Text.ToString(); cmd.Parameters.Add("@etel", SqlDbType.VarChar).Value = emerTxt.Text.ToString(); cmd.Parameters.Add("@nic", SqlDbType.VarChar).Value = nicTxt.Text.ToString(); cmd.Parameters.Add("@dob", SqlDbType.DateTime).Value = dobTime.Value.ToString("MM-dd-yyyy"); int n = cmd.ExecuteNonQuery(); MessageBox.Show(n.ToString()); But it returns me -1. I tried this stored procedure by entering the same values I captured from debugging. It was successful. What can be the possible error? Thanks a lot!

    Read the article

  • Using multiple aggregate functions in an (ANSI) SQL statement

    - by morpheous
    I have aggregate functions foo(), foobar(), fredstats(), barneystats() I want to create a domain specific query language (DSQL) above my DB, to facilitate using a domain language to query the DB. The 'language' comprises of algebraic expressions (or more specifically SQL like criteria) which I use to generate (ANSI) SQL statements which are sent to the db engine. The following lines are examples of what the language statements will look like, and hopefully, it will help further clarify the concept: **Example 1** DQL statement: foobar('yellow') between 1 and 3 and fredstats('weight') > 42 Translation: fetch all rows in an underlying table where computed values for aggregate function foobar() is between 1 and 3 AND computed value for AGG FUNC fredstats() is greater than 42 **Example 2** DQL statement: fredstats('weight') < barneystats('weight') AND foo('fighter') in (9,10,11) AND foobar('green') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 3** DQL statement: foobar('green') / foobar('red') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 4** DQL statement: foobar('green') - foobar('red') >= 42 Translation: Fetch all rows where the specified criteria matches Given the following information: The table upon which the queries above are being executed is called 'tbl' table 'tbl' has the following structure (id int, name varchar(32), weight float) The result set returns only the tbl.id, tbl.name and the names of the aggregate functions as columns in the result set - so for example the foobar() AGG FUNC column will be called foobar in the result set. So for example, the first DQL query will return a result set with the following columns: id, name, foobar, fredstats Given the above, my questions then are: What would be the underlying SQL required for Example1 ? What would be the underlying SQL required for Example3 ? Given an algebraic equation comprising of AGGREGATE functions, Is there a way of generalizing the algorithm needed to generate the required ANSI SQL statement(s)? I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.

    Read the article

  • Combining aggregate functions in an (ANSI) SQL statement

    - by morpheous
    I have aggregate functions foo(), foobar(), fredstats(), barneystats() I want to create a domain specific query language (DSQL) above my DB, to facilitate using using a domain language to query the DB. The 'language' comprises of boolean expressions (or more specifically SQL like criteria) which I then 'translate' back into pure (ANSI) SQL and send to the underlying Db. The following lines are examples of what the language statements will look like, and hopefully, it will help further clarify the concept: **Example 1** DQL statement: foobar('yellow') between 1 and 3 and fredstats('weight') > 42 Translation: fetch all rows in an underlying table where computed values for aggregate function foobar() is between 1 and 3 AND computed value for AGG FUNC fredstats() is greater than 42 **Example 2** DQL statement: fredstats('weight') < barneystats('weight') AND foo('fighter') in (9,10,11) AND foobar('green') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 3** DQL statement: foobar('green') / foobar('red') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 4** DQL statement: foobar('green') - foobar('red') >= 42 Translation: Fetch all rows where the specified criteria matches Given the following information: The table upon which the queries above are being executed is called 'tbl' table 'tbl' has the following structure (id int, name varchar(32), weight float) The result set returns only the tbl.id, tbl.name and the names of the aggregate functions as columns in the result set - so for example the foobar() AGG FUNC column will be called foobar in the result set. So for example, the first DQL query will return a result set with the following columns: id, name, foobar, fredstats Given the above, my questions then are: What would be the underlying SQL required for Example1 ? What would be the underlying SQL required for Example3 ? Given an algebraic equation comprising of AGGREGATE functions, Is there a way of generalizing the algorithm needed to generate the required ANSI SQL statement(s)? I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.

    Read the article

  • SSRS Report from Oracle DB - Use stored procedure

    - by Emtucifor
    I am developing a report in Sql Server Reporting Services 2005, connecting to an Oracle 11g database. As you post replies perhaps it will help to know that I'm skilled in MSSQL Server and inexperienced in Oracle. I have multiple nested subreports and need to use summary data in outer reports and the same data but in detail in the inner reports. In order to spare the DB server from multiple executions, I thought to populate some temp tables at the beginning and then query just them the multiple times in the report and the subreports. In SSRS, Datasets are evidently executed in the order they appear in the RDL file. And you can have a dataset that doesn't return a rowset. So I created a stored procedure to populate my four temp tables and made this the first Dataset in my report. This SP works when I run it from SQLDeveloper and I can query the data from the temp tables. However, this didn't appear to work out because SSRS was apparently not reusing the same session, so even though the global temporary tables were created with ON COMMIT PRESERVE ROWS my Datasets were empty. I switched to using "real" tables and am now passing in an additional parameter, a GUID in string form, uniquely generated on each new execution, that is part of the primary key of each table, so I can get back just the rows for this execution. Running this from Sql Developer works fine, example: DECLARE ActivityCode varchar2(15) := '1208-0916 '; ExecutionID varchar2(32) := SYS_GUID(); BEGIN CIPProjectBudget (ActivityCode, ExecutionID); END; Never mind that in this example I don't know the GUID, this simply proves it works because rows are inserted to my four tables. But in the SSRS report, I'm still getting no rows in my Datasets and SQL Developer confirms no rows are being inserted. So I'm thinking along the lines of: Oracle uses implicit transactions and my changes aren't getting committed? Even though I can prove that the non-rowset returning SP is executing (because if I leave out the parameter mapping it complains at report rendering time about not having enough parameters) perhaps it's not really executing. Somehow. Wrong execution order isn't the problem or rows would appear in the tables, and they aren't. I'm interested in any ideas about how to accomplish this (especially the part about not running the main queries multiple times). I'll redesign my whole report. I'll stop using a stored procedure. Suggest anything you like! I just need help getting this working and I am stuck. If you want more details, in my SSRS report I have a List object (it's a container that repeats once for each row in a Dataset) that has some header values and then contains a subreport. Eventually, there will be four total reports: one main report, with three nested subreports. Each subreport will be in a List on the parent report.

    Read the article

  • SQL Stored Procedure: Business Hours

    - by Jacob
    How can I create a stored procedure that accepts a start and end date.(e.g April 1 - April 30 1.) Get the business days including Saturdays x (a value). Plus 2.) Get Holidays x (a value) and return the total. I'm new to this, I guess it would be a tsql function. hmm. any help would be appreciated. Thanks

    Read the article

  • Variable collation with MySQL stored procedure?

    - by Chad Johnson
    I want to do something like this in a stored procedure: IF case_sensitive = FALSE THEN SET search_collation = "utf8_unicode_ci"; ELSE SET search_collation = "utf8_bin"; END IF; INSERT INTO TABLE1 (field1, field2) SELECT * FROM TABLE 2 WHERE some_field LIKE '%rarf%' collate search_collation; However, when I do this, I get ERROR 1273 (HY000): Unknown collation: 'search_collation' How can I use a dynamic collation without having two copies of the same query?

    Read the article

  • ExecuteNonQuery on a stored proc causes it to be deleted

    - by FinancialRadDeveloper
    This is a strange one. I have a Dev SQL Server which has the stored proc on it, and the same stored proc when used with the same code on the UAT DB causes it to delete itself! Has anyone heard of this behaviour? SQL Code: -- Check if user is registered with the system IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_is_valid_user IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_is_valid_user >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_is_valid_user >>>' END go create procedure dbo.sp_is_valid_user @username as varchar(20), @isvalid as int OUTPUT AS BEGIN declare @tmpuser as varchar(20) select @tmpuser = username from CPUserData where username = @username if @tmpuser = @username BEGIN select @isvalid = 1 END else BEGIN select @isvalid = 0 END END GO Usage example DECLARE @isvalid int exec dbo.sp_is_valid_user 'username', @isvalid OUTPUT SELECT valid = @isvalid The usage example work all day... when I access it via C# it deletes itself in the UAT SQL DB but not the Dev one!! C# Code: public bool IsValidUser(string sUsername, ref string sErrMsg) { string sDBConn = ConfigurationSettings.AppSettings["StoredProcDBConnection"]; SqlCommand sqlcmd = new SqlCommand(); SqlDataAdapter sqlAdapter = new SqlDataAdapter(); try { SqlConnection conn = new SqlConnection(sDBConn); sqlcmd.Connection = conn; conn.Open(); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.CommandText = "sp_is_valid_user"; // params to pass in sqlcmd.Parameters.AddWithValue("@username", sUsername); // param for checking success passed back out sqlcmd.Parameters.Add("@isvalid", SqlDbType.Int); sqlcmd.Parameters["@isvalid"].Direction = ParameterDirection.Output; sqlcmd.ExecuteNonQuery(); int nIsValid = (int)sqlcmd.Parameters["@isvalid"].Value; if (nIsValid == 1) { conn.Close(); sErrMsg = "User Valid"; return true; } else { conn.Close(); sErrMsg = "Username : " + sUsername + " not found."; return false; } } catch (Exception e) { sErrMsg = "Error :" + e.Source + " msg: " + e.Message; return false; } }

    Read the article

  • Stored Procedure Hit Counter

    - by Tyler
    I have a table with 3 column's in a table on a MS SQL 2008 Database ID ToolID Count Can someone toss me a script that will create a stored procedure that accepts the param ToolID and increases its value by 1? All of my efforts have failed.

    Read the article

  • Use a certain select statement in a stored procedure depending on the Exec statement

    - by MyHeadHurts
    Alright so i am not even sure if this is possible I have a q_00 and q_01 and q_02 which are all in my stored procedure. then on the bottom i have 3 select statements that select a certain catagory for example Sales,Net Sales and INS sales What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures ALTER PROCEDURE [dbo].[casof] @YearToGet int as ; with q_00 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as asofSales , sum(PARTY) as asofPAX , sum(NetAmount) as asofNetSales , sum(InsAmount) as asofInsSales , sum(CancelRevenue) as asofCXSales , sum(OtherAmount) as asofOtherSales , sum(CXVALUE) as asofCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = @YearToGet group by DIVISION, SDESCR, DYYYY ), q_01 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as YESales , sum(PARTY) as YEPAX , sum(NetAmount) as YENetSales , sum(InsAmount) as YEInsSales , sum(CancelRevenue) as YECXSales , sum(OtherAmount) as YEOtherSales , sum(CXVALUE) as YECXValue from dbo.B101BookingsDetails where DYYYY=@YearToGet group by DIVISION, SDESCR, DYYYY ), q_02 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as CurrentSales , sum(PARTY) as CurrentPAX , sum(NetAmount) as CurrentNetSales , sum(InsAmount) as CurrentInsSales , sum(CancelRevenue) as CurrentCXSales , sum(OtherAmount) as CurrentOtherSales , sum(CXVALUE) as CurrentCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = (year( getdate() )) group by DIVISION, SDESCR, DYYYY ) select a.DIVISION , a.SDESCR , a.DYYYY , asofSales , asofPAX , YESales , YEPAX , CurrentSales , CurrentPAX , asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales , asofpax/yepax as percentpax ,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofNetSales , YEPAX , YENetSales , CurrentPAX , CurrentNetSales , asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales , asofpax/yepax as percentpax ,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofInsSales , YEPAX , YEInsSales , CurrentPAX , CurrentInsSales , asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales , asofpax/yepax as percentpax ,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ;

    Read the article

  • Does this mySQL Stored Procedure Work?

    - by Laxmidi
    Hi, I got the following stored procedure from http://dev.mysql.com/doc/refman/5.1/en/functions-that-test-spatial-relationships-between-geometries.html Does this work? CREATE FUNCTION myWithin(p POINT, poly POLYGON) RETURNS INT(1) DETERMINISTIC BEGIN DECLARE n INT DEFAULT 0; DECLARE pX DECIMAL(9,6); DECLARE pY DECIMAL(9,6); DECLARE ls LINESTRING; DECLARE poly1 POINT; DECLARE poly1X DECIMAL(9,6); DECLARE poly1Y DECIMAL(9,6); DECLARE poly2 POINT; DECLARE poly2X DECIMAL(9,6); DECLARE poly2Y DECIMAL(9,6); DECLARE i INT DEFAULT 0; DECLARE result INT(1) DEFAULT 0; SET pX = X(p); SET pY = Y(p); SET ls = ExteriorRing(poly); SET poly2 = EndPoint(ls); SET poly2X = X(poly2); SET poly2Y = Y(poly2); SET n = NumPoints(ls); WHILE i<n DO SET poly1 = PointN(ls, (i+1)); SET poly1X = X(poly1); SET poly1Y = Y(poly1); IF ( ( ( ( poly1X <= pX ) && ( pX < poly2X ) ) || ( ( poly2X <= pX ) && ( pX < poly1X ) ) ) && ( pY > ( poly2Y - poly1Y ) * ( pX - poly1X ) / ( poly2X - poly1X ) + poly1Y ) ) THEN SET result = !result; END IF; SET poly2X = poly1X; SET poly2Y = poly1Y; SET i = i + 1; END WHILE; RETURN result; End; Usage: SET @point = PointFromText('POINT(5 5)') ; SET @polygon = PolyFromText('POLYGON((0 0,10 0,10 10,0 10))') ; SELECT myWithin(@point, @polygon) AS result ; I'm using phpMyAdmin and it blows up when using stored procedures. If this one works, then I'll try to figure out how to call it in php instead. Thanks, Laxmidi

    Read the article

  • Why wont my if statement work, in my stored procedure

    - by MyHeadHurts
    Alright so i am not even sure if this is possible I have a q_00 and q_01 and q_02 which are all in my stored procedure. then on the bottom i have 3 select statements that select a certain catagory for example Sales,Net Sales and INS sales What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures ALTER PROCEDURE [dbo].[casof] @YearToGet int, @mode VARCHAR(20) as ; with q_00 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as asofSales , sum(PARTY) as asofPAX , sum(NetAmount) as asofNetSales , sum(InsAmount) as asofInsSales , sum(CancelRevenue) as asofCXSales , sum(OtherAmount) as asofOtherSales , sum(CXVALUE) as asofCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = @YearToGet group by DIVISION, SDESCR, DYYYY ), q_01 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as YESales , sum(PARTY) as YEPAX , sum(NetAmount) as YENetSales , sum(InsAmount) as YEInsSales , sum(CancelRevenue) as YECXSales , sum(OtherAmount) as YEOtherSales , sum(CXVALUE) as YECXValue from dbo.B101BookingsDetails where DYYYY=@YearToGet group by DIVISION, SDESCR, DYYYY ), q_02 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as CurrentSales , sum(PARTY) as CurrentPAX , sum(NetAmount) as CurrentNetSales , sum(InsAmount) as CurrentInsSales , sum(CancelRevenue) as CurrentCXSales , sum(OtherAmount) as CurrentOtherSales , sum(CXVALUE) as CurrentCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = (year( getdate() )) group by DIVISION, SDESCR, DYYYY ) IF @mode = 'sales' select a.DIVISION , a.SDESCR , a.DYYYY , asofSales , asofPAX , YESales , YEPAX , CurrentSales , CurrentPAX , asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales , asofpax/yepax as percentpax ,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; else if @mode= 'netsales' select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofNetSales , YEPAX , YENetSales , CurrentPAX , CurrentNetSales , asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales , asofpax/yepax as percentpax ,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; ELSE IF @mode = 'inssales' select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofInsSales , YEPAX , YEInsSales , CurrentPAX , CurrentInsSales , asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales , asofpax/yepax as percentpax ,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ;

    Read the article

  • Returning date from Stored procedure in ASP.Net/VB.Net

    - by Mo
    Hi, I want to execute a method on VB.Net to return a date which is in the stored procedure. I tried using ExecuteScalar but it doesnt work it retruns error 'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query' Any help would be much appreciated please? thank you below is the code Public Function GetHolidaydate(ByVal struserID as String) As DateTime Dim objArgs1 As New clsSQLStoredProcedureParams objArgs1.Add("@userID", Me.Tag) objArgs1.Add("@Date", 0, 0, ParameterDirection.Output) Return (CDate(ExecuteScalar(clsLibrary.MyStoredProcedure.GetHolidayDate, objArgs1))) End Function

    Read the article

  • Stored procedure execution problem

    - by geeta
    Iam implementing entity spaces in C# application and was able to execute queries such as the below one successfully. coll.query.where(coll.prodlineid.equal("id") if( coll.query.load()) However I need to replace all these queries in the code with Stored procedures. For this I used: coll.Load(esQuerytype.storedprocedure, "testproc", param) At this point, Iam getting error as 'EntitySpaces.Core.esEntityCollection.Load(EntitySpaces.DynamicQuery.esQueryType, string, params object[])' is inaccessible due to its protection level esEntityCollection is a metadata file, so I could not change the access modifier there from protected to public. Help:-)

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >