Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for


Hi everybody,

I was looking for an equivalent ORACLE INSTR Function in MSSQL but I
don´t found it and I don´t know if it exist so I must to write it and
this is the code. Maybe it will be helful to you:

/************************************************** *************************
Looks for a string inside another string and returns an integer
that correspond to the position of first ocurrence.
- strSource. Contains the string where the functions look for the
other string
- strToFind. Contains the string to look for inside strSource
- Integer value indicating the position of first occurrence of
strToFind in strSource
************************************************** *************************/

(@strSource varchar(400),
@strToFind varchar(200)) RETURNS int


@position int,
@maxPos int,
@longSubStr int,
@res int,
@strSub varchar(200)

SET @position = 0
SET @res = 0
SET @longSubStr = LEN(RTIRM(LTRIM(@strToFind)))
SET @maxPos = LEN(@strSource) - @longSubStr

WHILE (@position <= @strToFind)
SET @strSub = SUBSTRING(@strSource, @position, @longSubStr)

IF (@strToFind = @StrSub)
SET @res = @position - 1
SET @position = @position + 1




View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Instr Function
Hi,In SQL SERVER 2005 Database I have a field called MedNames with values such as "sodium % 34ml" "desx chloride 9 % 76ml"I need to return the words before and including % so "sodium % 34ml"  should return ""sodium % " Is it possible to do it in a select statement? I need to populate a dropdownlist with the shortened names.Thanks

View Replies !   View Related
Using Instr() Len() And Mid() Function In SQL Statement
I am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?


View Replies !   View Related
String Function - InStr
In VB there is InStr, in Excel you have SEARCH. what function can i use to search a string for a specific string. ie: how can i find where a space is in someones name?

our database has a table that is used for security. it contains a user code, user name and passcode. i need to split the username (currently forename and surname in same field) into two, around the space.

in VB i would write something like
strFName = left(<usernamefield>,InStr(1,<usernamefield>," ")-1)
to get the forename and similar to get the surname. how can i do this in SQL so that a view will supply the data apprpriately?

one of the reasons i want to do this is so that i can sort the users by surname! another is so that i can give options on their usercode - a combination of forename initial and 2 characters from surname.

any help welcomed.

View Replies !   View Related
I Am Looking For The Function That Is The Same As InStr In Access
Hello there...
I am looking for the function that is the same as InStr in Access for SQL server. I have a column that has format like this.. Lastname,Firstname Middlename...
This column doesn't separate each one of them. However I need to separate Lastname and Firstname and Middlename.. I was told that in Access there is function(InStr) that can find a position of comma and separate it as Lastname like that....
I was searching BOL but I couldn't find like this function in SQL Server..
So I need help:-))))
Because everybody has a different length of the lastname, I have a problem.
I can not use SUBSTRING or LEFT or RIGHT because of the varying position of comma ...

Thanks in advance

View Replies !   View Related
Instr (vbscript) Function Explanation ???
I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).

My problem is this:

I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName

I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?

my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)

So would then my code be like:

Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)

How do I get whatever is returned from the Instr function to a column in a

Any help would be great.


View Replies !   View Related
INSTR Function In TransactSQL Of SQLServer 7
My question is about the INSTR function of MSAccess
I want to know

Goodbye at every
I would you like to know if in TransactSQL of SQLServer7 exist a function that return the position of a string into an other string .
that is a Instr function MSAccess like


I'm sorry for my bad English
Thank you for everything


View Replies !   View Related
Instr Function For MSQL Query
Hi All

My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?

View Replies !   View Related
Aggregate Function With MSSQL 7.0
with sybase-sql it is possible to use a SELECT-statment like this:

(select list(string(column_name,
if "order"='A' then ' ASC' else ' DESC' endif))
where index_id=SYSINDEX.index_id
and SYSIXCOL.table_id=SYSINDEX.table_id)

the result will be like:
field1 asc, field2 asc, field3 desc.....

How can i manage this with MSSQL 70 ?

I havn't found any function working like this.

Any suggestions ?

thankx from Germany

View Replies !   View Related
Need Help With Raiserror Function In Mssql 2000
I have a stored procedure that raises an error just fine, but the problem is when i want to catch that particular error in client code, how do i go able doing so, i want to display a message in a label when that particular error occurs.

right now all i know about the raiserror function is RAISERROR("Message", Severity, Stage) how do i give it number so i can catch it in code?

ALTER Stored Procedure [dbo].[spName]


IF (@DummySetKey IS NULL)

RAISERROR('Error: This setkey number does not exist as a valid entry.', 11, 1);




client code:

Public Function ExecuteNonQuery(byval connstr as string) as boolean

dim conn as new SqlConnection(connstr)
Dim cmd as sqlCommand(spName, conn)
cmd.CommandType = CommandType.StoredProcedure



dim rowsAffected as Integer = cmd.ExecuteNonQuery()

If ex.ErrorCode = 5021 Then
' display in textbox
End If
end try

if rowsAffected > 0 Then
return true
return false
end if
End Function

View Replies !   View Related
MSSQL 2000: How Can We Track Table/sp/function Changes?
My company would like to start keeping track of everytime a table, stored procedure, or function is changed.

What is the best way to do this?

View Replies !   View Related
USE InStr() For ASP
val = "the snow (hh/ffff[dg]fd) "


v1 = inStr(1,val,"/") or v1 = inStr(1,val,"[") or  v1 = inStr(1,val,"(")


it have error :

 Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'inSrt'


v1 = inStr(1,val," ") is OK


How do it correct



View Replies !   View Related
Instr() Equivalent In SQL Server
I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.

View Replies !   View Related
Find? Instr? Indexof?
is there a sql keyword for find or instr?

i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colm

View Replies !   View Related
Using CURSOR For INSTR Purposes
TASK: At my work we want to categorize and summarize all our IIS web logs and make statistics from it and such. What I need to do is take the browser type from a certain column in the table. All the information is stored in 1 column, and I figure an instr function would be best to do this. I am new to SQL, so I was told to look up the cursor function. In summary, I want to take all the IIS data and match it up against a defined table and then have a sum function for each browser.

Here are some examples of what the column data looks like: (found in the [csMethod] column


I made a define table which lists an ID (primary key) and instr to search for as well as the full browser name. (define.browser)


1___Opera+7_______Opera 7
2___Opera/9_______Opera 9
4___Firefox/1.0___Mozilla Firefox 1.0
5___Firefox/1.5___Mozilla Firefox 1.5
6___Firefox/2.0___Mozilla Firefox 2.0
7___MSIE+5.5______Microsoft Internet Explorer 5.5
8___MSIE+5________Microsoft Internet Explorer 5
9___MSIE+6________Microsoft Internet Explorer 6
10___MSIE+7________Microsoft Internet Explorer 7
11_________________OTHER BROWSER

I am having problems getting a cursor to work. Are there any good tutorials out there, or can anyone be of assistance. Thank you in advance.

View Replies !   View Related
InStr To Remove : And , And Sum Data

Greetings All

I have some data-- specifically times for cell phone usage in the format of (7:00, 15:51, 1,200:45, etc).  I need to find a way to remove the ":" and the ","-- sum the data and then return it to its previous format of (7:00, 15:51, 1,200:45, etc).  Does anyone have some code they could post??

thanks as always


View Replies !   View Related
Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server
The syntax for Oracle's INSTR function is

instr (string1, string2, [start_position], [nth_appearance])

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

Do anybody know the solution for this ????

View Replies !   View Related
How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View Replies !   View Related
Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)

i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..

they have different table structures and names . . :D

i was thing of what language shall i use.. or what language is the best to work on this kind of project :)

hoping for your kind help guys. thanks :)


View Replies !   View Related
Server Configuration For MSSQL 2000 And MSSQL 2005
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View Replies !   View Related
Differenece Between Mssql Sp3 And Mssql Sp4 - Urgent Reply Is Required
is there any differernce between mssql sp3 connection establishment and mssql sp4 connection eslablishment while using java jdk1.4 to connect to...

i want to use driver for connection instead of sun.jdbc.odbc.JdbcOdbcDriver and DNS

View Replies !   View Related
Creating Index In MSSQL 2000 From MSSQL 2005
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.





Any help in creating such indexes in 2000 version is welcome.

View Replies !   View Related
Generating Script For MSSQL 2000 From MSSQl 2005?
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Sankar R

View Replies !   View Related
Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?

I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.

However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?


David Spaisman

View Replies !   View Related
MSSQL Express 2005 Vs. MSSQL 2000
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:

USE [master]
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
USE [master]
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
USE [master]
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
USE [SurveyData]
exec sp_adduser 'SSDBUSERNAME'
USE [SurveyManagement]
exec sp_adduser 'SSDBUSERNAME'

I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!


View Replies !   View Related
Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View Replies !   View Related
Deploying MSSQL 2005 Express DB To MSSQL 2005 WKGP Errors
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?

I have tried several approaches.

A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error. 

TITLE: Microsoft SQL Server Management Studio


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)



The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click:






B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:

TITLE: Microsoft SQL Server Management Studio


Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)

For help, click:



An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click:





 C: I have also tried opening the Database, and back up file through Server Management Studio. without success. 

 D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.

I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.

Mark Roach




View Replies !   View Related
Conversion Of MSSql 2000 Database To MSSQL 2005 Database
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?

View Replies !   View Related
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !   View Related
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies !   View Related
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,
I executed the following sql script successfuuly:

USE pubs


CREATE FUNCTION dbo.AuthorsForState(@cState char(2))



RETURN (SELECT * FROM Authors WHERE state = @cState)

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

USE pubs


SELECT * FROM shcInLineTableFN


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang

View Replies !   View Related
With Recompile: Mssql 6.5 Vs Mssql 7.0

We are now on mssql version 7.0, sp2. The application was upgraded from
mssql 6.5 to mssql 7.0 several weeks ago

Although we are not suppose to do so, two stored procedures were put into
production with the "WITH RECOMPILE' option under mssql 7.0, sp2.

During a nightly run yesterday, these 2 procedures ran extremely slow and
blocked others trying to run other stored procedures. We know why becuase
of the behaviour of the 'with recompile' option.

In researching this, we noticed that Microsoft speaks in mssql 7.0about
the possible slowness if there are 'temp' tables involved here but these 2
procedures do not create or use 'temp' tables.

We removed the 'with recompile' option statements from the two stored
procedures and they ran fine and quickly.

The with recompile was put there because of arithmatic overflows with some
data in . Since we took them out, we will need to get a workaround.
My concerns is:

1) The stored procedures 'with recompile' under mssql 6.5 ran fine but
under mssql 7.0, the stored procedures ran extremely slow. Does any one
have any information on why stored procs with 'with recompile' would run
differently under mssql 6.5 and mssql 7.0?

Any information would be greatly appreciated. Thanks.

View Replies !   View Related
GUI Client For MsSQL (was: MSSQL Help)
I have my MSSQL hosted, any interface i can use so i can't connect to the data source?

for Mysql, i know i can use phpmyadmin, or mysql interface.

i have no idea for MSSQL, any advices?

i know there is one, which is Toad, but it has a lots of limitations, any good idea?


View Replies !   View Related
MsSQL And Php Resources (was: Need Mssql)

till now i have used mysql with i want to use mssql with php and apache versions are
php 5.2.3
apache 2.2.4

from where i can get mssql and what i need to run my php scripts with mssql database.iam newbie in mssql.any of your help will be surely appreciated


View Replies !   View Related
A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


View Replies !   View Related
Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?

View Replies !   View Related
I Want A Function Like IfNull Function To Use In Expression Builder

I wonder if there a function that i can use in the expression builder that return a value (e.g o)  if the input value is null ( Like ifnull(colum1,0)  )


i hope to have the answer because i need it so much.



View Replies !   View Related
Migrate MSSQL 2005 Express -&&> MSSQL 2005 Srv

What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?

View Replies !   View Related
ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?

View Replies !   View Related
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !   View Related
Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.



I am trying to create a inline function which is listed below.


USE [Northwind]







(SELECT ident_current('orders'))



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.


Pleae help me to fix this error





View Replies !   View Related
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
FROM Split(@tmp,DEFAULT)

View Replies !   View Related
Use Getdate Function In A Own Function
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View Replies !   View Related
Calling A Function From A Function?
Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name

and will call this function UserSummary():
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View Replies !   View Related
Im trying to write a  very basic stored porcedure that looks up some data and performs simple calculations, the results of which are then saved (updated) to a specific table. To be more specific, I use the avg function in a simple select statement to average some figures based on a where clause:
SELECT AVG(exactskip)FROM HistoryWHERE Exact = '023'
...This should return 425.66, but it returns either 425 or 425.0000.
The results which are returned and being saved are being shown as a rounded integer or shown with 4 zeros after the decimal. I know it should be ending with a decimal in almost all cases. The column in the table that the result are being saved to is formated as smallmoney. The column in the table whos data is being averaged is set as an integer.  Question, how can I make the avg function calculate the decimal values and save the appropriate decimal results. What changes do I need to make to my select statement. Any help would be appreciated.
I am still very new to all this, so please be specific and somewhat step by step in any reply

View Replies !   View Related
any free videos to study mssql server 2000 queries

View Replies !   View Related
Mssql And
Hey guys, im trying to write an application to connect to a database on a mssql server using  Thus far any code i've seen and tried seems to not recognize the ODBC connections, so i was hoping you guys can offer some suggestions.  For the example lets say the server is the db name is xyz with a username of welsh_welsh and the password is q with a table named abc in the database.  How exactly would you go about connecting.  The code i've tried so far is below:
"Data Source =;Initial Catalog = xyz;UserID = welsh_welsh;Password = q;"
The error i get from the sever is
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30035: Syntax error.

View Replies !   View Related
I have an SQL 2000 server set up that runs perfectly, and an IIS set up that runs perfectly but I can not get the webpage to read data from the dbase.  Is there a connection that I have to make or a program I have to load? The page is a .asp page, and when I run the page that should connectI do not get any errors in the dbase at all, so I can only imagine that it is not even seeing an attemptat a connection.  Any help is appreciated.

View Replies !   View Related
Help, Im New To MSSQL
-- =============================================-- Create table with IDENTITY column-- =============================================create table logviewer(idLog smallint IDENTITY(1, 1),timeLog, Char(14) NOT NULL,dateLog, Char(14) NOT NULL,typeLog, Char(14) NOT NULL,adressLog, Char(90) NOT NULL,messageLog, Char(254) NOT NULL)Server: Nachr.-Nr. 173, Schweregrad 15, Status 1, Zeile 8Die Definition für die timeLog-Spalte muss einen Datentyp enthalten.eng. define timLog colom as datatypeCould anyone help me please?

View Replies !   View Related
Hey Guys, are you aware of anyone moving from MYSQL to MSSQL or viceversa - what were your experiences - Mark Smith

View Replies !   View Related
Hello!!How can I check my actual login i MSSQL.I need it becouse Login is argument of my procedure.Is it possible to make something like this??Please help me!!

View Replies !   View Related

Copyright © 2005-08, All rights reserved