simple stored procedure help - 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 2005 Forums

Transact-SQL (2005)

simple stored procedure help

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 09:51:01

When I verify syntax, I get this error:
Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Here's the proc:
CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME varchar(40) = ""
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT *
FROM BENEFICIAL_USES_DM
END
GO

Thanks.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-12 : 09:54:08

USE
@DISPOSAL_AREA_NAME varchar(40) = NULL instead of
@DISPOSAL_AREA_NAME varchar(40) = ""

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 09:55:03

Awesome, thank you for the help.

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-06-12 : 09:58:57

Replace double quotes (") with single quotes (') in the default parameter value.

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 10:04:04

Is there any difference speed-wise between these:
IF @DISPOSAL_AREA_NAME IS NULL
and
IF @DISPOSAL_AREA_NAME = ''

Any benefits doing it one way or the other?

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-06-12 : 10:07:07

NULL and '' (empty string) are different.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-12 : 10:10:07

I dont think there is any speed difference.But with NULL value passed you have to just use ISNULL to check whether a parameter value has been passed.For example if you use NULL you can use the parameter value in a query like this
SELECT *
FROM BENEFICIAL_USES_DM WHERE DISPOSAL_AREA_NAME=ISNULL(@DISPOSAL_AREA_NAME,DISPOSAL_AREA_NAME).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources