syntax error line numbers

[kristinac]

Please forgive me if this is the wrong forum. And also I'm a total newbie at SQLServer.

I'm working with SQLServer2k. When I do a syntax check on my stored procedure, I get an error message. It says the error message # (156) and that there is a syntax error near the keyword SET. Well there are many SETs in my procedure. How the heck to I make the line numbers appear in the error message?

 

[bdreed35]

You can double click the red text in the error message and it will bring you to the offending line in your code.

~Brian

 

[JohnDTampaBay]

What are you using to write the procedure? If you're writing the procedure in Query Analyzer, the line number should be included in the error message. Also in Query Analyzer, you can double-click on the error message and it will take you to the line it thinks is the problem. If you're using Enterprise Manager, I don't think you have any options.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

Actually I should have been more clear. It's a user defined function and I'm in Enterprise Manager. I copied it into the query analyzer and replaced the @variables with actual fields, but it won't work at all. I know I'm doing this wrong. Can you check a function in the query analyzer?

 

[JohnDTampaBay]

If you are looking at the ALTER FUNCTION or CREATE FUNCTION code that defines the function, you don't need to change the @variables to fields. The @variables will be replaced at runtime with whatever values are passed as parameters. I believe you can only use SET in functions to assign values to variables. If you are trying to SET NOCOUNT ON or another environment setting, this could be the problem. Is it possible to post the function code?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

Sure... It's really just a little function that puts fields together to make a street name. And I had null values that were screwing it up so someone suggested I add the IF NULL statements...

CREATE FUNCTION [dbo].[GetFullStreetName]
(@Prefix varchar(10), @StreetName varchar(30), @Type varchar(10), @Suffix varchar(10))
RETURNS varchar(62)
AS
BEGIN
DECLARE @FullName varchar(62)

IF (@Prefix = NULL) SET @Prefix = ' '
IF (@StreetName = NULL) SET @StreetName = ' '
IF (@Type = NULL) SET @Type = ' '
IF (@Suffix = NULL) SET @Suffix = ' '

SET @FullName = @Prefix

IF (@Prefix <> ' ') AND (@StreetName <> ' ' OR @Type <> ' ' OR @Suffix <> ' ')
SET @FullName = @FullName + ' '

SET @FullName = @FullName + @StreetName

IF (@StreetName <> ' ' AND (@Type <> ' ' OR @Suffix <> ' ')
SET @FullName = @FullName + ' '

IF @Type <> ' '
SET @FullName = @FullName + @Type

IF @Suffix <> ' '
SET @FullName = @FullName + ' '

SET @FullName = @FullName + @Suffix

RETURN (@FullName)
END

And thank you so much for helping me! I'm so lost.

 

[JohnDTampaBay]

It's a parentheses problem. In the following line you have two opens and only one close. Fix it and you should be golden. Good luck!

IF (@StreetName <> ' ' AND (@Type <> ' ' OR @Suffix <> ' ')


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

Oh for gosh sakes. I've been so stressed today trying to figure things out...when I got to this problem I think I just short circuited. Thanks, you are an Angel!! It's working great.

 

[JohnDTampaBay]

Here's an alternative script that seems to accomplish the same thing. I use ISNULL to substitute empty string on the fly and use RTRIM to the separator space when it isn't needed.

ALTER FUNCTION [dbo].[GetFullStreetName]
(@Prefix varchar(10)
, @StreetName varchar(30)
, @Type varchar(10)
, @Suffix varchar(10))
RETURNS varchar(62)
AS BEGIN
DECLARE @FullName varchar(62)

SET @FullName =
RTrim(
RTrim(
IsNull(@Prefix,'') + ' ' + IsNull(@StreetName,'')
) + ' '
+ IsNull(@Type,'')) + ' '
+ IsNull(@Suffix,'')

RETURN LTrim(RTrim(@FullName))
END


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

May I ask why you are using ALTER FUNCTION instead of CREATE FUNCTION?

 

[JohnDTampaBay]

Strictly speaking from a T-SQL point of view, once the function has been created, you have to either ALTER it to change the code or DROP the existing one before you can CREATE it again. All permissions and dependencies are lost when an object is dropped, even if you recreate it immediately. So its safer to ALTER rather than DROP and recreate an object.

Enterprise Manager only shows CREATE code for procedures and functions. When you make changes through EM, it manages permissions for you so the new object retains the same permissions as the old object. Query Analyzer executes T-SQL without managing anything. In QA, you'd need to go the ALTER route.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[kristinac]

Oh okay. Thank you. See I'm just so new at SQL Server. I put your code in a new udf. It changed to say CREATE and now I have two GetFullStreetName functions. But they are both the same now. I guess it altered the old one then. I was going to delete one but it looks like it will kill them both. LOL I guess I should have just changed the code in the original.