Help with a SQL stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Help with a SQL stored procedure

Author

Topic

grandflavour
Starting Member

2 Posts

Posted - 2008-10-28 : 00:47:54

Hello everyone.

I'm having trouble with a stored procedure that I created.


CREATE PROCEDURE makeLetter 
@numberNo integer
AS
DECLARE @licenseno INT
if exists (select driverLicenseNo
	from BUS
	where numberPlate = @numberNo)
  Print 'the license number is: ' + driverLicenseNo
ELSE
  Print 'Record doesn''t exist'
GO

I can't seem to get it to print the license number properly and was wondering if someone could help me out a bit.

Any help greatly appreciated.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-28 : 01:01:53

Print 'the license number is: ' + driverLicenseNo

The 'driverLicenseNo' in the above statement is neither a variable nor part of select statement. SQL Server cannot understand whats that and will error out. You can find one solution below -

if exists (select driverLicenseNo
from BUS
where numberPlate = @numberNo)
SELECT 'the license number is: ' + driverLicenseNo
from BUS
where numberPlate = @numberNo
ELSE
Print 'Record doesn''t exist'

grandflavour
Starting Member

2 Posts

Posted - 2008-10-28 : 01:10:23

Thanks for the help. Though I'm trying to print it as a string. I tried this way and I got the error message:

Syntax error converting the varchar value 'the license number is: ' to a column of data type int.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-28 : 01:21:00

Please use convert/cast function to convert the integer License No to
character.

SELECT 'the license number is: ' + Use convert on this column driverLicenseNo
from BUS
where numberPlate = @numberNo

Go through BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources