Click here to Skip to main content
15,113,531 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
alter proc SP_GetEmployeeByGender
@Gender varchar(50),
@EmployeeName varchar(100) output
as
begin
select @EmployeeName = 
    select Name ,GenderValue
	from PersonTable 
	join PersonGender
	on PersonTable.Gender = PersonGender.ID
	where GenderValue = @Gender
end


as i am executing this code it get me error on the underlined word the select i need to kno how to solve this problem

What I have tried:

<pre lang="SQL">
alter proc SP_GetEmployeeByGender
@Gender varchar(50),
@EmployeeName varchar(100) output
as
begin
select @EmployeeName = 
        'select Name ,GenderValue
	from PersonTable 
	join PersonGender
	on PersonTable.Gender = PersonGender.ID
	where GenderValue = @Gender'
end

what i have tried i added single qoutes as you can see in code but the result was the same words between the qutes it considered a sring
Posted
Updated 29-Aug-19 8:26am
Comments
Maciej Los 28-Aug-19 14:42pm
   
What's error message?
Member 14479161 28-Aug-19 14:45pm
   
error massage it is
Msg 156, Level 15, State 1, Procedure SP_GetEmployeeByGender, Line 7 [Batch Start Line 7]
Incorrect syntax near the keyword 'select'.<pre>

If you would like to return a result set, change your SP accordingly:

SQL
alter proc usp_GetEmployeeByGender
@Gender varchar(50)
AS
BEGIN

    SET NOCOUNT OFF;

    SELECT [Name] ,GenderValue
    FROM PersonTable AS pt
	    join PersonGender AS pg ON pt.Gender = pg.ID
    WHERE pg.GenderValue = @Gender

END
   
Comments
Member 14479161 28-Aug-19 15:13pm
   
hi i want to assign the result set to a value is that possible
Maciej Los 28-Aug-19 15:29pm
   
You have 2 errors in your code:
1. When assigning a value from a SELECT statement, that SELECT needs parenthesis.
2. Your SELECT statement is returning 2 values (Name & Gender) to that assignment

Try something like this out
SQL
SELECT @EmployeeName = (
	SELECT [Name]
	FROM   PersonTable
	JOIN   PersonGender on PersonTable.Gender = PersonGender.ID
	WHERE  GenderValue = @Gender
)
   
Try:
SET @EmployeeName = 
    select Name ,GenderValue
	from PersonTable 
	join PersonGender
	on PersonTable.Gender = PersonGender.ID
	where GenderValue = @Gender
   
Comments
Member 14479161 28-Aug-19 14:38pm
   
hi
o recived this error
Msg 156, Level 15, State 1, Procedure SP_GetEmployeeByGender, Line 7 [Batch Start Line 7]
Incorrect syntax near the keyword 'select'.<pre>
Maciej Los 28-Aug-19 14:44pm
   
Seems, OP has tried this... Please, read the question again.
I suppoose that select statement returns set of records instead of single record.
Member 14479161 28-Aug-19 14:47pm
   
@Maciej Los how can i solve that by your opinion
It seems as there are 2 issues in your script

1. You cannot assign 2 column values for one variable.
2. There is an additional "select" term in your script

see below sample example. Hopes this will help

declare @name varchar(255)

select @name = name from[dbo].[Employees] where Id =1

select @name
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900