select query

[ssrajpal2001]

[CODE=sql]create procedure [sp_LoginUser]
--inputparameter------------------------------------------------------------------------------
@User_id varchar(50) ,
@SendPassword varchar(50) ,
@Schema_name varchar(50) ,
@dbpassword varchar(15) output,
@Department varchar(20) output

as
declare @sql Nvarchar(300);
declare @id int;

begin transaction
set @sql=N'select dbpassword=Pass word,id=id from '+ @Schema_name+'. Users where Username='+@Use r_id ;
exec sp_executesql @sql
set @Department=(se lect Department from Agent_departmen t where id=@id);

commit transaction[/CODE]

this is not the complete code what i want is .
i want that dbpassword should fetch password from table and then using the if condition dbpassword will be checked with the password send under the procedure .if both the password matches it will check other table for department and department field will be returned to UI.
Please help how can this be done .
what i wrote previously was
[CODE=sql]create procedure sp_LoginUser
--inputparameter------------------------------------------------------------------------------
@User_id varchar(50) = null ,
@SendPassword varchar(50) = null,
@Schema_name varchar(50) = null,
@dbpassword varchar(15)=nul l output,
@Department varchar(20)=nul l output,
@verify bit output

as
declare @sql Nvarchar(300);
declare @id int;

begin transaction
set @sql=N'select @dbpassword=Pas sword,@id=id from '+ @Schema_name+'. Users where Username='+@Use r_id ;
exec sp_executesql @sql;
if (@dbpassword=@S endPassword)
begin
set @Department=(se lect Department from TRAVELSHOPERP.A gent_department where id=@id);
set @verify=1;
end
else
begin
set @verify=0;
end
commit transaction[/CODE]

[iburyak]

Try this:

CREATE procedure sp_LoginUser
--inputparameter------------------------------------------------------------------------------
@User_id        varchar(50) =    NULL ,      
@SendPassword      varchar(50)    =   NULL,
@Schema_name    varchar(50) =    NULL,
@dbpassword  varchar(15)=NULL output,
@Department  varchar(20)=NULL output,
@verify    bit = 0 output
 
AS

If @Schema_name = 'Schema1'

	SELECT @Department = Department, @verify = 1, @dbpassword = password
	FROM TRAVELSHOPERP.Agent_department d
	JOIN Schema1.Users u on d.id = u.id
	WHERE u.Username = @User_id and password = @dbpassword 

else if @Schema_name = 'Schema2'

	SELECT @Department = Department, @verify = 1, @dbpassword = password 
	FROM TRAVELSHOPERP.Agent_department d
	JOIN Schema2.Users u on d.id = u.id
	WHERE u.Username = @User_id and password = @dbpassword

Good Luck.

[ssrajpal2001]

thanks for the reply but the thing is
schema name is not fixed it may change i.e this procedures will be called n number of times with different schema names