Why won't my SP work? 1

[1DMF]

hi,

i have the following SP

CREATE PROCEDURE spPPP_Prospects
@@ContType nvarchar
AS
SELECT      Contacts.CompanyName, Contacts.firstname,Contacts.Lastname, Contacts.ContactID
FROM         Contacts 
WHERE     (Contacts.ContactTypeID = 'Prospect - ' + @@ContType) AND Contacts.CompanyName > '' 
ORDER BY Contacts.CompanyName
GO

When I run

EXEC spPPP_Prospects 'AR'

I get no results, yet the following SQL returns 1,000's!

SELECT     CompanyName, FirstName, LastName, ContactID
FROM         Contacts
WHERE     (ContactTypeID = 'Prospect - AR') AND (CompanyName > '')
ORDER BY CompanyName

What's the difference?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[robertfah]

Remove the extra '@' symbol from the variable and you should be all set.

 

[SQLScholar]

Hi,

Firstly - why are you using @@ to define variables?

Should just be one @. I think @@ might be global - but either way if you want just normal variables i would use just one @.

If you make that change does it help?

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

[tt]
CREATE PROCEDURE spPPP_Prospects
@@ContType [!]nvarchar[/!]
[/tt]

You did not specify a size for the nvarchar variable. make it something like this:


[tt]
CREATE PROCEDURE spPPP_Prospects
@@ContType [!]nvarchar(100)[/!]
[/tt]


When you use a varchar or nvarchar as a parameter and you do not specify the size, it defaults to 1 character.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

See here for a full explanation:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-stored-procedure-with-nvarcha

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

lol - thanks George, just came back to say I'd worked it out - forgot to dimension the var - D'oh!

not sure what eveyone elses problem with double AT is (@@) works fine?

I see countless examples using double @ , what's the difference?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

not sure what eveyone elses problem with double AT is (@@) works fine?

I see countless examples using double @ , what's the difference?

There's nothing inherently wrong with using double @. Most programmers use a single @ for their variables whether declared within the procedure or as a parameter. SQL Server has some built-in functions with names that start with @@.

If you really want to have fun, change it to a single @, like this:

CREATE PROCEDURE spPPP_Prospects
[!]@[/!] nvarchar(100)
AS
SELECT      Contacts.CompanyName, Contacts.firstname,Contacts.Lastname, Contacts.ContactID
FROM         Contacts 
WHERE     (Contacts.ContactTypeID = 'Prospect - ' + [!]@[/!]) AND Contacts.CompanyName > '' 
ORDER BY Contacts.CompanyName

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom