Stored Procedure syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure syntax

Author

Topic

theLastFlop
Starting Member

4 Posts

Posted - 2006-08-29 : 09:05:09

I'm new to SQL and am having a hell of a time correctly writing a Stored Procedure..here is what I have so far...

CREATE PROCEDURE [dbo].[getCoverage]
AS
DECLARE @Limit VARCHAR(1000)
SELECT
CASE WHEN CHARINDEX('*01', dbo.extract_ve.Coverages,1)=0 THEN
@Limit = 'NONE'
ELSE
@Limit = RTRIM(SUBSTRING(dbo.extract_ve.Coverages, CHARINDEX('*01', dbo.extract_ve.Coverages, 1) + 4, 7))
END

Basically, I have a column called 'Coverages' which is the result of a View. I want to look for '*01' in that column and return the text to the right of the place where '*01' occurs. In the event that '*01' doesn't show up, I want to set the variable that I declared to 'NONE'.

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-29 : 09:13:59

maybe something like

SELECT @Limit =
CASE  CHARINDEX('*01', dbo.extract_ve.Coverages,1) WHEN 0 THEN
 'NONE'
ELSE 
 RTRIM(SUBSTRING(dbo.extract_ve.Coverages, CHARINDEX('*01', dbo.extract_ve.Coverages, 1) + 4, 7)) 
END

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-29 : 09:14:21

is this a function ?
dbo.extract_ve.Coverages
or a column?

I see no FROM clause specifying a table so perhaps you want to post more of the code.

________________________________________________
"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks"

theLastFlop
Starting Member

4 Posts

Posted - 2006-08-29 : 09:34:59

dbo.extract_ve.Coverages is referencing a Column in a View that I created.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 09:47:48

In SQL Server wildcard character is '%', not '*' as in VB.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources