|
Hi, not an expert SQL programmer so apologies if this seems like a novice question...
Is there a way to pass multiple parameters to a select statement in an SQL stored procedure? For example:
Say you wish to select all addresses in a certain county, where the counties table is foreign-keyed to the Address table on ID. So you would write a stored proc to accept a single county ID, and select on that basis.
But say you want to pass in a range of counties, and select all addresses with a county ID in that range? It's easy to do in inline SQL, using the IN operator, so I could build a SQL string in my data access class and pass that for direct execution to the DB engine, but I'd rather stay away from that approach for obvious reasons.
The other option is to not select on county, pass all results to my .NET class, and filter the data using my list of selection values in code before passing the resultset to the client.
I think I'd prefer the latter approach but if it were possible I'd like to do the whole lot in SQL to enhance performance. It's an MS SQL 2000 database.
Does anyone out there know of a way to get around this?
All the dude ever wanted... was his rug back.
|
|
|
|
|
Hi,
You can do some thing like this.
Create Procedure <procname>
@CountyID varchar(100)
Begin
Exec ('Select * from CountyTable where countyId in ('+@CountyID+')')
End
Where @CountyID contains the comma separated list of countyId's
Hope this helps you.
rAm
i Think, i Wait, i Fast -- Siddartha
|
|
|
|
|
Yes, it is possible.
There are 3 ways to do this as mentioned here :
Click Here[^]
Regards,
Arun Kumar.A
|
|
|
|
|
I have a Database MDF file created in MSSQL 2000
and would like to convert the database file to match the
MSSQL 2005 version our company recently obtained in an upgrade.
I can connect with MSSQL 2005 to my MSSQL 2000 datbase without any problems.
However I would like the database file structure to be that of the MSSQL 2005 as I believe that would be beneficial to performance and to make use of all other features of 2005.
No.1 Can I perform this conversion.
No.2 Can this be done with MSSQL 2005 or do I need an external tool.
No.3 If an external tool is needed which one should I get ?
I would be grateful for any help.
Thank You.
|
|
|
|
|
I normally back up the database in 2000 and restore it in 2005.
You can also just detach the database from 2000 and attach it in 2005.
|
|
|
|
|
Hi,
I have 10 000 products, I have a page parameter in my aspx page. Lets say it is page=1, then I want it to bring back records 1 through 50. Then if it is page=2, then records 51 through 100 should be returned.
Is this possible in a SQL statement in SQL Server 2000?
Regards
ma se
|
|
|
|
|
|
If there is an identity column in the table that increments by 1 each time you could use the SQL BETWEEN statement like so:
SELECT * FROM table_name WHERE identity_column BETWEEN (page_num * 50 - 49) AND (page_num * 50)
That's if there is an identity column, hope it helps.
Ma se wat? 
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
I have a problem substracting dates in Oracle. I tested the following:
Create a new table with a date field
Insert a register using sysdate
Then a select (sysdate - dateField) doesn't return 0 as expected. In the SQL Developer it returns an empty field.
How can I solve that?
Sometimes, maybe depending on the time difference, it works and returns a value (really, I need (sysdate - datefield) * 24*60*60
Regards,
Diego F.
|
|
|
|
|
To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following:
select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi",
trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-created "Tdy",
(sysdate-created)*24 "Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60 "Tsec"
from all_users
where rownum < 50
/
Regards,
Satips. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Could you try that basic sample so I see what is the result?
First create that table:
CREATE TABLE TB_TEST
(
ID NUMBER(2, 0) NOT NULL,
DATEFIELD DATE
, CONSTRAINT TB_TEST_PK PRIMARY KEY
(
ID
)
)
Now, I insert a register:
insert into tb_test values (1, sysdate)
commit;
And then, the select query:
select (sysdate-datefield) from tb_test where id=1
What is the result?
Regards,
Diego F.
|
|
|
|
|
I have 2 tables storing documents as IMAGE Type and version of document
what i want is when i open and save a document if the document is changed then i want to save the version in detail table. Is there any method to compare IMAGE data types in SQL SERVER..?
|
|
|
|
|
value() Method (xml Data Type)
Performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.
You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.
Regards,
Satips. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
static string conn="provider=microsoft.jet.oledb.4.0;data Source="+ Server.MapPath("/data/db1.mdb");
OleDbConnection cn=new OleDbConnection(conn);
the above statement giving the below error:
Error showing:- 'System.Web.UI.Page.Server' denotes a 'property' where a 'class' was expected
please suggest me how to change the code
www.mywebsite.com/data/db1.mdb
please write the connection string for the above website to locate db1.mdb
i will be thank full to you if you reply.
thank u
multimedia9
|
|
|
|
|
Look at the various connection strings at http://www.connectionstrings.com[^], and I'd bookmark the mentioned site for future reference 
"That's no moon, it's a space station." - Obi-wan Kenobi
|
|
|
|
|
Can someone help me understand the meaning of this query I found in a stored procedure in a SQL Server 2005 database?
SELECT @varMID = master_id
FROM tbl_link
WHERE email_address=(SELECT @@identity)
The part I don't understand is the WHERE clause:
1. What does it mean when an identifier is prefixed by TWO '@' chars instead of just one? ( There is no variable declaration for 'identity', BTW. )
2. Is it even a valid SQL statement that appears in the parenthesis, and if so, is it querying from tbl_link ?
Thank you very much for any help!
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
Hi
1. @@Identity is a valid sql statement - which returns the last-inserted identity value.
2. This is used after an insert statement if you insert a row to a table where a column is having an identity type.
For example:
Assume the table EmpDetails contains three columns EmpID, EmpName and EmpAge.
EmpID should be auto generated serial no.
So if you run the foll. statement:
INSERT INTO EmpDetails (EmpName,EmpAge)<br />
VALUES ('XYZ',55)<br />
<br />
SELECT @@IDENTITY AS 'Identity'
Displays identity value used in the new row.
Hope you got it.
Harini
|
|
|
|
|
Thanks, now it makes sense! ![Jig | [Dance]](https://www.codeproject.com/script/Forums/Images/jig.gif)
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
after insert new record , you can get the new unique ID by this.
|
|
|
|
|
Thanks!
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
I used to work in SQL Server 2000 and it's been a while. Now getting into a project with SQL Server 2005. I used to be able to create an auto-filling sequential record ID (effectively a long unique ID) assigned to every record so I could use that as a relational field among related tables. This seems to have been replaced by the uniqueidentifier type in SQL 2005? The problem is that a GUID is effectively a formatted string and a lot of characters, where the long is preferrable for me. How can I get a long int id that is sequential, and therefore unique for each record?
Thanks!
|
|
|
|
|
CREATE TABLE example
(
[ID] bigint IDENTITY (1, 1)
)Works in both SQL Server 2000 and 2005.
|
|
|
|
|
Thanks. I guess working in SQL Server Studio Express isn't the full product and isn't giving me all the options. I'll get the SQL Server access in the next few days and try it on there directly.
|
|
|
|
|
I am using VC++ 6 to access a database using ADO. I have created a record set which contains a variable of type DBTIMESTAMP. I have trouble when I try to update, or insert a record with a field which is a time/date stamp.
I know how to do this with an SQL statement using the Execute method of the connection. What I want to do is use the AddNew or Update method of the recordset to alter, or create a record.
It seems that all values specified must be represented as strings. The string that I use for the time/date stamp however must be the wrong format as I get the type mismatch error.
Any idea what could be wromg?
Mark Kunkel
|
|
|
|
|
You should be OK. I've had no problems using adDBTimeStamp in a recordset in VB.
Be aware that the SQL Server datatype timestamp does not correspond to a date and time. It is a synonym for rowversion, which is simply a unique identifier for the row information and is modified every time the row is updated. You cannot supply data for this column type.
The database type for a date and time is datetime, or if you don't need that wide a range or precision and want to pack a few more rows to a page (can help in reducing I/O when scanning a table), smalldatetime.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
