I have the following coe to populate combobox with data from different tables and I want to know how to convert it into stored procedure?
private void populate_drop_down()
{
try
{
//ensure the box is cleared
cboIDType.Items.Clear();
cboNationality.Items.Clear();
cboCountry.Items.Clear();
cboGender.Items.Clear();
cboMaritalStatus.Items.Clear();
cboBloodGroup.Items.Clear();
cboProfession.Items.Clear();
cboCompany.Items.Clear();
cboBloodGroup.Items.Add("");
cboProfession.Items.Add("");
cboCompany.Items.Add("");
//Use the SQLServer provider..
//set up the connection and the command...
// sql_connection = new SqlConnection("server=JASSIM-PC\SQLEXPRESS;Trusted_Connection=yes;database=shefa;connection timeout=30");
string strSQL = "SELECT id_type FROM id_type SELECT country_name FROM iso_country SELECT gender_title FROM gender SELECT marital_status FROM marital_status SELECT blood_group FROM blood_group SELECT profession_title FROM profession SELECT account_name FROM accounts";
sql_command = new SqlCommand(strSQL, sql_connection);
//open the connection, and use the reader to populate the combobox
I would like to convert a couple informix stored procedures to SQLServer stored procedures. I have no idea how to accomplish this. Hereis an example of one of the procedures I need to convert.drop function mnaf_calc_calendar_quarter;CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)--************************************************** ***************************-- Name: mnaf_calc_calendar_quarter-- Description/Notes:-- Calculates the most recent calendar quarter based on the end date.---- Parms:-- End Date.-- Returns:-- The calculated period start date and end date.----************************************************** ***************************-- Revisions:-- PgmrDate # Description-- HPI05/03/2005--************************************************** ***************************RETURNING date, date;DEFINE dtStartDate date;DEFINE dtEndDate date;LET dtStartDate = mdy(12,31,1899);LET dtEndDate = pEndDate;-- If the end date parameter is equal to a calendar quarter,-- calculate the start date by subtracting three months.IF month(pEndDate) = 3 or month(pEndDate) = 6 ormonth(pEndDate) = 9 or month(pEndDate) = 12 thenLET dtEndDate = pEndDate;ELSE-- Otherwise find the closest previous calendar quarter end date-- then calculate the start date.IF month(pEndDate) = 1 or month(pEndDate) = 4 ormonth(pEndDate) = 7 or month(pEndDate) = 10 then-- Subtract 1 month off end date parameter to get the calendarqtr end dateLET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 unitsmonth);ELSE-- Month must be equal to 2, 5, 8, 11-- Subtract 2 months off end date parameter to get the calendarqtr end dateLET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 unitsmonth);END IF;END IF;-- Calcuate the start date by subtracting off two monthsLET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);RETURN dtStartDate, dtEndDate;END FUNCTION;grant execute on mnaf_calc_calendar_quarter to public;
I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL:
SELECT DISTINCTROW EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo, Accounts.* FROM Accounts INNER JOIN AccountCodes ON Accounts.AccountID = AccountCodes.AccountID WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24)) ORDER BY Accounts.LastName, Accounts.FirstName
My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this?
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :( Syntax error converting the varchar value 'ABC' to a column of data type int. SQL file ====================== DECLARE @S AS VARCHAR(1000) EXEC @S = PDT.DBO.SP_RPT 'ABC' SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S ====================== Stored Procedure ====================== CREATE procedure sp_RPT( @array varchar(1000) ) AS DECLARE @content AS VARCHAR(2000) SET @content = 'RPT: ' + @array + ' loaded successfully ' SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array) GO
Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht : ..... Do While NOT oInFile.AtEndOfStream oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop ..... After that, I will use a BULK INSERT to input data to ms sql. I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!! I guess it's because the file is not being converted into a correct format??
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
Simple example: declare @tTable(col1 int) insert into @tTable(col1) values (1) select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
Hi, I am having a SP which returns two Rowsets: create proc GetSalesDetails as select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006' select ProductName from Products where SalesDate > '10/10/2006'
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible? Marius G.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi guys I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order . The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that. Any Idea?
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
I've got a stored procedure which should update a table (add new customer record) When I run it locally everythings fine,
Since uploading it all to the web it no longer seems to add a new record, I've debugged it and it seems that the output parameters is set to nothing.
I believe it's a permissions issue but the user i'm using has full access to both the table and permission to execute the stored procedure is there any error handling I can do to capture the exact error? the code I use to execute the sProc is below
I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure. This is what I have so far, but it does not allow me to run it.
alter procedure dbo.createNewBUtable ( @BU as varchar(50) ) as set nocount on; create table @BU ( BUid varchar(50) primary key, BUinfo varchar(50) )
Hi, I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
I can't script the table because this process must be automatic no manual.
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
