sp help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I didn't know where else to ask this, so I hoped maybe someone here can help
me:

please help.
here is my sp:
ALTER PROCEDURE spGetProjectNameForCurrentFilter

@MemberType varchar(50),
@Employee varchar(200)


AS

Select ProjectID, ProjectName
FROM Project
WHERE ProjectID IN
(Select TEAMS.ProjectID
From Teams
where (MemberType = '@MemberType' AND FULLName = '@Employee' ))
ORDER BY ProjectName

to test, I do a spGetProjectNameForCurrentFilter 'ReportManager', 'Ann
LastName' in QueryAnalyer I get no results, I even tried
spGetProjectNameForCurrentFilter @MemberType ='ReportManager', @Employee =
'Ann LastName'

I don't get any errors, just no results...sigh...

If I run the select statement on it's own hardcoding the @ paramaters it
works fine.

Where am I going wrong?
Quite new at this so please bear with me.
Thanks
 
ann said:
I didn't know where else to ask this, so I hoped maybe someone here can help
me:

please help.
here is my sp:
ALTER PROCEDURE spGetProjectNameForCurrentFilter

@MemberType varchar(50),
@Employee varchar(200)


AS

Select ProjectID, ProjectName
FROM Project
WHERE ProjectID IN
(Select TEAMS.ProjectID
From Teams
where (MemberType = '@MemberType' AND FULLName = '@Employee' ))
ORDER BY ProjectName

to test, I do a spGetProjectNameForCurrentFilter 'ReportManager', 'Ann
LastName' in QueryAnalyer I get no results, I even tried
spGetProjectNameForCurrentFilter @MemberType ='ReportManager', @Employee =
'Ann LastName'

I don't get any errors, just no results...sigh...

If I run the select statement on it's own hardcoding the @ paramaters it
works fine.

Where am I going wrong?
Quite new at this so please bear with me.
Thanks
Click to expand...

Probably should go to the:
Microsoft.Public.SqlServer.Programming group.
 
Ann,

Are those quotes around the parameters in the SP itself? Those quotes
will cause you to search for MemberType and FULLName equal to those strings,
not the strings you pass in.

Additionally, naming your stored procedure to begin with 'sp' is a bad
idea, since it will degrade performance. When they begin with SP (on SQL
Server, at least), SQL Server looks in the master database first to find the
SP, then in the local database. If it does not, then it looks in the local
DB first. You might want to change this.

Hope this helps.
 
see inline
I didn't know where else to ask this, so I hoped maybe someone here
can help me:

please help.
here is my sp:
ALTER PROCEDURE spGetProjectNameForCurrentFilter

@MemberType varchar(50),
@Employee varchar(200)


AS

Select ProjectID, ProjectName
FROM Project
WHERE ProjectID IN
(Select TEAMS.ProjectID
From Teams
where (MemberType = '@MemberType' AND FULLName = '@Employee' ))
ORDER BY ProjectName
Click to expand...

remove the quotes around the parameters. You are now testing
for literally '@MemberType' instead of the value of the @MemberType
parameter.
 
Nicholas - thank you for taking the time to help - I really appreciate it and
your reply is right - when I took off the ', everything worked as it should

Also, thank you for the additional info on naming of sp...I didn't know
that, but will certainly correct my naming convention in the future.



Nicholas Paldino said:
Ann,

Are those quotes around the parameters in the SP itself? Those quotes
will cause you to search for MemberType and FULLName equal to those strings,
not the strings you pass in.

Additionally, naming your stored procedure to begin with 'sp' is a bad
idea, since it will degrade performance. When they begin with SP (on SQL
Server, at least), SQL Server looks in the master database first to find the
SP, then in the local database. If it does not, then it looks in the local
DB first. You might want to change this.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

ann said:
I didn't know where else to ask this, so I hoped maybe someone here can
help
me:

please help.
here is my sp:
ALTER PROCEDURE spGetProjectNameForCurrentFilter

@MemberType varchar(50),
@Employee varchar(200)


AS

Select ProjectID, ProjectName
FROM Project
WHERE ProjectID IN
(Select TEAMS.ProjectID
From Teams
where (MemberType = '@MemberType' AND FULLName = '@Employee' ))
ORDER BY ProjectName

to test, I do a spGetProjectNameForCurrentFilter 'ReportManager', 'Ann
LastName' in QueryAnalyer I get no results, I even tried
spGetProjectNameForCurrentFilter @MemberType ='ReportManager', @Employee =
'Ann LastName'

I don't get any errors, just no results...sigh...

If I run the select statement on it's own hardcoding the @ paramaters it
works fine.

Where am I going wrong?
Quite new at this so please bear with me.
Thanks
Click to expand...
Click to expand...
 
Hans - thank you for taking the time to help - I really appreciate it and
your reply is right - when I took off the ', everything worked as it should

doh! feel like such a dork sometimes :)
 
Nicholas said:
Ann,

Are those quotes around the parameters in the SP itself? Those quotes
will cause you to search for MemberType and FULLName equal to those strings,
not the strings you pass in.

Additionally, naming your stored procedure to begin with 'sp' is a bad
idea, since it will degrade performance. When they begin with SP (on SQL
Server, at least), SQL Server looks in the master database first to find the
SP, then in the local database. If it does not, then it looks in the local
DB first. You might want to change this.

Hope this helps.
Click to expand...
Are you sure its all sp prefixes?
I thought it was only sp_ prefixes and sp should be ok.

JB
 
Back
Top