Convert Table In Stored Procedure
Apr 23, 2004
I have a flat table from 1 of the clients with a lot of fields (more then 100) like this
Item F1 F2 F3 .... F(N)
---------------------------------------------
100 X X
101 X
There are more 10000 records , X is the data inside the field.
I need to quickly convert it to this table
Item FieldNumber Value
----------------------------
100 1 X
100 2 X
101 1 X
Any ideas ?
Thanks
Mikhail
View 3 Replies
ADVERTISEMENT
Oct 15, 2006
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
// sql_connection.Open();
sql_reader = sql_command.ExecuteReader();
if (sql_reader != null)
{
// while (sql_reader.Read())
// {
// cboIDType.Items.Add(sql_reader["id_type"]);
// }
while (sql_reader.Read())
{
cboIDType.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("id_type")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboNationality.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("country_name")));
cboCountry.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("country_name")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboGender.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("gender_title")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboMaritalStatus.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("marital_status")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboBloodGroup.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("blood_group")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboProfession.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("profession_title")));
}
sql_reader.NextResult();
while (sql_reader.Read())
{
cboCompany.Items.Add(sql_reader.GetString(sql_reader.GetOrdinal("account_name")));
}
}
//the finally clause will tidy up for us.
cboIDType.Text = "CPR";
cboNationality.Text = "BAHRAIN";
cboCountry.Text = "BAHRAIN";
cboGender.Text = "MALE";
cboMaritalStatus.Text = "SINGLE";
cboBloodGroup.SelectedIndex = 0;
cboProfession.Text = "";
cboCompany.Text = "";
}
catch (Exception oE)
{
MessageBox.Show("Problem Populating Reader Box: [" + oE.ToString() + "]");
}
finally
{
if (sql_reader != null) sql_reader.Close();
if (sql_connection != null)
{
if (sql_connection.State == ConnectionState.Open)
sql_connection.Close();
}
}
}
View 8 Replies
View Related
Jul 23, 2005
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;
View 1 Replies
View Related
Jun 6, 2008
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?
Jon
View 1 Replies
View Related
Jun 23, 2006
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
View 2 Replies
View Related
May 7, 2008
problem convet date
Code Snippet
DECLARE
@y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2)
DECLARE @mydate [datetime]
set @y_Date='2008'
set @M_Date ='8'
SET @d_Date='05'
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME)
select @mydate
2008-05-08 00:00:00.000
and it look ok
but whan i do this
Code SnippetEXECUTE [dbo].[testpro] @mydate
the date not pass properly !!
Code Snippet
DECLARE
DECLARE @mydate [datetime]
set @mydate = getdate()
2008-05-08 01:20:35.870
like this it work ok
what is the problem with tis line ?
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS
tnx
View 7 Replies
View Related
Jul 14, 2006
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??
View 1 Replies
View Related
Feb 23, 2015
I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.
I have tried to create function, but the error I am facing is select statement can not return data.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
[code]....
View 9 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Mar 27, 2006
Hello to all!
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...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Jul 19, 2012
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
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.
View 11 Replies
View Related
Apr 18, 2007
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
RETURN
END
View 2 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
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.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
Jan 31, 2006
I am working with the following two tables:
Category(NewID,OldID)
Link(CategoryID,BusinessID)
All fields are of Integer Type.
I need to write a stored procedure in sql 2000 which works as follows:
Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)
Then for each rows fetched from last query, execute a update query in the Link table.
For Example,
Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..
UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID
Please help me with the code.
Thanks,
anisysnet
View 1 Replies
View Related
Sep 8, 2006
Hello
Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?
insert #MyTempTable
exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3
JOIN dbo.OtherTable...
I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.
I just can't figure it out
Many Thanks!
Worf
View 2 Replies
View Related
Jul 23, 2005
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!
View 1 Replies
View Related
Apr 25, 2006
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.
View 1 Replies
View Related
Jan 29, 2013
I've never used a stored procedure before - let alone created one. how to append records from table A to table B.
View 1 Replies
View Related
Oct 11, 2007
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?
Cheers,
Soumen
View 3 Replies
View Related
Mar 9, 2004
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.
View 2 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
Aug 28, 2007
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?
View 8 Replies
View Related
Nov 12, 2007
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
View 2 Replies
View Related
May 17, 2008
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?
View 4 Replies
View Related
Jan 20, 2004
Hi,
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
thanks for any help
Dave
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
' Calculate the New CustNo using Output Param from SPROC
Dim custNo As Integer = CInt(parameterCustNo.Value)
Return custNo.ToString()
Catch
<---This is where it's dropping in can I put any
Error handling in to show me the error?
Return String.Empty
End Try
View 3 Replies
View Related
Feb 10, 2005
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)
)
View 4 Replies
View Related
Apr 25, 2006
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.
View 2 Replies
View Related
Feb 16, 2001
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.
Thank you
View 1 Replies
View Related
Nov 20, 2001
Hi All,
Can any one give me code sample which uses the recordset returned by the execution of a stored procedure as a derived table.
Thanks,
venkat.
View 1 Replies
View Related
Feb 16, 2001
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.
Thank you
View 1 Replies
View Related
