what is this sproc doing? 2

[rds80]

This is from a SQL Server book:

Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
  DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO
EXEC dbo.ListCustomersByCity

How is @Country working? Is the where clause reading WHERE Country LIKE @%? Thanks.

 

[SQLDenis]

>> Is the where clause reading WHERE Country LIKE @%?

it is reading it as '%' meaning if nothing is passed in it will grab all countries

--all countries
EXEC dbo.ListCustomersByCity


--Mexico
EXEC dbo.ListCustomersByCity 'mexico'





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

It first creates the variable @country as a nvarchar(30), then it sets a default value of '%'.

So, if the user does not enter a parameter it will look for everything.

So.

EXEC dbo.ListCustomersByCity 'USA'

would run:

SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE 'USA'
GROUP BY City


But:

EXEC dbo.ListCustomersByCity

would run

SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE '%'
GROUP BY City

Running the script will create the procedure, and then test the default action.

-SQLBill


Posting advice: FAQ481-4875

 

[rds80]

Thats pretty cool, thanks guys