Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

sored proce error

checked my syntax and it says it's ok.

when i click the Ok button it returns this error.

Error 21001:[SQL-DMO]Stored procedurre definition must include name and text (for Standard storeProcedure) or libraryname (for Extended StoredProcedure).


Create Procedure[dbo].[stp_Authenticate_User]
@username varchar(50),
@password varchar(50)
as

if exists(select  destination from fsa_t_dev_login where Username=@username and password=@password)
  select destination from fsa_t_dev_login where Username=@username and password=@password

else
 select "
0
Eric_Trogdon
Asked:
Eric_Trogdon
  • 7
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Create Procedure[dbo].[stp_Authenticate_User]
@username varchar(50),
@password varchar(50)
as

if exists(select  destination from fsa_t_dev_login where Username=@username and password=@password)
  select destination from fsa_t_dev_login where Username=@username and password=@password

else
 select '' as destination

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
or simply


Create Procedure[dbo].[stp_Authenticate_User]
@username varchar(50),
@password varchar(50)
as

if exists(select  destination from fsa_t_dev_login where Username=@username and password=@password)
  select destination from fsa_t_dev_login where Username=@username and password=@password

else
 select ''
GO
0
 
Eric_TrogdonAuthor Commented:
tried both of those and get the same error with the
else
  select "destination"

and get a sytax erro on line 10 with

else
  select
GO
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to use Single Quotes instead of the double Quotes
0
 
Eric_TrogdonAuthor Commented:
used single quotes

    else
       select 'destination'

still get the error
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think you must install the latest service packs
0
 
Brian CroweCommented:
CREATE PROCEDURE dbo.stp_Authenticate_User
   @username varchar(50),
   @password varchar(50)
AS

SELECT ISNULL(destination, '')
FROM fsa_t_dev_login
WHERE Username = @username
   AND password = @password
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Eric_TrogdonAuthor Commented:
thanks BriCrowe
0
 
Brian CroweCommented:
I believe the problem was that you were missing the space after "CREATE PROCEDURE"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi briCrowe,
i dont think it is the case, did you tried it ? It is not associated with the space?

 Eric_Trogdon ,
can you run
SELECT @@Version

and send us the result .
0
 
Brian CroweCommented:
actually i was able to replicate the exact error by creating a new procedure and simply deleting the space between "Procedure" and the left bracket.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In my system it is not throwing any error
i am using SP4
0
 
Brian CroweCommented:
I am using developer version and even though I have installed SP4 it says I am using SP2

Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
      Aug  6 2000 00:57:48
      Copyright (c) 1988-2000 Microsoft Corporation
      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now