If my parameter is null I want to set it -sql call

So if my store procedure is called with a null item I want to make it '' (otherwise it won't work with my result list) How does one do this?

I would like my code to spit out the population for one country if the country input was US,null,null, or I want it more specific population if US,New York, New York City is put in. So population of New York City.

开发者_如何转开发Create procedure [dbo].[GetPopulation]

        @CountryCode varchar(3),
    @State varchar(80)=null,
    @City varchar(80)=null
as
if @State is Null 
    @State = ''
if @City is Null 
    @City = ''

select Population
FROM Countries
Where CountryCode = @CountryCode AND State = @State AND City=@City

If I am right you want to ignore where clause's when parameter is null

select Population
FROM Countries
Where (CountryCode = @CountryCode OR @CountryCode IS NULL)
 AND (State = @State OR @State IS NULL)
 AND (City=@City OR @City IS NULL)

In case your problem is the application parameters the procedure run's with, then default param's to ''

Create procedure [dbo].[GetPopulation]

        @CountryCode varchar(3),
    @State varchar(80)='',
    @City varchar(80)=''

select Population
FROM Countries
Where CountryCode = @CountryCode AND State = @State AND City=@City

Run the procedure as dbo.GetPopulation 'US' for US or

dbo.GetPopulation 'US','New York', 'New York City' for New York City

Try something like:

set @State = coalesce(@State, '')

The coalesce operator will return the first non-null value. In this case, either @state or ''

If I understand your question -- just fix your WHERE clause like this:

   WHERE      
             CountryCode = @CountryCode 
         AND (State = @State OR @State is null) 
         AND (City = @City OR @City is null)

@State varchar(80) = null --> this means "allow the procedure to run without the @State parameter"
@State varchar(80) = '' --> this means "if the procedure is run without the @State parameter, then change the @State parameter to '' (empty string).

Option 2 is doing the same thing as this, so you can delete this section:

if @State is Null 
    @State = ''
if @City is Null 
    @City = ''

The only other thing is to fix your WHERE clause to handle missing parameter values (which is hopefully self-explanatory when you see it below).

So then this is your original code, fixed per the explanation above:

Create procedure [dbo].[GetPopulation]

    @CountryCode varchar(3),
    @State varchar(80)='',
    @City varchar(80)=''
as

select Population
FROM Countries
Where CountryCode = @CountryCode AND (State = @State OR @State = '') AND (City=@City OR @City = '')

You don't need to make the parameters empty:

SET ANSI_NULLS ON
Create procedure [dbo].[GetPopulation]

    @CountryCode varchar(3),
    @State varchar(80)=null,
    @City varchar(80)=null
as
select Population
FROM Countries
Where CountryCode = @CountryCode AND 
    State = coalesce(@State, State) AND 
    City = coalesce(@City, City)