|
Modest Bird wrote: I want to use stored procedure in place of select statement for cursor.
I don't think that you can do this. What you can do is create a temporary table in your current procedure and fill it from nested procedure. select from this temporary table in cursor
|
|
|
|
|
Hello Modest,
You can try to use User Definied Function for your cursor instead of stored procedure.
Like this:
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT * FROM dbo.fn_GetTestData(1)
Please get the sample from --> http://www.oin1.com/Technical/SQL/FunctionInCursor.htm[^]
Eliz.K
|
|
|
|
|
You can't. You have to provide a SELECT statement, and you cannot directly SELECT from a stored procedure's results.
You can replace the stored procedure with a user-defined function - either an inline table-valued function, which simply contains a single SELECT statement, or a multistatement table-valued function.
Alternatively, the INSERT statement can insert the results of a stored procedure into a table (e.g. INSERT table (columns) EXECUTE stored_proc). You would need to create a temporary table or table variable to store the results, with the appropriate schema, insert into the temporary table/table variable, then declare the cursor to select from that.
|
|
|
|
|
what string function or any expression that returns the first letter of each word in uppercase, all other letters in lowercase in mssql2000?
thank you in advance!!!
jyn
|
|
|
|
|
Not sure if there is any built-in function from MSSQL. But you can write your own function -->
select UPPER(SUBSTRING(colName, 1, 1)) + LOWER(SUBSTRING(colName, 2, LEN(colName))) from tblTable
Eliz.K
www.oin1.com
|
|
|
|
|
this does only first letter of of the text to uppercase.
not first letter of all words in the text
there is no in built function for this. need to write a udf for this.
Regards
KP
|
|
|
|
|
Oh ya... sorry for my mistake.
To look through every word will affect the performace. I have a way which is not very clever, but should be workable.
Write a user definied function, where you replace ' a' to ' A', ' b' to ' B'.. and so on.
Sorry...
Eliz.K
www.oin1.com
|
|
|
|
|
Hi guys
I am trying to execute a dts package which has a text file as it's source (comma delimited with .txt extension) and then loads it into a table in sql server 2000. When I execute it, it fails on the first datarow it reads on the second field, the first field's value it reads.
THis is the error message:
"The number of failing rows exceeds the maximum specified.
Readfile transform 'DTSTransformation_2', source column 'MSISDN' (DBTyPE_STR), destination column 'PhoneNo'
(DBTYPE_STR): File '0842473397' not found."
funny thing is that '0842473397' is not a file, it's the second value in the first data row in the text source file.
Baffled, Plz Help.
Kiefie
Jack of all trades, master of some.
|
|
|
|
|
Nevermind I got it, I should've used 'copy column' and not 'read file' transformation.
Kiefie
The man with a plan.
|
|
|
|
|
Hi,
Can any one tell me how to connect with SQl using OleDb or Odbc.
Suppose the database name is TestData.mdf. Now what would be ths exact connection string?.
|
|
|
|
|
|
Dang, you beat me to it 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
When I run the following query I get results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'religion');
..but when I run the following query, I get no results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'the');
I know for a fact there are titles with the word the in it.
Please help.
Regards
ma se
|
|
|
|
|
Try CONTAINS.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, 'the');
|
|
|
|
|
This doesn't work either.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, ' "the" OR "and" ');
What I am trying to achieve is what ever words are passed through, I need to search on it. So if I were to pass through the word "a" or "an" or "the" I need it to bring back data that contains these words.
Regards
ma se
|
|
|
|
|
hi
i Hope this mail will help u little bit.
FREETEXT:
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches
The following example searches for all documents containing the words related to vital, safety, components.
SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components' );
I think contions will give u r result
contains can use in this cases.
A word or phrase.
The prefix of a word or phrase.
A word near another word.
A word inflectionally generated from another (for example, the word "drive" is the inflectional stem of drives, drove, driving, and driven).
A word that has a higher designated weighting than another word.
Syntax
regards,
.Net professional
|
|
|
|
|
Thanks, but I have another scenario:
I am using SQL Server 2005, and I have set up a full text search on my products table.
I am using ASP.NET 1.1. In my search.aspx page I have 4 textboxes used in the search. But only one of the textboxes are required for the search. I'm not sure hot to construct my SELECT statement based on the given criteria because some of the textboxes can be blank.
I have a title textbox where the user can type a couple of words that he wants to find in the book's title. How do I specify this in the SELECT clause, as it will not be an exact match, but a combination of words in the title.
I would appreciated it if someone could get back to me.
Regards
ma se
|
|
|
|
|
???
SELECT Title
FROM Production.Document
WHERE Document LIKE '%vital safety components%'
|
|
|
|
|
Hi Guru's,
I made a basic search filtering query, it searches for the predefine (default) values when user does not place any values on search fields, my search query works fine but not until having the sort option, below is my query and my db structure:
TABLE:
ListingID int
HLCN varchar(6)
HousePrice money
Bathrooms float
Bedrooms int
HomeLocation varchar(50)
ContactName varchar(50)
EmailContact varchar(50)
PhoneContact varchar(50)
Address varchar(100)
SquareFeet varchar(50)
YearBuilt varchar(50)
LotSize varchar(50)
Garage varchar(50)
Notes varchar(255)
QUERY:
declare @sort nvarchar(50)
set @sort ='HomeLocation'
Select
listingID,
HLCN,
HousePrice,
Bedrooms,
Bathrooms,
HomeLocation,
ContactName
from
listings
where
houseprice between 0 and 100000000
and
bathrooms >= 0
and
bedrooms >= 0
and
homelocation like '%' + '' + '%'
order by
case @sort
when 'HomeLocation' then HomeLocation
when 'HousePrice' then HousePrice
end
when I sort by HousePrice it return correct resultm but when I sort by Homelocation it returned
Cannot convert a char value to money. The char value has incorrect syntax. I'm wondering whey It's causing char-to- money conversion error when I'm not passing char values and also this only happens when I'm using HomeLocation as sort.
Please advice,
Thanks
Dom
|
|
|
|
|
I believe it is because the HomeLocation is VARCHAR type while the HomePrice is MONEY type. I try the following and it works (LoginID is VARCHAR, UserID is INT):
DECLARE @sort NVARCHAR(50)
SET @sort ='LoginID'
SELECT * FROM tblUser
ORDER BY
CASE @sort
WHEN 'LoginID' THEN CONVERT(VARCHAR, LoginID)
WHEN 'UserID' THEN CONVERT(VARCHAR, UserID)
END
Eliz.K
www.oin1.com
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Hi,
I am using SQL Server 2005, and I have set up a full text search on my products table.
I am using ASP.NET 1.1. In my search.aspx page I have 4 textboxes used in the search. But only one of the textboxes are required for the search. I'm not sure hot to construct my SELECT statement based on the given criteria because some of the textboxes can be blank.
I have a title textbox where the user can type a couple of words that he wants to find in the book's title. How do I specify this in the SELECT clause, as it will not be an exact match, but a combination of words in the title.
I would appreciated it if someone could get back to me.
Regards
ma se
|
|
|
|
|
need suggestions to this problem
Data is like this(WHEN RETURNING FROM DIFFERENT TABLES)
column A column B
7001 NULL
7002A A
7002AB AB
7003VA VA
7003AV AV
7004ABC ABC
7005XYZ XYZ
NEED TO CONVERT TO LOOK LIKE THIS FOR FUTURE COMBINATIONS
column A column B
7001 NULL
7002A A
7002AB A
7002AB B
7003VA V
7003VA A
7003AV A
7003AV V
7004ABC A
7004ABC B
7004ABC C
ANY HELP WILL BE HELPFUL
|
|
|
|
|
Hello everyone,
I am working on an Windows Application which requires to access a remote database. I would like to be able to obtain the size of the tables before they are used to populate the DataTable in the Windows Application so, the data tranmission is displayed to the user.
I am using "mysql_fetch_array(SHOW TABLE STATUS FROM MyDatabase LIKE 'MyTableName')" command to get the Data_Length of the table. But I am unable to get this working. Can someone be kind enough to tell me how I can get the Table Size (Byte) from Windows Application (C#)?
Thank you very much and have a great day
Khoramdin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
