SQL question...

  • Thread starter Thread starter Jan Nielsen
  • Start date Start date
J

Jan Nielsen

Hi
I would like to use a stored procedure to return a city from a zip and
return "unknown" if no zip or a non-excisting zip is entered
I have this code that only works on existing zips:
********************
Dim myCommand As New SqlCommand("ReturnerByFraPostnr", SqlConnectionFindBy)
Dim retRow As String

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@postnr", SqlDbType.Int).Value =
txtPostnummer.Text

If txtPostnummer.Text = "" Then

txtBy.Text = ""

Exit Sub

End If

If SqlConnectionFindBy.State = ConnectionState.Closed Then

SqlConnectionFindBy.Open()

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

Else

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

End If

myCommand = Nothing

************************
Here is my stored procedures sql
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int --,

AS
select [by] from tPostnr where Postnr = @postnr
GO
*******************************

Best regards

Jan
 
Hi
I would like to use a stored procedure to return a city from a zip and
return "unknown" if no zip or a non-excisting zip is entered
I have this code that only works on existing zips:
********************
Dim myCommand As New SqlCommand("ReturnerByFraPostnr", SqlConnectionFindBy)
Dim retRow As String

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@postnr", SqlDbType.Int).Value =
txtPostnummer.Text

If txtPostnummer.Text = "" Then

txtBy.Text = ""

Exit Sub

End If

If SqlConnectionFindBy.State = ConnectionState.Closed Then

SqlConnectionFindBy.Open()

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

Else

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

End If

myCommand = Nothing

************************
Here is my stored procedures sql
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int --,

AS
select [by] from tPostnr where Postnr = @postnr
GO
*******************************

Best regards

Jan
Click to expand...
read up on coalesce and/or case

something like select coalesce(by,'Unknown') from ......
 
Hi Lyndon
Thanks for answering

I tried
***************
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int

AS
select coalesce([by],'Ukendt') from tPostnr where Postnr = @postnr
GO
****************
It does not work. When I supply a existing postnr (= Zip) I get the correct
by (City). But if I supply a non-existing zip nothing is received.
And no error is produced.

Then I tried
*******************
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int,
@result as varchar

AS
select @result = coalesce([by],'Ukendt') from tPostnr where Postnr =
@postnr
return @result
GO
**********************
But I can't call this SP from the Query Analyzer. Somehow I don't seem to
supply the parameters in the correct way. I use
ReturnerByFraPostnr 2500, 't'
('t' is just a random letter, 2500 is a current zip)

Jan



Lyndon Hills said:
Hi
I would like to use a stored procedure to return a city from a zip and
return "unknown" if no zip or a non-excisting zip is entered
I have this code that only works on existing zips:
********************
Dim myCommand As New SqlCommand("ReturnerByFraPostnr", SqlConnectionFindBy)
Dim retRow As String

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@postnr", SqlDbType.Int).Value =
txtPostnummer.Text

If txtPostnummer.Text = "" Then

txtBy.Text = ""

Exit Sub

End If

If SqlConnectionFindBy.State = ConnectionState.Closed Then

SqlConnectionFindBy.Open()

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

Else

retRow = myCommand.ExecuteScalar

txtBy.Text = retRow

End If

myCommand = Nothing

************************
Here is my stored procedures sql
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int --,

AS
select [by] from tPostnr where Postnr = @postnr
GO
*******************************

Best regards

Jan
Click to expand...
read up on coalesce and/or case

something like select coalesce(by,'Unknown') from ......
Click to expand...
 
Hi Lyndon
Thanks for answering

I tried
***************
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int

AS
select coalesce([by],'Ukendt') from tPostnr where Postnr = @postnr
GO
****************
Click to expand...
Sorry,

select coalesce((select regiondescription from region where regionid =
5),'Unknown')

This works on the northwind database. Supply regionid=4 to get a valid
(Souther) region for testing.

HTH
 
It works perfect!

Thanks a lot

Jan
Lyndon Hills said:
Hi Lyndon
Thanks for answering

I tried
***************
CREATE PROCEDURE [dbo].[ReturnerByFraPostnr]
@postnr int

AS
select coalesce([by],'Ukendt') from tPostnr where Postnr = @postnr
GO
****************
Click to expand...
Sorry,

select coalesce((select regiondescription from region where regionid =
5),'Unknown')

This works on the northwind database. Supply regionid=4 to get a valid
(Souther) region for testing.

HTH
Click to expand...
 
Back
Top