|
Tamimi - Code wrote: then how you can pass the selected ids to a stored procedure ??
Presuming that you really did mean stored procedure then...
First step, determine based on to create the stored procedure in the language supported by the database. Options that I have used for variable argument lists.
- Arrays
- Varchar with values as a comma separated values in that list.
- Proc with up to X args and of which can be null.
- Several procs each one with an increasing number of parameters: first has 5, second has 10, etc.
- dynamic SQL, run in a proc (only suitable for situations where input is known to be secure.)
Once you do in fact have a proc then you write code which populates the parameters dependent on the type of proc that actually exists.
Conversely without a proc, and just using SQL, one creates the SQL from scratch using code (for loops, string concatenation, etc) with the appropriate number of bind variables. Then one populates the bind variables. Then you run it.
To my mind the last option is easier than any solution with procs.
|
|
|
|
|
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks.
Like
Declare a temporary table to store the Ids, like declare @Ids table(Id int)
Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc)
After that, you can then use the temporary table on your WHERE ... IN ... clause
Like
select * from tbl_Name where id in (select Id from @Ids)
|
|
|
|
|
thank you
this is nice. i will give it a try.
or simply 
string strIds = "1,2,3,4";
SqlCommand com="select * from tbl_name where id in(" + strIds + ")";
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Note: I'm not sure about MS-SQL, but in Oracle there is a limit to the number of values you can have in an "IN" clause. Back in Oracle 9, I believe the max was 1000.
for example: select * from myTable where ID IN (1,2,3,....,1000,1001) would fail because there was more than 1000 values listed.
I'm not sure if this is still the case, but you might want to consider it in your design.
Good luck. 
|
|
|
|
|
|
|
this is new to me 
thank you... will read about it
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
Hi,
I am pretty new to database section. I tried porting a table created in Oracle to SQL Server 2005. In Oracle, i could see a column defined holding the user details. The column is defines as given below.
CREATE_USER VARCHAR2(255 BYTE) DEFAULT USER NOT NULL
My doubt is regarding default value USER.
I would like to know
1) What will be the value keyword "USER" will be returning in ORACLE. Is it an in built property or function which returns the default user?
2) And the equivalent value to be used for "USER" in SQL Server 2005?
Please help
Thanks,
Arudya
modified on Wednesday, October 27, 2010 12:01 AM
|
|
|
|
|
The value returned for USER will be the userid that was supplied to establish the connection. If SQL*Plus is available, login to an oracle database and try the command select user from dual. It should return the same value as you supplied as the userid when you logged in. As for the equivalent in SQL Server, I'd suggest try googling for an equivalent.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi,
I have a form which including different sort of information. for instance, some information is regarding to personal data which related to Table1 and some are for Table2 and the some are for Table3. all this information are in 1 form.
Now, I just wondering to know, when I wanna insert data via Store procedure do I have to write SP for each of them?
is there any approach to insert data with writing just one SP.
how can I write SP, inserting data in Multi table. is it possible
|
|
|
|
|
If you insist on using stored procedures then there should be one that performs all the required statements in a transaction that can be rolled back.
Many practitioners seem to forget that by definition a procedure should contain more than one statement.
|
|
|
|
|
future3839 wrote: is there any approach to insert data with writing just one SP
This is a basic aspect of an SP
Receive values via variables in the procedure declaration
CREATE PROC StoreDumy
-- Declare
@Var1 VARCHAR(100),@Var2 VARCHAR(100),@Var3 VARCHAR(100),@Var4 VARCHAR(100),
@Var5 VARCHAR(100),@Var6 VARCHAR(100),@Var7 VARCHAR(100)
Insert the variables in the tables required. You may want to wrap the inserts in a transaction so that if 1 insert fails all inserts are rolled back and you are not left with orphaned data
As
DECLARE @ID int
begin TRAN
INSERT dbo.Country (Fld1, Fld2)
VALUES(@Var1, @Var2)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.State (CtryID, Fld1, Fld2)
VALUES(@ID, @Var3, @Var4)
SET @ID = SCOPE_IDENTITY()
INSERT dbo.Town (StateID, Fld1, Fld2)
VALUES(@ID, @Var5, @Var6)
COMMIT TRAN
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
anyone has the URL for the english official forum of PostgreSQL NOT the Postgre Advanced Server..
|
|
|
|
|
jrahma wrote: anyone has the URL
And you want to use a database product that you cannot even find the URL for. What level of support do you think you are going to get for this DB. Oh wait, you are looking here, a MS centric site, for support 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Oh wait, you are looking here, a MS centric site, for support
Thanks, good point, I could not fathom why the question got down voted. I guess I look at things too narrowly.
|
|
|
|
|
djj55 wrote: I could not fathom why the question got down voted
Some twat who probably has not heard of Postgre.
I nearly got involved in it in the 90s but chose Access spit instead and from there went naturally into SQL Server. I had just spent some years working with SuperBase only to have it sold 5 times in as many years and it got totally marginalised. I'll stick to mainstream!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I deploy PostgreSQL database in InstallShield 2010 so it will install the database server and create my database as well?
|
|
|
|
|
how can I create a script for the entire database inPostgreSQL including the structure, rows, functions, views, etc?
|
|
|
|
|
You could use PgAdminIII and it's database backup function. This allows you to select which objects you would like to re-create. This could then be run using restore from either PgAdmin or the command line, depending on your requirements
|
|
|
|
|
I have a demo application that uses SQL Server via ADO to create and populate a database. I connect to the master database and then execute a "CREATE DATABASE ..." SQL statement. Here is the connect:
bool CMsSqlDemoView::Connect(LPCTSTR szDatabase)
{
Disconnect();
m_DBConnection.CreateInstance(__uuidof(Connection));
if (!m_DBConnection)
{
m_listErrors.AddString("Failed to create ADO::Connection instance");
return(false);
}
m_DBConnection->CursorLocation = adUseClient;
// Note: The "sqloledb" provider does not fully support XML.
// For XML support, the Provider and DataTypeCompatibility values must be set as indicated.
CString strConnection;
strConnection.Format(
"Provider=SQLNCLI10;DataTypeCompatibility=80;Integrated Security=SSPI;Data Source=%s;Database=%s;Trusted_Connection=Yes;",
m_strDataSource.GetString(),
szDatabase);
HRESULT hr = E_FAIL;
bool bResult = true;
try
{
hr = m_DBConnection->Open(strConnection.GetString(), "", "", adConnectUnspecified);
}
catch(_com_error &e)
{
CString strText;
strText.Format("Error code '%d' : '%s'", e.Error(), (LPCTSTR)e.Description());
m_listErrors.AddString(strText);
bResult = false;
}
return(bResult);
}
So far so good. I have now created a second demo application that uses SQL Server CE 3.5. I set the provider as "Microsoft.SQLSERVER.CE.OLEDB.3.5" but I am unable to create a connection object, and I have no idea how to create a database since I cannot connect to a master database. Anyone know how to do this (C++)? Thanks. BY the way I don't want to use managed C++, or OLEDB.
modified on Tuesday, October 26, 2010 8:52 AM
|
|
|
|
|
In case anyone else needs the answer, I had to use the MS_ADOX::_Catalog COM interface which is imported from msadox.dll. For example:
MS_ADOX::_CatalogPtr catalogPtr;
catalogPtr.CreateInstance(__uuidof(MS_ADOX::Catalog));
catalogPtr->Create("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Temp\\sqlcedemo.sdf;");
The above crap non error checking code creates the C:\\Temp\\sqlcedemo.sdf database, which can then be opened and populated using ADO in the normal fashion.
|
|
|
|
|
is there an advantage to laying out fields of certain data types in a particular order?
For example, in MS Access 2007, is it best to have all Memo fields at the end of the table definition?
Thanks,
JJ
|
|
|
|
|
|
|
While there is no technical benifit I have always followed these rules
Primary keys is the first field (I almost never use complex keys) and is usually an identity or a GUID inder duress
Foreign key fields (you know all this ####ID fields)
Primary description field(s)
All other fields
Modified and ModifiedBy are generally the last 2 fields on most tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
