Hi, I have a stored procedure parameters are emp_no, avgsal.number

i have data in a table.my request is take emp_no from table and pass parameter to procedure .

For this one i want create a procedure and temp table.

ALTER procedure [dbo].[SalaryRetrivial]
as
begin
declare @TSQL nvarchar(200)

set @TSQL='select distinct emp_no from dbo.employee'
Create Table #Temp
(
avgsal int
,emp_no nvarchar(10)
)
insert into #Temp
(
avgsal,
emp_no
)
exec dbo.DebtScoreProcedure @TSQL,0,' ',

select d.emp_no,d.emp_name,t.avgsal from dbo.employee d
inner join  #Temp t on d.emp_no=t.emp_no
end
GO

i got zero result when execte a procedure can you help on this please



what does this SP do?

exec dbo.DebtScoreProcedure @TSQL,0,' ',

stored procedure has two 3 arameters

emp_no--- input

avgsal---out

number---out

when i was in debugging the procedure gave result

I do not think the syntax is correct for calling stored procedure having 3 parameter,

exec dbo.DebtScoreProcedure @TSQL,0,' ',

Remove the last comma character from the command and try once.

Secondly, i do not think we need OUT parameter. Share the code for dbo.DebtScoreProcedure, that will help us.

I removed comma but i got zero result

Refer the below example, hope it clarifies.

-------------------------
CREATE PROCEDURE TESTING(@QRY NVARCHAR(MAX))
AS
BEGIN
	EXEC(@QRY)
END
--------------------------
DECLARE @TEMP TABLE(DT DATETIME)
INSERT INTO @TEMP
EXEC TESTING 'SELECT GETDATE()'
SELECT * FROM @TEMP

-------------------------
Regards, RSingh

Thank you for your replay. but my table has more than one value

ALTER procedure [dbo].[SalaryRetrivial]
as
begin
	declare @TSQL nvarchar(200)
	set @TSQL='select distinct emp_no from dbo.employee'
	Create Table #Temp(avgsal int,emp_no nvarchar(10))
	insert into #Temp(avgsal,emp_no) exec DebtScoreProcedure @TSQL
	select d.emp_no,d.emp_name,t.avgsal from dbo.employee d
	inner join  #Temp t on d.emp_no=t.emp_no
end
-------------------------------------------
create procedure DebtScoreProcedure(@TSQL nvarchar(max))
as
begin
	declare @strs nvarchar(max)
	set @strs = 'SELECT avg(salary) as avgsal,emp_no FROM TABLENAME WHERE emp_no IN (' + @tSQL + ')	GROUP BY  emp_no'
	exec(@strs)
end

Thank you for give replay. actually i don't want average salary.instead of avg(salary) any posiible to mentioned my own stored procedure